- the sets.setID - parts.partID relationship is the one that generated cycles in the model, so I deleted it. There is actually no need for it.
- I set the sets.setID, translations.lang, sampleRelationships.sampleRelID column as primary keys, as all the tables should have one and the database complains if they do not, when running the code generated by Lucid.
- I set the samples.pooled and measures.reportable data type to int, as there is no boolean data type in MS SQL Server. In the documentation, we should specify that 1/0 should be used instead of TRUE/FALSE. This improves the model compatibility with various databases.
All these changes were implemented in a duplicated tab named [Sorin ERD V2.0.0]. If you’re happy with them, please delete the old [ERD V2.0.0] tab and delete the “Sorin” from the name of this tab. Also please change back to black the PKs and ints that I marked in red.
What else I got:
- FK_sites.siteID, FK_datasets.datasetID, FK_protocols.protocolID relationships are reversed in the code exported by Lucid. I checked if starting the link from one or from the other column in the relationship influences the generated code but this is not the case. It may have to do with the order in which the column were added to the table but I don’t know for sure. The code should be fixed manually before generating the database.
- there were two relationships named FK_samples.protocolID in the code generated by Lucid, which is not allowable, so I renamed one to FK_samples.protocolID_partID.
This is working code for MS SQL Server:
CREATE TABLE [sets] (
[setID] varchar,
[setType] varchar,
[partID] varchar,
[partLabel] varchar,
[status] varchar,
[firstReleased] datetime,
[lastUpdated] datetime,
[changes] varchar,
[notes] varchar,
PRIMARY KEY ([setID])
);
CREATE TABLE [parts] (
[partID] varchar,
[partLabel] varchar,
[partType] varchar,
[shortName] varchar,
[partDesc] varchar,
[partInstr] varchar,
[domain] varchar,
[specimenSet] varchar,
[compartmentSet] varchar,
[group] varchar,
[class] varchar,
[nomenclature] varchar,
[ontologyRef] varchar,
[latExp] varchar,
[mmaSet] varchar,
[unitSet] varchar,
[aggreationScale] varchar,
[aggregationSet] varchar,
[qualitySet] varchar,
[missingnessSet] varchar,
[status] varchar,
[firstReleased] datetime,
[lastUpdated] datetime,
[changes] varchar,
[protocolSteps] varchar,
[protocolStepsRequired] varchar,
[protocolStepsOrder] int,
[protocolRelationships] varchar,
[protocolRelationshipsRequired] varchar,
[protocolRelationshipsOrder] int,
[measures] varchar,
[measuresRequired] varchar,
[measuresOrder] int,
[measureSets] varchar,
[measureSetsRequired] varchar,
[measureSetsOrder] int,
[datasets] varchar,
[datasetsRequired] varchar,
[datasetsOrder] int,
[sites] varchar,
[sitesRequired] varchar,
[sitesOrder] int,
[samples] varchar,
[samplesRequired] varchar,
[samplesOrder] int,
[addresses] varchar,
[addressesRequired] varchar,
[addressesOrder] int,
[contacts] varchar,
[contactsRequired] varchar,
[contactsOrder] int,
[organizations] varchar,
[organizationsRequired] varchar,
[organizationsOrder] int,
[instruments] varchar,
[instrumentsRequired] varchar,
[instrumentsOrder] int,
[polygons] varchar,
[polygonsRequired] varchar,
[polygonsOrder] int,
[languages] varchar,
[languagesRequired] varchar,
[languagesOrder] int,
[translations] varchar,
[translationsRequired] varchar,
[translationsOrder] int,
[parts] varchar,
[partsRequired] varchar,
[partsOrder] int,
[sets] varchar,
[setsRquired] varchar,
[setsOrder] int,
[qualityReports] varchar,
[qualityReportsRequired] varchar,
[qualityReportsOrder] int,
[sampleRelationships] varchar,
[sampleRelationshipsRequired] varchar,
[sampleRelationshipsOrder] int,
[protocols] varchar,
[protocolsRequired] varchar,
[protocolsOrder] int,
[countries] varchar,
[countriesRequired] varchar,
[countriesOrder] int,
[zones] varchar,
[zonesRequired] varchar,
[zonesOrder] int,
[refLink] varchar,
[dataType] varchar,
[minValue] int,
[maxValue] int,
[minLength] int,
[maxLength] int,
PRIMARY KEY ([partID]));
CREATE TABLE [wideNames] (
[wideName] varchar,
[label] varchar,
[charLength] int,
[descr] varchar,
[source] varchar,
[wideMeasure] varchar,
[wideProtocol] varchar,
[wideAttribute] varchar,
[wideNameType] varchar,
[reportTableName] varchar,
[reportTableInput] varchar,
[partTypeName] varchar,
[partTypeInput] varchar,
[compartmentName] varchar,
[compartmentInput] varchar,
[specimenName] varchar,
[specimenInput] varchar,
[fractionName] varchar,
[fractionInput] varchar,
[measureName] varchar,
[measureInput] varchar,
[methodName] varchar,
[methodInput] varchar,
[unitName] varchar,
[unitInput] varchar,
[aggregationName] varchar,
[aggregationInput] varchar,
[index] int,
[attributeName] varchar,
[attributeInput] varchar,
PRIMARY KEY ([wideName]));
CREATE TABLE [countries] (
[isoCode] varchar,
[isoCodeX] varchar,
[numCode] varchar,
[tld] varchar,
[nameEngl] varchar,
[nameOffical] varchar,
[sovereignity] varchar,
[countryExonym] varchar,
[capitalExonym] varchar,
[countryEndonym] varchar,
[capitalEndonym] varchar,
[langScript] varchar,
[phone] varchar,
[utc] varchar,
[utcDST] varchar,
PRIMARY KEY ([isoCode])
);
CREATE TABLE [zones] (
[isoCode] varchar,
[isoZone] varchar,
[zoneName] varchar,
PRIMARY KEY ([isoZone]));
CREATE TABLE [datasets] (
[parDatasetID] varchar,
[datasetID] varchar,
[datasetDate] datetime,
[name] varchar,
[license] varchar,
[descr] varchar,
[refLink] varchar,
[langID] int,
[funderCont] varchar,
[custodyCont] varchar,
[funderID] varchar,
[custodyID] varchar,
[lastEdited] datetime,
[notes] varchar,
PRIMARY KEY ([datasetID]));
CREATE TABLE [addresses] (
[addressID] varchar,
[datasetID] varchar,
[addL1] varchar,
[addL2] varchar,
[city] varchar,
[stateProvReg] varchar,
[pCode] varchar,
[country] varchar,
[lastEdited] datetime,
[notes] varchar,
PRIMARY KEY ([addressID]));
CREATE TABLE [organizations] (
[organizationID] varchar,
[name] varchar,
[descr] varchar,
[addressID] varchar,
[datasetID] varchar,
[orgType] varchar,
[orgLevel] varchar,
[orgSector] varchar,
[lastEdited] datetime,
[notes] varchar,
PRIMARY KEY ([organizationID]));
CREATE TABLE [contacts] (
[contactID] varchar,
[datasetID] varchar,
[organizationID] varchar,
[firstName] varchar,
[lastName] varchar,
[email] varchar,
[phone] varchar,
[role] varchar,
[lastEdited] datetime,
[notes] varchar,
PRIMARY KEY ([contactID]));
CREATE TABLE [instruments] (
[InstrumentID] varchar,
[datasetID] varchar,
[name] varchar,
[model] varchar,
[manufacturer] varchar,
[contactID] varchar,
[organizationID] varchar,
[descr] varchar,
[refLink] varchar,
[instType] varchar,
[insTypeOth] varchar,
[index] int,
[lastEdited] datetime,
[notes] varchar,
PRIMARY KEY ([InstrumentID]));
CREATE TABLE [protocolSteps] (
[stepID] varchar,
[method] varchar,
[measure] varchar,
[summ] varchar,
[sourceStep] varchar,
[stepVer] varchar,
[refLink] varchar,
[organizationID] varchar,
[contactID] varchar,
[InstrumentID] varchar,
[value] varchar,
[unit] varchar,
[aggregation] varchar,
[lastEdited] datetime,
[notes] varchar,
PRIMARY KEY ([stepID]));
CREATE TABLE [protocols] (
[sourceProtocol] varchar,
[protocolID] varchar,
[datasetID] varchar,
[name] varchar,
[summ] varchar,
[refLink] varchar,
[organizationID] varchar,
[contactID] varchar,
[protocolVersion] varchar,
[lastEdited] datetime,
[notes] varchar,
PRIMARY KEY ([protocolID]));
CREATE TABLE [protocolRelationships] (
[protocolIDContainer] varchar,
[protocolIDObj] varchar,
[stepIDObj] varchar,
[relationshipID] varchar,
[protocolIDSub] varchar,
[stepIDSub] varchar,
[lastEdited] datetime,
[notes] varchar);
CREATE TABLE [samples] (
[sampleID] varchar,
[protocolID] varchar,
[organizationID] varchar,
[contactID] varchar,
[siteID] varchar,
[purposeID] varchar,
[saMaterial] varchar,
[datasetID] varchar,
[origin] varchar,
[repType] varchar,
[collType] varchar,
[collPer] float,
[collNum] int,
[pooled] int,
[collDT] datetime,
[collDTStart] datetime,
[collDTEnd] datetime,
[sentDate] datetime,
[recDate] datetime,
[reportable] varchar,
[lastEdited] datetime,
[notes] varchar,
PRIMARY KEY ([sampleID]));
CREATE TABLE [sampleRelationships] (
[sampleIDSubject] varchar,
[sampleRelID] varchar,
[sampleIDObject] varchar,
[lastEdited] datetime,
[notes] varchar,
PRIMARY KEY ([sampleRelID]));
CREATE TABLE [polygons] (
[polygonID] varchar,
[datasetID] varchar,
[name] varchar,
[descr] varchar,
[polyPop] int,
[geoType] varchar,
[geoEPSG] varchar,
[geoWKT] varchar,
[fileLocation] varchar,
[refLink] varchar,
[organizationID] varchar,
[contactID] varchar,
[lastEdited] datetime,
[notes] varchar,
PRIMARY KEY ([polygonID]));
CREATE TABLE [sites] (
[parSiteID] varchar,
[siteID] varchar,
[datasetID] varchar,
[polygonID] varchar,
[siteType] varchar,
[sampleShed] varchar,
[addressID] varchar,
[organizationID] varchar,
[contactID] varchar,
[name] varchar,
[descr] varchar,
[repOrg1] varchar,
[repOrg2] varchar,
[healthReg] varchar,
[popServ] int,
[geoLat] varchar,
[geoLong] varchar,
[geoEPSG] varchar,
[lastEdited] datetime,
[notes] varchar,
PRIMARY KEY ([siteID]));
CREATE TABLE [measureSets] (
[measureSetRepID] varchar,
[protocolID] varchar,
[name] varchar,
[organizationID] varchar,
[contactID] varchar,
[lastEdited] datetime,
[notes] varchar,
PRIMARY KEY ([measureSetRepID]));
CREATE TABLE [measures] (
[measureRepID] varchar,
[protocolID] varchar,
[sampleID] varchar,
[purposeID] varchar,
[polygonID] varchar,
[siteID] varchar,
[datasetID] varchar,
[measureSetRepID] varchar,
[aDateStart] datetime,
[aDateEnd] datetime,
[reportDate] datetime,
[compartment] varchar,
[specimenID] varchar,
[fraction] varchar,
[group] varchar,
[class] varchar,
[measure] varchar,
[value] varchar,
[unit] varchar,
[aggregation] varchar,
[nomenclature] varchar,
[index] int,
[measureLic] varchar,
[reportable] int,
[organizationID] varchar,
[contactID] varchar,
[refLink] varchar,
[lastEdited] datetime,
PRIMARY KEY ([measureRepID]));
CREATE TABLE [qualityReports] (
[quality] varchar,
[measureRepID] varchar,
[sampleID] varchar,
[measureSetRepID] varchar,
[qualityFlag] varchar,
[severity] varchar,
[lastEdited] datetime,
[notes] varchar,
PRIMARY KEY ([quality]));
CREATE TABLE [languages] (
[lang] varchar,
[langFam] varchar,
[langName] varchar,
[natName] varchar,
[ISO6391] varchar,
[ISO6392B] varchar,
[ISO6392T] varchar,
[ISO6393] varchar,
[ISO6396] varchar,
[firstRelased] datetime,
[lastUpdated] datetime,
[changes] varchar,
[notes] varchar,
PRIMARY KEY ([lang])
);
CREATE TABLE [translations] (
[lang] varchar,
[part] varchar,
[partLabel] varchar,
[partDesc] varchar,
[partInstr] varchar,
[firstReleased] datetime,
[lastUpdated] datetime,
[changes] varchar,
[notes] varchar,
PRIMARY KEY ([lang]));
;
ALTER TABLE [parts] ADD
CONSTRAINT [FK_parts.missingnessSet]
FOREIGN KEY ([missingnessSet])
REFERENCES [sets]([setID]),
CONSTRAINT [FK_parts.compartmentSet]
FOREIGN KEY ([compartmentSet])
REFERENCES [sets]([setID]),
CONSTRAINT [FK_parts.aggregationSet]
FOREIGN KEY ([aggregationSet])
REFERENCES [sets]([setID]),
CONSTRAINT [FK_parts.specimenSet]
FOREIGN KEY ([specimenSet])
REFERENCES [sets]([setID]),
CONSTRAINT [FK_parts.qualitySet]
FOREIGN KEY ([qualitySet])
REFERENCES [sets]([setID]),
CONSTRAINT [FK_parts.unitSet]
FOREIGN KEY ([unitSet])
REFERENCES [sets]([setID])
;
ALTER TABLE [wideNames] ADD
CONSTRAINT [FK_wideNames.attributeInput]
FOREIGN KEY ([attributeInput])
REFERENCES [parts]([partID]),
CONSTRAINT [FK_wideNames.aggregationInput]
FOREIGN KEY ([aggregationInput])
REFERENCES [parts]([partID]),
CONSTRAINT [FK_wideNames.unitInput]
FOREIGN KEY ([unitInput])
REFERENCES [parts]([partID]),
CONSTRAINT [FK_wideNames.methodInput]
FOREIGN KEY ([methodInput])
REFERENCES [parts]([partID]),
CONSTRAINT [FK_wideNames.measureInput]
FOREIGN KEY ([measureInput])
REFERENCES [parts]([partID]),
CONSTRAINT [FK_wideNames.fractionInput]
FOREIGN KEY ([fractionInput])
REFERENCES [parts]([partID]),
CONSTRAINT [FK_wideNames.compartmentInput]
FOREIGN KEY ([compartmentInput])
REFERENCES [parts]([partID]),
CONSTRAINT [FK_wideNames.partTypeInput]
FOREIGN KEY ([partTypeInput])
REFERENCES [parts]([partID]),
CONSTRAINT [FK_wideNames.reportTableInput]
FOREIGN KEY ([reportTableInput])
REFERENCES [parts]([partID]),
CONSTRAINT [FK_wideNames.specimenInput]
FOREIGN KEY ([specimenInput])
REFERENCES [parts]([partID])
;
ALTER TABLE [zones] ADD
CONSTRAINT [FK_zones.isoCode]
FOREIGN KEY ([isoCode])
REFERENCES [countries]([isoCode])
;
ALTER TABLE [datasets] ADD
CONSTRAINT [FK_datasets.parDatasetID]
FOREIGN KEY ([datasetID])
REFERENCES [datasets]([datasetID])
;
ALTER TABLE [addresses] ADD
CONSTRAINT [FK_addresses.stateProvReg]
FOREIGN KEY ([stateProvReg])
REFERENCES [zones]([isoZone]),
CONSTRAINT [FK_addresses.country]
FOREIGN KEY ([country])
REFERENCES [countries]([isoCode]),
CONSTRAINT [FK_addresses.datasetID]
FOREIGN KEY ([datasetID])
REFERENCES [datasets]([datasetID])
;
ALTER TABLE [organizations] ADD
CONSTRAINT [FK_organizations.datasetID]
FOREIGN KEY ([datasetID])
REFERENCES [datasets]([datasetID]),
CONSTRAINT [FK_organizations.addressID]
FOREIGN KEY ([addressID])
REFERENCES [addresses]([addressID])
;
ALTER TABLE [contacts] ADD
CONSTRAINT [FK_contacts.datasetID]
FOREIGN KEY ([datasetID])
REFERENCES [datasets]([datasetID]),
CONSTRAINT [FK_contacts.organizationID]
FOREIGN KEY ([organizationID])
REFERENCES [organizations]([organizationID])
;
ALTER TABLE [instruments] ADD
CONSTRAINT [FK_instruments.instType]
FOREIGN KEY ([instType])
REFERENCES [parts]([partID]),
CONSTRAINT [FK_instruments.datasetID]
FOREIGN KEY ([datasetID])
REFERENCES [datasets]([datasetID]),
CONSTRAINT [FK_instruments.contactID]
FOREIGN KEY ([contactID])
REFERENCES [contacts]([contactID]),
CONSTRAINT [FK_instruments.organizationID]
FOREIGN KEY ([organizationID])
REFERENCES [organizations]([organizationID])
;
ALTER TABLE [protocolSteps] ADD
CONSTRAINT [FK_protocolSteps.aggregation]
FOREIGN KEY ([aggregation])
REFERENCES [parts]([partID]),
CONSTRAINT [FK_protocolSteps.unit]
FOREIGN KEY ([unit])
REFERENCES [parts]([partID]),
CONSTRAINT [FK_protocolSteps.sourceStep]
FOREIGN KEY ([sourceStep])
REFERENCES [protocolSteps]([stepID]),
CONSTRAINT [FK_protocolSteps.contactID]
FOREIGN KEY ([contactID])
REFERENCES [contacts]([contactID]),
CONSTRAINT [FK_protocolSteps.organizationID]
FOREIGN KEY ([organizationID])
REFERENCES [organizations]([organizationID]),
CONSTRAINT [FK_protocolSteps.InstrumentID]
FOREIGN KEY ([InstrumentID])
REFERENCES [instruments]([InstrumentID])
;
ALTER TABLE [protocols] ADD
CONSTRAINT [FK_protocols.datasetID]
FOREIGN KEY ([datasetID])
REFERENCES [datasets]([datasetID]),
CONSTRAINT [FK_protocols.contactID]
FOREIGN KEY ([contactID])
REFERENCES [contacts]([contactID]),
CONSTRAINT [FK_protocols.organizationID]
FOREIGN KEY ([organizationID])
REFERENCES [organizations]([organizationID]),
CONSTRAINT [FK_protocols.sourceProtocol]
FOREIGN KEY ([protocolID])
REFERENCES [protocols]([protocolID])
;
ALTER TABLE [protocolRelationships] ADD
CONSTRAINT [FK_protocolRelationships.stepIDObj]
FOREIGN KEY ([stepIDObj])
REFERENCES [protocolSteps]([stepID]),
CONSTRAINT [FK_protocolRelationships.protocolIDSub]
FOREIGN KEY ([protocolIDSub])
REFERENCES [protocols]([protocolID]),
CONSTRAINT [FK_protocolRelationships.protocolIDContainer]
FOREIGN KEY ([protocolIDContainer])
REFERENCES [protocols]([protocolID]),
CONSTRAINT [FK_protocolRelationships.stepIDSub]
FOREIGN KEY ([stepIDSub])
REFERENCES [protocolSteps]([stepID]),
CONSTRAINT [FK_protocolRelationships.protocolIDObj]
FOREIGN KEY ([protocolIDObj])
REFERENCES [protocols]([protocolID])
;
ALTER TABLE [samples] ADD
CONSTRAINT [FK_samples.collType]
FOREIGN KEY ([collType])
REFERENCES [parts]([partID]),
CONSTRAINT [FK_samples.repType]
FOREIGN KEY ([repType])
REFERENCES [parts]([partID]),
CONSTRAINT [FK_samples.saMaterial]
FOREIGN KEY ([saMaterial])
REFERENCES [parts]([partID]),
CONSTRAINT [FK_samples.purposeID]
FOREIGN KEY ([purposeID])
REFERENCES [parts]([partID]),
CONSTRAINT [FK_samples.protocolID_partID]
FOREIGN KEY ([protocolID])
REFERENCES [parts]([partID]),
CONSTRAINT [FK_samples.organizationID]
FOREIGN KEY ([organizationID])
REFERENCES [organizations]([organizationID]),
CONSTRAINT [FK_samples.protocolID]
FOREIGN KEY ([protocolID])
REFERENCES [protocols]([protocolID]),
CONSTRAINT [FK_samples.datasetID]
FOREIGN KEY ([datasetID])
REFERENCES [datasets]([datasetID]),
CONSTRAINT [FK_samples.contactID]
FOREIGN KEY ([contactID])
REFERENCES [contacts]([contactID])
;
ALTER TABLE [sampleRelationships] ADD
CONSTRAINT [FK_sampleRelationships.sampleRelID]
FOREIGN KEY ([sampleRelID])
REFERENCES [parts]([partID]),
CONSTRAINT [FK_sampleRelationships.sampleIDSubject]
FOREIGN KEY ([sampleIDSubject])
REFERENCES [samples]([sampleID]),
CONSTRAINT [FK_sampleRelationships.sampleIDObject]
FOREIGN KEY ([sampleIDObject])
REFERENCES [samples]([sampleID])
;
ALTER TABLE [polygons] ADD
CONSTRAINT [FK_polygons.datasetID]
FOREIGN KEY ([datasetID])
REFERENCES [datasets]([datasetID]),
CONSTRAINT [FK_polygons.contactID]
FOREIGN KEY ([contactID])
REFERENCES [contacts]([contactID]),
CONSTRAINT [FK_polygons.organizationID]
FOREIGN KEY ([organizationID])
REFERENCES [organizations]([organizationID])
;
ALTER TABLE [sites] ADD
CONSTRAINT [FK_sites.repOrg2]
FOREIGN KEY ([repOrg2])
REFERENCES [organizations]([organizationID]),
CONSTRAINT [FK_sites.repOrg1]
FOREIGN KEY ([repOrg1])
REFERENCES [organizations]([organizationID]),
CONSTRAINT [FK_sites.parSiteID]
FOREIGN KEY ([siteID])
REFERENCES [sites]([siteID]),
CONSTRAINT [FK_sites.siteType]
FOREIGN KEY ([siteType])
REFERENCES [parts]([partID]),
CONSTRAINT [FK_sites.organizationID]
FOREIGN KEY ([organizationID])
REFERENCES [organizations]([organizationID]),
CONSTRAINT [FK_sites.datasetID]
FOREIGN KEY ([datasetID])
REFERENCES [datasets]([datasetID]),
CONSTRAINT [FK_sites.polygonID]
FOREIGN KEY ([polygonID])
REFERENCES [polygons]([polygonID])
;
ALTER TABLE [measureSets] ADD
CONSTRAINT [FK_measureSets.contactID]
FOREIGN KEY ([contactID])
REFERENCES [contacts]([contactID]),
CONSTRAINT [FK_measureSets.protocolID]
FOREIGN KEY ([protocolID])
REFERENCES [protocols]([protocolID]),
CONSTRAINT [FK_measureSets.organizationID]
FOREIGN KEY ([organizationID])
REFERENCES [organizations]([organizationID])
;
ALTER TABLE [measures] ADD
CONSTRAINT [FK_measures.siteID]
FOREIGN KEY ([siteID])
REFERENCES [sites]([siteID]),
CONSTRAINT [FK_measures.nomenclature]
FOREIGN KEY ([nomenclature])
REFERENCES [parts]([partID]),
CONSTRAINT [FK_measures.contactID]
FOREIGN KEY ([contactID])
REFERENCES [contacts]([contactID]),
CONSTRAINT [FK_measures.measureLic]
FOREIGN KEY ([measureLic])
REFERENCES [parts]([partID]),
CONSTRAINT [FK_measures.unit]
FOREIGN KEY ([unit])
REFERENCES [parts]([partID]),
CONSTRAINT [FK_measures.fraction]
FOREIGN KEY ([fraction])
REFERENCES [parts]([partID]),
CONSTRAINT [FK_measures.organizationID]
FOREIGN KEY ([organizationID])
REFERENCES [organizations]([organizationID]),
CONSTRAINT [FK_measures.aggregation]
FOREIGN KEY ([aggregation])
REFERENCES [parts]([partID]),
CONSTRAINT [FK_measures.measure]
FOREIGN KEY ([measure])
REFERENCES [parts]([partID]),
CONSTRAINT [FK_measures.class]
FOREIGN KEY ([class])
REFERENCES [parts]([partID]),
CONSTRAINT [FK_measures.group]
FOREIGN KEY ([group])
REFERENCES [parts]([partID]),
CONSTRAINT [FK_measures.specimenID]
FOREIGN KEY ([specimenID])
REFERENCES [parts]([partID]),
CONSTRAINT [FK_measures.purposeID]
FOREIGN KEY ([purposeID])
REFERENCES [parts]([partID]),
CONSTRAINT [FK_measures.datasetID]
FOREIGN KEY ([datasetID])
REFERENCES [datasets]([datasetID]),
CONSTRAINT [FK_measures.polygonID]
FOREIGN KEY ([polygonID])
REFERENCES [polygons]([polygonID]),
CONSTRAINT [FK_measures.measureSetRepID]
FOREIGN KEY ([measureSetRepID])
REFERENCES [measureSets]([measureSetRepID]),
CONSTRAINT [FK_measures.protocolID]
FOREIGN KEY ([protocolID])
REFERENCES [protocols]([protocolID]),
CONSTRAINT [FK_measures.sampleID]
FOREIGN KEY ([sampleID])
REFERENCES [samples]([sampleID])
;
ALTER TABLE [qualityReports] ADD
CONSTRAINT [FK_qualityReports.quality]
FOREIGN KEY ([quality])
REFERENCES [parts]([partID]),
CONSTRAINT [FK_qualityReports.measureRepID]
FOREIGN KEY ([measureRepID])
REFERENCES [measures]([measureRepID]),
CONSTRAINT [FK_qualityReports.measureSetRepID]
FOREIGN KEY ([measureSetRepID])
REFERENCES [measureSets]([measureSetRepID]),
CONSTRAINT [FK_qualityReports.sampleID]
FOREIGN KEY ([sampleID])
REFERENCES [samples]([sampleID])
;
ALTER TABLE [translations] ADD
CONSTRAINT [FK_translations.lang]
FOREIGN KEY ([lang])
REFERENCES [languages]([lang]),
CONSTRAINT [FK_translations.part]
FOREIGN KEY ([part])
REFERENCES [parts]([partID])
;
