/* SQL DDL: Create an implementation of the model using the SQL Data Definition Language */
/*  Note: Comments such as this may not be supported by your system and may have to be removed */
/*  You may want to change some field types to improve the implementation on your DBMS. */
/*  Data type 'National Character' etc. indicates Unicode text./*   PostgreSQL: You should create the entire database as Unicode!  */
/*   PostgreSQL: For Postgres you should create the entire database as Unicode! The 'National' has been removed from the SQL code */
/*   PostgreSQL: the data type CHARACTER VARYING has been replaced with VARCHAR */
/*   PostgreSQL: the data type DATETIME has been replaced with TIMESTAMP */
/*   PostgreSQL: Table and field names have been quoted (= "Fieldname") to allow case-sensitivity */
/*   PostgreSQL: the default values for boolean fields have been replaced with 'true' and 'false' */



/*=== Table: "Collection" ===*/
/* Definition of independent collections and a tree of subcollections (subcollections may contain subcollections). */
  /*   CollectionID: Unique reference ID for the collection (= Primary key). */
  /*   ParentCollectionID: For a subcollection within another collection: CollectionID of the collection to which the subcollection belongs. For an independent collection: Null. */
  /*   TopmostCollectionID: For an independent collection or subcollection: CollectionID of the independent collection (highest level within the hierarchy). For an independent collection this will be equal to CollectionID. */
  /*   CollectionName: Name of the collection (e. g. 'Herbarium Kew') or subcollection (e. g. 'cone collection', 'alcohol preservations'). This text should be kept relatively short, use Description for additional information. */
  /*   Description: A short description of the collection. */
  /*   Room: Optionally the number or a description of room(s) housing the (sub)collection. */
CREATE TABLE "Collection" (
    "CollectionID" 	int4	 NOT NULL PRIMARY KEY,
    "ParentCollectionID" 	int4	 NULL,
    "TopmostCollectionID" 	int4	 NOT NULL,
    "CollectionName" 	varchar(80)	 NOT NULL,
    "Description" 	text	 NULL,
    "Room" 	varchar(255)	 NULL
);




/*=== Table: "CollectionSpecimen" ===*/
/* A specimen (= collection unit) within a collection. This is the central object of the DiversityCollection database. */
  /*   CollectionSpecimenID: Unique reference ID for the collection specimen record (primary key). */
  /*   CollectionDateCache: Date of the collection event (calculated value based on CollectionDay, Month, and Year; if day or month are missing they are assumed to be '1' for the purpose of the date calculation). */
  /*   CollectionDay: The day of the date of the collection event. */
  /*   CollectionMonth: The month of the date of the collection event. */
  /*   CollectionYear: The year of the collection event. The year may be left empty if only the day or month are known. */
  /*   CollectionDateSupplement: Verbal or additional collection date information, e.g. 'end of summer 1985', 'first quarter', '1888-1892'. */
  /*   LocalityDescription: Locality and geo-ecological description of the locality, exactly as written on the original label (i.e. without corrections during data entry). */
  /*   LabelTitle: Title of label if different from ExsiccataTitle for printing labels. Will be printed on label after ExsiccataTitle. */
  /*   LabelType: Printed, typewritten, typewritten with handwriting added, entirely in handwriting, etc. */
  /*   LabelScanPath: The path and file name of the scan of the label underneath the folder where all labels are stored e.g. "/Fungi/Erysiphales/M-0010/M-001034.jpg". */
  /*   LabelTranscription: The state of the transcription of a label into the database: 'Not started' / 'incomplete' / 'complete'. */
  /*   LabelTranscriptionNotes: User defined notes concerning the transcription of the label into the database. */
  /*   AccessionNumber: Accession number of the specimen within the collection, e.g. "M-29834752". */
  /*   AccessionDay: The day of the date  when the specimen was acquired in the collection. */
  /*   AccessionMonth: The month of the date  when the specimen was acquired in the collection. */
  /*   AccessionYear: The year  when the specimen was acquired in the collection. The year may be left empty if only the day or month are known. */
  /*   AccessionDateSupplement: Verbal or additional accession date information, e.g. 'end of summer 1985', 'first quarter', '1888-1892'. */
  /*   ExsiccataID: If specimen is an exsiccata: Unique reference ID for the Exsiccata series as stored within the DiversityExsiccata database. */
  /*   ExsiccataAbbreviation: If specimen is an exsiccata: Standard abbreviation of the exsiccata (not necessarily a unique identifier; editors or publication places may change over time). */
  /*   ExsiccataNumber: If specimen is an exsiccata: Number of current specimen within the exsiccata series. */
  /*   DepositorsName: The name of the depositor(s) (person or organization responsible for deposition). Where entire collections are deposited, this should also contain the collection name (e.g. 'Herbarium P. Döbbler'). */
  /*   DepositorsAccessionNumber: Accession number of the specimen within the previous or original collection, e.g. 'D-23948'. */
  /*   OriginalNotes: Notes found on the label of the specimen, by the original collector or from a later revision. */
  /*   AdditionalNotes: Additional notes made by the editor of the specimen record, e. g. 'doubtful identification/locality'. */
  /*   LoanInformation: Notes if the item is on loan or has been borrowed. */
  /*   Problems: Description of a problem that occurred during data editing. Typically these entries should be deleted after help has been obtained. Do not enter scientific problems here; use AdditionalNotes for such permanent problems! */
  /*   PrintLabel: Is set to true if the label should be printed, is set to false if the label should not be printed. */
  /*   LogCreatedBy: ID of the user who first entered (typed or imported) the data. This is the operator (or typist) name, which may be different from the person responsible. */
  /*   LogCreatedWhen: Date and time when record was first entered (= typed or imported) into this system. (Note: Updates are recorded in CollectionSpecimenChanges). */
CREATE TABLE "CollectionSpecimen" (
    "CollectionSpecimenID" 	SERIAL  	 NOT NULL PRIMARY KEY,
    "CollectionDateCache" 	timestamp	 NULL,
    "CollectionDay" 	int2	 NULL,
    "CollectionMonth" 	int2	 NULL,
    "CollectionYear" 	int2	 NULL,
    "CollectionDateSupplement" 	varchar(255)	 NULL,
    "LocalityDescription" 	varchar(255)	 NULL,
    "LabelTitle" 	varchar(255)	 NULL,
    "LabelType" 	varchar(255)	 NULL,
    "LabelScanPath" 	varchar(255)	 NULL,
    "LabelTranscription" 	varchar(255)	 NULL,
    "LabelTranscriptionNotes" 	varchar(255)	 NULL,
    "AccessionNumber" 	varchar(255)	 NULL,
    "AccessionDay" 	int2	 NULL,
    "AccessionMonth" 	int2	 NULL,
    "AccessionYear" 	int2	 NULL,
    "AccessionDateSupplement" 	varchar(255)	 NULL,
    "ExsiccataID" 	int4	 NULL,
    "ExsiccataAbbreviation" 	varchar(255)	 NULL,
    "ExsiccataNumber" 	varchar(255)	 NULL,
    "DepositorsName" 	varchar(255)	 NULL,
    "DepositorsAccessionNumber" 	varchar(255)	 NULL,
    "OriginalNotes" 	text	 NULL,
    "AdditionalNotes" 	text	 NULL,
    "LoanInformation" 	varchar(255)	 NULL,
    "Problems" 	varchar(255)	 NULL,
    "PrintLabel" 	bool	 NULL DEFAULT 'true',
    "LogCreatedBy" 	int4	 NULL,
    "LogCreatedWhen" 	timestamp	 NOT NULL DEFAULT current_timestamp
);




/*=== Table: "CollectionDuplicate" ===*/
/* Specimen duplicates that are stored in other collections. */
  /*   CollectionSpecimenID: Refers to the ID of CollectionSpecimen (= Foreign key and part of primary key). */
  /*   AccessionNumber: Accession number as defined by the collection holding the duplicate of the current specimen. Example: 'M-3890492' when CollectionID points to the collection 'Bot. Staatssamml. München'. */
  /*   CollectionID: The collection holding a duplicate with the accession number above (Use IDs as stored in table Collections (= foreign key)). */
  /*   Notes: Notes regarding the duplicate in another collection. */
CREATE TABLE "CollectionDuplicate" (
    "CollectionSpecimenID" 	int4	 NOT NULL,
    "AccessionNumber" 	varchar(255)	 NOT NULL,
    "CollectionID" 	int4	 NULL,
    "Notes" 	varchar(255)	 NULL,
  PRIMARY KEY ("CollectionSpecimenID","AccessionNumber")
);




/*=== Table: "CollectionStorage" ===*/
/* Storage location and type of collection material of the specimen. */
  /*   CollectionSpecimenID: Refers to the ID of CollectionSpecimen (= Foreign key and part of primary key). */
  /*   CollectionID: ID of the Collection as stored in table Collections (= Foreign key and part of primary key). */
  /*   StorageLocation: A code identifying the place where the specimen is stored within the collection. Frequently the accepted scientific name is used as storage location code. */
  /*   MaterialCategory: Material category of specimen. Examples: 'herbarium sheets', 'drawings', 'microscopic slides' etc. */
CREATE TABLE "CollectionStorage" (
    "CollectionSpecimenID" 	int4	 NOT NULL,
    "CollectionID" 	int4	 NOT NULL,
    "StorageLocation" 	varchar(255)	 NULL,
    "MaterialCategory" 	varchar(255)	 NULL DEFAULT 'herbarium sheets',
  PRIMARY KEY ("CollectionSpecimenID","CollectionID")
);




/*=== Table: "CollectingPerson" ===*/
/* Persons or groups involved in collecting specimens ('Collectors'). */
  /*   CollectionSpecimenID: Refers to ID of CollectionSpecimen (= Foreign key and part of primary key). */
  /*   CollectorsName: Name of the collecting person. Example: 'Miller, W. I., Jr.'. Format: Last name, a comma, the first initial with period and blank, an optional middle initial, and an optional comma plus suffix (Jr./Sr./III./MD etc.). */
  /*   CollectorsNumber: Number assigned to a specimen or a batch of specimens by the collector during the collection event (= 'field number'). */
  /*   CollectorsSequence: The order of collectors in a team. Automatically set by the database system (technical note: based on system date/time; counters are changed to random sequence when replicating the database!). */
CREATE TABLE "CollectingPerson" (
    "CollectionSpecimenID" 	int4	 NOT NULL,
    "CollectorsName" 	varchar(255)	 NOT NULL,
    "CollectorsNumber" 	varchar(255)	 NULL,
    "CollectorsSequence" 	timestamp	 NOT NULL DEFAULT now(),
  PRIMARY KEY ("CollectionSpecimenID","CollectorsName")
);




/*=== Table: "CollectionImage" ===*/
/* Images of the collection specimen. */
  /*   CollectionSpecimenID: Refers to the ID of CollectionSpecimen (= Foreign key). */
  /*   ImagePath: The file name and path of the image relative to the main folder for all images e.g. '/Fungi/Ascomycetes/Erysiphales/ErysipheGraminis.jpg' (within the globally defined server/path combination). */
  /*   IdentificationUnitID: If image refers to only on out of several identification units for a specimen, refers to the ID of an IdentificationUnit for a CollectionSpecimen (= foreign key). */
  /*   Responsible: ID of the Person responsible for including the image in the data collection. */
  /*   ImageTitle: The title of the image. */
  /*   Creator: Optional: Author(s), photographer, director, etc. Person(s) or institution(s) responsible for the creation of the abstract resource item (not of the instance, e.g. a scan of an image). [DC11!] */
  /*   CreationDate: Creation date (optionally also time) of the original resource. For digitized resources this should refer to the original creation (of the abstract item, e.g. when picture was drawn or photographed), not the digitization process. */
  /*   CreationMetadata: Optional: additional information regarding the creation process, e.g. image lens, scale, exposure time, etc. */
  /*   Rights: Optional: Information about rights (copyright, intellectual property) held in and over the resource. Enter esp. name of person or institution holding the copyright. Leave empty if unknown or if rights are globally described in resource collection. [DC11!] */
CREATE TABLE "CollectionImage" (
    "CollectionSpecimenID" 	int4	 NOT NULL,
    "ImagePath" 	varchar(255)	 NOT NULL,
    "IdentificationUnitID" 	int4	 NULL,
    "Responsible" 	int4	 NULL,
    "ImageTitle" 	varchar(255)	 NULL,
    "Creator" 	varchar(50)	 NULL,
    "CreationDate" 	timestamp	 NULL,
    "CreationMetadata" 	varchar(255)	 NULL,
    "Rights" 	varchar(255)	 NULL,
  PRIMARY KEY ("CollectionSpecimenID","ImagePath")
);




/*=== Table: "CollectionProject" ===*/
/* Projects as stored within the database DiversityUsers to which the collection specimen belongs to. */
  /*   CollectionSpecimenID: Refers to the ID of CollectionSpecimen (= Foreign key). */
  /*   DiversityProject: Project to which the specimen belongs (Projects are defined in DiversityUsers). */
CREATE TABLE "CollectionProject" (
    "CollectionSpecimenID" 	int4	 NOT NULL,
    "DiversityProject" 	varchar(255)	 NOT NULL,
  PRIMARY KEY ("CollectionSpecimenID","DiversityProject")
);




/*=== Table: "CollectionGeography" ===*/
/* Geographical data of the locality where the specimen were collected. */
  /*   CollectionSpecimenID: Refers to the ID of CollectionSpecimen (= Foreign key and part of primary key). */
  /*   LocalisationSystemID: Refers to the ID of LocationIdentification (= Foreign key and part of primary key). */
  /*   Location1: Either a named location selected from a thesaurus (e. g. 'Germany, Bavaria, Kleindingharting') or altitude range or other values (e. g. 100-200 m). */
  /*   Location2: Corresponding value to Location1 or ID of thesaurus. */
  /*   DeterminationDate: Date of the determination of the geographical localisation. */
  /*   Responsible: Person, team or organisation responsible for the data (free text, not provided by security provider like DiversityUsers). */
  /*   AverageAltitudeCache: Calculated altitude as parsed from the location fields. */
  /*   AverageLongitudeCache: Calculated longitude as parsed from the location fields. */
  /*   AverageLatitudeCache: Calculated latitude as parsed from the location fields. */
CREATE TABLE "CollectionGeography" (
    "CollectionSpecimenID" 	int4	 NOT NULL,
    "LocalisationSystemID" 	int4	 NOT NULL,
    "Location1" 	varchar(255)	 NULL,
    "Location2" 	varchar(255)	 NULL,
    "DeterminationDate" 	timestamp	 NULL,
    "Responsible" 	varchar(255)	 NULL,
    "AverageAltitudeCache" 	float8	 NULL,
    "AverageLongitudeCache" 	float8	 NULL,
    "AverageLatitudeCache" 	float8	 NULL,
  PRIMARY KEY ("CollectionSpecimenID","LocalisationSystemID")
);




/*=== Table: "LocalisationSystem" ===*/
/* Localisation system used for the determination of the geographical data. */
  /*   LocalisationSystemID: Unique ID for the localisation system (= Primary key). */
  /*   LocalisationSystemName: Name of the system used for the determination of the place of the collection, e. g. Gauss-Krüger, MTB, GIS. */
  /*   LocalisationSystemTypeID: Refers to the ID of a LocalisationSystemType (= Foreign key). */
  /*   InterfaceDefinitions: User interface definition: locked status, label captions, etc.; used e. g. in the form CollectionLocality. */
  /*   Description: Description of the localisation method. */
  /*   MeasurementUnit: Measurement unit used within the localisation system, e.g. m, miles etc. Metric units should be preferred. */
  /*   AccuracyOfLocalisation: A description of the accuracy of values that can be reached with this method. */
  /*   ParsingMethod: Internal value, specifying a programming method used for parsing text in fields Location1/Location2 in table CollectionGeography. */
  /*   Enabled: Specifies if this item is enabled to be used within the database. LocalisationSystems can be disabled to avoid seeing them, but to keep the definition for the future. */
CREATE TABLE "LocalisationSystem" (
    "LocalisationSystemID" 	int4	 NOT NULL PRIMARY KEY,
    "LocalisationSystemName" 	varchar(255)	 NOT NULL,
    "LocalisationSystemTypeID" 	int4	 NOT NULL,
    "InterfaceDefinitions" 	varchar(255)	 NULL,
    "Description" 	varchar(255)	 NULL,
    "MeasurementUnit" 	varchar(255)	 NULL,
    "AccuracyOfLocalisation" 	varchar(255)	 NULL,
    "ParsingMethod" 	varchar(255)	 NULL,
    "Enabled" 	bool	 NOT NULL
);




/*=== Table: "LocalisationSystemType" ===*/
/* Coordinate systems etc. used by the localisation systems. */
  /*   LocalisationSystemTypeID: Unique ID for localisation system type (= Primary key). */
  /*   LocalisationSystemType: Type of the localisation system used, e. g. Greenwich coordinates. */
  /*   LocalisationSystemGroup: Group of the localisation system used, e. g. 'Coordinates'. */
CREATE TABLE "LocalisationSystemType" (
    "LocalisationSystemTypeID" 	int4	 NOT NULL PRIMARY KEY,
    "LocalisationSystemType" 	varchar(255)	 NOT NULL,
    "LocalisationSystemGroup" 	varchar(255)	 NOT NULL
);




/*=== Table: "Identification" ===*/
/* Identifications of the identification units. */
  /*   IdentificationUnitID: Refers to the ID of CollectionUnit (= foreign key and part of primary key). */
  /*   CollectionSpecimenID: Refers to the ID of CollectionSpecimen (= foreign key and part of primary key). */
  /*   IdentificationSequence: Date and time the identification was entered into the system, defines the ordering of identifications  (= part of primary key). Note: this is usually not the date the identification occurred! */
  /*   IdentificationDateCache: Date of the identification event (calculated value based on IdentificationDay, Month, and Year; if day or month are missing they are assumed to be '1' for the purpose of the date calculation). */
  /*   IdentificationDay: The day of the identification event. */
  /*   IdentificationMonth: The month of the identification event. */
  /*   IdentificationYear: The year of the identification event. The year may be empty if only the day or month are known. */
  /*   IdentificationDateSupplement: Verbal or additional identification date information, e.g. 'end of summer 1985', 'first quarter', '1888-1892'. */
  /*   VernacularName: Name other than a taxonomic (= scientific) name, e.g. 'pine', 'limestone', 'conifer', 'hardwood'. */
  /*   TaxonomicName: Valid name of the species (including the taxonomic author where available. Example: 'Rosa canina L.'. */
  /*   NomID: Unique ID code of the taxon. Refers to the NomID code of TaxonomyName (= foreign key) in the DiversityTaxonomy component. */
  /*   NomSignature: Signature of the taxonomic name thesaurus from which NomID is derived. */
  /*   IdentificationCategory: Category of the identification e.g. 'determination', 'confirmation', 'absence'. */
  /*   IdentificationQualifier: Qualification of the identification e.g. "cf."," aff.", "sp. nov.". */
  /*   IsLastName: Defines the valid name for the organism. Can be set only once per organism. */
  /*   LiteratureUsed: Publications or authorititative opinions of scientist used during the identification process. Example: enter 'Schmeil-Fitschen 1995' if this field flora was used. */
  /*   Notes: User defined notes, e.g. the reason for a re-determination / change of the name, etc. */
  /*   IsExsiccataTaxon: Defines the name under which the collection specimen is published within an exsiccate. Can be set only once per collection specimen. */
  /*   ResponsibleName: Name of the person or institution responsible for the determination. */
  /*   LogCreatedBy: ID of user who first entered (typed or imported) the data. This is the operator (or typist) name, which may be different from the person responsible. */
  /*   LogCreatedWhen: Date and time when record was first entered (= typed or imported) into this system. */
CREATE TABLE "Identification" (
    "IdentificationUnitID" 	int4	 NOT NULL,
    "CollectionSpecimenID" 	int4	 NOT NULL,
    "IdentificationSequence" 	timestamp	 NOT NULL DEFAULT current_timestamp,
    "IdentificationDateCache" 	timestamp	 NULL,
    "IdentificationDay" 	int2	 NULL,
    "IdentificationMonth" 	int2	 NULL,
    "IdentificationYear" 	int2	 NULL,
    "IdentificationDateSupplement" 	varchar(255)	 NULL,
    "VernacularName" 	varchar(255)	 NULL,
    "TaxonomicName" 	varchar(255)	 NULL,
    "NomID" 	int4	 NULL DEFAULT 0,
    "NomSignature" 	int4	 NULL DEFAULT 0,
    "IdentificationCategory" 	varchar(255)	 NULL,
    "IdentificationQualifier" 	varchar(50)	 NULL,
    "IsLastName" 	bool	 NULL,
    "LiteratureUsed" 	varchar(255)	 NULL,
    "Notes" 	varchar(255)	 NULL,
    "IsExsiccataTaxon" 	bool	 NULL,
    "ResponsibleName" 	varchar(50)	 NULL,
    "LogCreatedBy" 	int4	 NOT NULL,
    "LogCreatedWhen" 	timestamp	 NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY ("IdentificationUnitID","CollectionSpecimenID","IdentificationSequence")
);




/*=== Table: "IdentificationUnit" ===*/
/* Organism that is present in or on a collection specimen. */
  /*   IdentificationUnitID: ID of the identification unit (= part of Primary key). Usually one of possibly several organisms present on the collection specimen. Example: parasite with hyperparasite on leaf = 3 units, */
  /*   CollectionSpecimenID: ID of the collection specimen on which the identification unit is present (= Foreign key and part of primary key). */
  /*   TaxonomicGroup: Taxonomic group the organism identified by this unit belongs to. Groups are (1) plant, (2) fungus, (3) lichen, (4) animal, (5) prokaryotic, (6) virus, (7) other. */
  /*   OnlyObserved: True if the organism was only observed rather than collected. It is therefore not present on the preserved specimen. Example: Tree under which the collected mycorrhizal fungus grew. */
  /*   TypeStatus: If identification unit is type of a taxonomic name: holotype, syntype, etc. */
  /*   TypifiedTaxon: Scientific name of the taxon typified by this identification unit. */
  /*   SubstrateAssociation: The IdentificationUnitID of the organism or substrate, on which this organism is growing (= foreign key). */
  /*   ColonisedPart: If a SubstrateAssociation exists: part of the substrate that is affected in the interaction (e.g. 'leaves'). */
  /*   LifeStage: Examples: 'II, III' for spore generations of rusts or 'seed', 'seedling' etc. for higher plants. */
  /*   Notes: Further information on the identification unit or interaction, e. g. infection symptoms like 'producing galls'. */
CREATE TABLE "IdentificationUnit" (
    "IdentificationUnitID" 	SERIAL  	 NOT NULL,
    "CollectionSpecimenID" 	int4	 NOT NULL,
    "TaxonomicGroup" 	int4	 NOT NULL,
    "OnlyObserved" 	bool	 NOT NULL DEFAULT 'false',
    "TypeStatus" 	varchar(255)	 NULL,
    "TypifiedTaxon" 	varchar(255)	 NULL,
    "SubstrateAssociation" 	int4	 NULL,
    "ColonisedPart" 	varchar(255)	 NULL,
    "LifeStage" 	varchar(255)	 NULL,
    "Notes" 	varchar(255)	 NULL,
  PRIMARY KEY ("IdentificationUnitID","CollectionSpecimenID")
);




/*=== Table: "CollectionLookup" ===*/
/* Internal lists of values or text strings, used, e.g., to provide data for pick lists. */
  /*   Parameter: Name of the parameter, e.g. "TypeStatus". */
  /*   Value: Value of the parameter, e.g. "holotypus". */
  /*   Description: Description of the parameter. */
  /*   EnableEditing: True if this item can be edited by the user. */
CREATE TABLE "CollectionLookup" (
    "Parameter" 	varchar(255)	 NOT NULL,
    "Value" 	varchar(255)	 NOT NULL,
    "Description" 	varchar(255)	 NULL,
    "EnableEditing" 	bool	 NULL,
  PRIMARY KEY ("Parameter","Value")
);


ALTER TABLE "Collection" ADD 
  FOREIGN KEY ("TopmostCollectionID") REFERENCES "Collection" ("CollectionID"),
  FOREIGN KEY ("ParentCollectionID") REFERENCES "Collection" ("CollectionID") ON UPDATE CASCADE ON DELETE CASCADE
ALTER TABLE "CollectionDuplicate" ADD 
  FOREIGN KEY ("CollectionID") REFERENCES "Collection" ("CollectionID") ON UPDATE CASCADE,
  FOREIGN KEY ("CollectionSpecimenID") REFERENCES "CollectionSpecimen" ("CollectionSpecimenID") ON UPDATE CASCADE ON DELETE CASCADE
ALTER TABLE "CollectionStorage" ADD 
  FOREIGN KEY ("CollectionID") REFERENCES "Collection" ("CollectionID") ON UPDATE CASCADE ON DELETE CASCADE,
  FOREIGN KEY ("CollectionSpecimenID") REFERENCES "CollectionSpecimen" ("CollectionSpecimenID") ON UPDATE CASCADE ON DELETE CASCADE
ALTER TABLE "CollectingPerson" ADD 
  FOREIGN KEY ("CollectionSpecimenID") REFERENCES "CollectionSpecimen" ("CollectionSpecimenID") ON UPDATE CASCADE ON DELETE CASCADE
ALTER TABLE "CollectionImage" ADD 
  FOREIGN KEY ("CollectionSpecimenID") REFERENCES "CollectionSpecimen" ("CollectionSpecimenID") ON UPDATE CASCADE ON DELETE CASCADE
ALTER TABLE "CollectionProject" ADD 
  FOREIGN KEY ("CollectionSpecimenID") REFERENCES "CollectionSpecimen" ("CollectionSpecimenID") ON UPDATE CASCADE ON DELETE CASCADE
ALTER TABLE "CollectionGeography" ADD 
  FOREIGN KEY ("CollectionSpecimenID") REFERENCES "CollectionSpecimen" ("CollectionSpecimenID") ON UPDATE CASCADE ON DELETE CASCADE,
  FOREIGN KEY ("LocalisationSystemID") REFERENCES "LocalisationSystem" ("LocalisationSystemID") ON UPDATE CASCADE ON DELETE CASCADE
ALTER TABLE "LocalisationSystem" ADD 
  FOREIGN KEY ("LocalisationSystemTypeID") REFERENCES "LocalisationSystemType" ("LocalisationSystemTypeID") ON UPDATE CASCADE ON DELETE CASCADE
ALTER TABLE "Identification" ADD 
  FOREIGN KEY ("IdentificationUnitID","CollectionSpecimenID") REFERENCES "IdentificationUnit" ("IdentificationUnitID","CollectionSpecimenID") ON UPDATE CASCADE ON DELETE CASCADE
ALTER TABLE "IdentificationUnit" ADD 
  FOREIGN KEY ("CollectionSpecimenID") REFERENCES "CollectionSpecimen" ("CollectionSpecimenID") ON UPDATE CASCADE ON DELETE CASCADE