# Cycles in the code generated by Lucid

**URL:** <https://odm.discourse.group/t/cycles-in-the-code-generated-by-lucid/135>\
**Category:** Bugs\
**Created:** [September 2, 2023, 11:09am UTC](https://odm.discourse.group/t/cycles-in-the-code-generated-by-lucid/135 "2023-09-02T11:09:53Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![Sorin](https://yyz2.discourse-cdn.com/free1/user_avatar/odm.discourse.group/sorin/32/52_2.png) [@Sorin](https://odm.discourse.group/u/Sorin)\
**Post date:** [September 2, 2023, 11:09am UTC](https://odm.discourse.group/t/cycles-in-the-code-generated-by-lucid/135/1 "2023-09-02T11:09:53Z")

</div>

- 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 **PK** s and **int** s 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:

```auto
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])
;

```

---

<div class="post-metadata">

**Author:** ![Sorin](https://yyz2.discourse-cdn.com/free1/user_avatar/odm.discourse.group/sorin/32/52_2.png) [@Sorin](https://odm.discourse.group/u/Sorin)\
**Post date:** [September 2, 2023, 12:12pm UTC](https://odm.discourse.group/t/cycles-in-the-code-generated-by-lucid/135/2 "2023-09-02T12:12:48Z")

</div>

… and this is actual code exported from MS SQL Server with proper column sizes (since not specifying a size for the varchar type would generate a size 1 column).  
I chose to define most varchar columns as nvarchar(450) since columns above 900 bytes cannot be included in indexes and nvarchar takes 2 bytes for each character.  
Description and Notes fields were allowed 4000 characters and geoWKT was defined as (MAX) - up to 2GB characters.

```auto
ALTER TABLE [dbo].[zones] DROP CONSTRAINT IF EXISTS [FK_zones.isoCode]
GO
ALTER TABLE [dbo].[wideNames] DROP CONSTRAINT IF EXISTS [FK_wideNames.unitInput]
GO
ALTER TABLE [dbo].[wideNames] DROP CONSTRAINT IF EXISTS [FK_wideNames.specimenInput]
GO
ALTER TABLE [dbo].[wideNames] DROP CONSTRAINT IF EXISTS [FK_wideNames.reportTableInput]
GO
ALTER TABLE [dbo].[wideNames] DROP CONSTRAINT IF EXISTS [FK_wideNames.partTypeInput]
GO
ALTER TABLE [dbo].[wideNames] DROP CONSTRAINT IF EXISTS [FK_wideNames.methodInput]
GO
ALTER TABLE [dbo].[wideNames] DROP CONSTRAINT IF EXISTS [FK_wideNames.measureInput]
GO
ALTER TABLE [dbo].[wideNames] DROP CONSTRAINT IF EXISTS [FK_wideNames.fractionInput]
GO
ALTER TABLE [dbo].[wideNames] DROP CONSTRAINT IF EXISTS [FK_wideNames.compartmentInput]
GO
ALTER TABLE [dbo].[wideNames] DROP CONSTRAINT IF EXISTS [FK_wideNames.attributeInput]
GO
ALTER TABLE [dbo].[wideNames] DROP CONSTRAINT IF EXISTS [FK_wideNames.aggregationInput]
GO
ALTER TABLE [dbo].[translations] DROP CONSTRAINT IF EXISTS [FK_translations.part]
GO
ALTER TABLE [dbo].[translations] DROP CONSTRAINT IF EXISTS [FK_translations.lang]
GO
ALTER TABLE [dbo].[sites] DROP CONSTRAINT IF EXISTS [FK_sites.siteType]
GO
ALTER TABLE [dbo].[sites] DROP CONSTRAINT IF EXISTS [FK_sites.repOrg2]
GO
ALTER TABLE [dbo].[sites] DROP CONSTRAINT IF EXISTS [FK_sites.repOrg1]
GO
ALTER TABLE [dbo].[sites] DROP CONSTRAINT IF EXISTS [FK_sites.polygonID]
GO
ALTER TABLE [dbo].[sites] DROP CONSTRAINT IF EXISTS [FK_sites.parSiteID]
GO
ALTER TABLE [dbo].[sites] DROP CONSTRAINT IF EXISTS [FK_sites.organizationID]
GO
ALTER TABLE [dbo].[sites] DROP CONSTRAINT IF EXISTS [FK_sites.datasetID]
GO
ALTER TABLE [dbo].[samples] DROP CONSTRAINT IF EXISTS [FK_samples.saMaterial]
GO
ALTER TABLE [dbo].[samples] DROP CONSTRAINT IF EXISTS [FK_samples.repType]
GO
ALTER TABLE [dbo].[samples] DROP CONSTRAINT IF EXISTS [FK_samples.purposeID]
GO
ALTER TABLE [dbo].[samples] DROP CONSTRAINT IF EXISTS [FK_samples.protocolID_partID]
GO
ALTER TABLE [dbo].[samples] DROP CONSTRAINT IF EXISTS [FK_samples.protocolID]
GO
ALTER TABLE [dbo].[samples] DROP CONSTRAINT IF EXISTS [FK_samples.organizationID]
GO
ALTER TABLE [dbo].[samples] DROP CONSTRAINT IF EXISTS [FK_samples.datasetID]
GO
ALTER TABLE [dbo].[samples] DROP CONSTRAINT IF EXISTS [FK_samples.contactID]
GO
ALTER TABLE [dbo].[samples] DROP CONSTRAINT IF EXISTS [FK_samples.collType]
GO
ALTER TABLE [dbo].[sampleRelationships] DROP CONSTRAINT IF EXISTS [FK_sampleRelationships.sampleRelID]
GO
ALTER TABLE [dbo].[sampleRelationships] DROP CONSTRAINT IF EXISTS [FK_sampleRelationships.sampleIDSubject]
GO
ALTER TABLE [dbo].[sampleRelationships] DROP CONSTRAINT IF EXISTS [FK_sampleRelationships.sampleIDObject]
GO
ALTER TABLE [dbo].[qualityReports] DROP CONSTRAINT IF EXISTS [FK_qualityReports.sampleID]
GO
ALTER TABLE [dbo].[qualityReports] DROP CONSTRAINT IF EXISTS [FK_qualityReports.quality]
GO
ALTER TABLE [dbo].[qualityReports] DROP CONSTRAINT IF EXISTS [FK_qualityReports.measureSetRepID]
GO
ALTER TABLE [dbo].[qualityReports] DROP CONSTRAINT IF EXISTS [FK_qualityReports.measureRepID]
GO
ALTER TABLE [dbo].[protocolSteps] DROP CONSTRAINT IF EXISTS [FK_protocolSteps.unit]
GO
ALTER TABLE [dbo].[protocolSteps] DROP CONSTRAINT IF EXISTS [FK_protocolSteps.sourceStep]
GO
ALTER TABLE [dbo].[protocolSteps] DROP CONSTRAINT IF EXISTS [FK_protocolSteps.organizationID]
GO
ALTER TABLE [dbo].[protocolSteps] DROP CONSTRAINT IF EXISTS [FK_protocolSteps.InstrumentID]
GO
ALTER TABLE [dbo].[protocolSteps] DROP CONSTRAINT IF EXISTS [FK_protocolSteps.contactID]
GO
ALTER TABLE [dbo].[protocolSteps] DROP CONSTRAINT IF EXISTS [FK_protocolSteps.aggregation]
GO
ALTER TABLE [dbo].[protocols] DROP CONSTRAINT IF EXISTS [FK_protocols.sourceProtocol]
GO
ALTER TABLE [dbo].[protocols] DROP CONSTRAINT IF EXISTS [FK_protocols.organizationID]
GO
ALTER TABLE [dbo].[protocols] DROP CONSTRAINT IF EXISTS [FK_protocols.datasetID]
GO
ALTER TABLE [dbo].[protocols] DROP CONSTRAINT IF EXISTS [FK_protocols.contactID]
GO
ALTER TABLE [dbo].[protocolRelationships] DROP CONSTRAINT IF EXISTS [FK_protocolRelationships.stepIDSub]
GO
ALTER TABLE [dbo].[protocolRelationships] DROP CONSTRAINT IF EXISTS [FK_protocolRelationships.stepIDObj]
GO
ALTER TABLE [dbo].[protocolRelationships] DROP CONSTRAINT IF EXISTS [FK_protocolRelationships.protocolIDSub]
GO
ALTER TABLE [dbo].[protocolRelationships] DROP CONSTRAINT IF EXISTS [FK_protocolRelationships.protocolIDObj]
GO
ALTER TABLE [dbo].[protocolRelationships] DROP CONSTRAINT IF EXISTS [FK_protocolRelationships.protocolIDContainer]
GO
ALTER TABLE [dbo].[polygons] DROP CONSTRAINT IF EXISTS [FK_polygons.organizationID]
GO
ALTER TABLE [dbo].[polygons] DROP CONSTRAINT IF EXISTS [FK_polygons.datasetID]
GO
ALTER TABLE [dbo].[polygons] DROP CONSTRAINT IF EXISTS [FK_polygons.contactID]
GO
ALTER TABLE [dbo].[parts] DROP CONSTRAINT IF EXISTS [FK_parts.unitSet]
GO
ALTER TABLE [dbo].[parts] DROP CONSTRAINT IF EXISTS [FK_parts.specimenSet]
GO
ALTER TABLE [dbo].[parts] DROP CONSTRAINT IF EXISTS [FK_parts.qualitySet]
GO
ALTER TABLE [dbo].[parts] DROP CONSTRAINT IF EXISTS [FK_parts.missingnessSet]
GO
ALTER TABLE [dbo].[parts] DROP CONSTRAINT IF EXISTS [FK_parts.compartmentSet]
GO
ALTER TABLE [dbo].[parts] DROP CONSTRAINT IF EXISTS [FK_parts.aggregationSet]
GO
ALTER TABLE [dbo].[organizations] DROP CONSTRAINT IF EXISTS [FK_organizations.datasetID]
GO
ALTER TABLE [dbo].[organizations] DROP CONSTRAINT IF EXISTS [FK_organizations.addressID]
GO
ALTER TABLE [dbo].[measureSets] DROP CONSTRAINT IF EXISTS [FK_measureSets.protocolID]
GO
ALTER TABLE [dbo].[measureSets] DROP CONSTRAINT IF EXISTS [FK_measureSets.organizationID]
GO
ALTER TABLE [dbo].[measureSets] DROP CONSTRAINT IF EXISTS [FK_measureSets.contactID]
GO
ALTER TABLE [dbo].[measures] DROP CONSTRAINT IF EXISTS [FK_measures.unit]
GO
ALTER TABLE [dbo].[measures] DROP CONSTRAINT IF EXISTS [FK_measures.specimenID]
GO
ALTER TABLE [dbo].[measures] DROP CONSTRAINT IF EXISTS [FK_measures.siteID]
GO
ALTER TABLE [dbo].[measures] DROP CONSTRAINT IF EXISTS [FK_measures.sampleID]
GO
ALTER TABLE [dbo].[measures] DROP CONSTRAINT IF EXISTS [FK_measures.purposeID]
GO
ALTER TABLE [dbo].[measures] DROP CONSTRAINT IF EXISTS [FK_measures.protocolID]
GO
ALTER TABLE [dbo].[measures] DROP CONSTRAINT IF EXISTS [FK_measures.polygonID]
GO
ALTER TABLE [dbo].[measures] DROP CONSTRAINT IF EXISTS [FK_measures.organizationID]
GO
ALTER TABLE [dbo].[measures] DROP CONSTRAINT IF EXISTS [FK_measures.nomenclature]
GO
ALTER TABLE [dbo].[measures] DROP CONSTRAINT IF EXISTS [FK_measures.measureSetRepID]
GO
ALTER TABLE [dbo].[measures] DROP CONSTRAINT IF EXISTS [FK_measures.measureLic]
GO
ALTER TABLE [dbo].[measures] DROP CONSTRAINT IF EXISTS [FK_measures.measure]
GO
ALTER TABLE [dbo].[measures] DROP CONSTRAINT IF EXISTS [FK_measures.group]
GO
ALTER TABLE [dbo].[measures] DROP CONSTRAINT IF EXISTS [FK_measures.fraction]
GO
ALTER TABLE [dbo].[measures] DROP CONSTRAINT IF EXISTS [FK_measures.datasetID]
GO
ALTER TABLE [dbo].[measures] DROP CONSTRAINT IF EXISTS [FK_measures.contactID]
GO
ALTER TABLE [dbo].[measures] DROP CONSTRAINT IF EXISTS [FK_measures.class]
GO
ALTER TABLE [dbo].[measures] DROP CONSTRAINT IF EXISTS [FK_measures.aggregation]
GO
ALTER TABLE [dbo].[instruments] DROP CONSTRAINT IF EXISTS [FK_instruments.organizationID]
GO
ALTER TABLE [dbo].[instruments] DROP CONSTRAINT IF EXISTS [FK_instruments.instType]
GO
ALTER TABLE [dbo].[instruments] DROP CONSTRAINT IF EXISTS [FK_instruments.datasetID]
GO
ALTER TABLE [dbo].[instruments] DROP CONSTRAINT IF EXISTS [FK_instruments.contactID]
GO
ALTER TABLE [dbo].[datasets] DROP CONSTRAINT IF EXISTS [FK_datasets.parDatasetID]
GO
ALTER TABLE [dbo].[contacts] DROP CONSTRAINT IF EXISTS [FK_contacts.organizationID]
GO
ALTER TABLE [dbo].[contacts] DROP CONSTRAINT IF EXISTS [FK_contacts.datasetID]
GO
ALTER TABLE [dbo].[addresses] DROP CONSTRAINT IF EXISTS [FK_addresses.stateProvReg]
GO
ALTER TABLE [dbo].[addresses] DROP CONSTRAINT IF EXISTS [FK_addresses.datasetID]
GO
ALTER TABLE [dbo].[addresses] DROP CONSTRAINT IF EXISTS [FK_addresses.country]
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[zones]') AND type in (N'U'))
DROP TABLE IF EXISTS [dbo].[zones]
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[wideNames]') AND type in (N'U'))
DROP TABLE IF EXISTS [dbo].[wideNames]
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[translations]') AND type in (N'U'))
DROP TABLE IF EXISTS [dbo].[translations]
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sites]') AND type in (N'U'))
DROP TABLE IF EXISTS [dbo].[sites]
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sets]') AND type in (N'U'))
DROP TABLE IF EXISTS [dbo].[sets]
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[samples]') AND type in (N'U'))
DROP TABLE IF EXISTS [dbo].[samples]
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sampleRelationships]') AND type in (N'U'))
DROP TABLE IF EXISTS [dbo].[sampleRelationships]
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[qualityReports]') AND type in (N'U'))
DROP TABLE IF EXISTS [dbo].[qualityReports]
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[protocolSteps]') AND type in (N'U'))
DROP TABLE IF EXISTS [dbo].[protocolSteps]
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[protocols]') AND type in (N'U'))
DROP TABLE IF EXISTS [dbo].[protocols]
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[protocolRelationships]') AND type in (N'U'))
DROP TABLE IF EXISTS [dbo].[protocolRelationships]
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[polygons]') AND type in (N'U'))
DROP TABLE IF EXISTS [dbo].[polygons]
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[parts]') AND type in (N'U'))
DROP TABLE IF EXISTS [dbo].[parts]
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[organizations]') AND type in (N'U'))
DROP TABLE IF EXISTS [dbo].[organizations]
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[measureSets]') AND type in (N'U'))
DROP TABLE IF EXISTS [dbo].[measureSets]
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[measures]') AND type in (N'U'))
DROP TABLE IF EXISTS [dbo].[measures]
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[languages]') AND type in (N'U'))
DROP TABLE IF EXISTS [dbo].[languages]
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[instruments]') AND type in (N'U'))
DROP TABLE IF EXISTS [dbo].[instruments]
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[datasets]') AND type in (N'U'))
DROP TABLE IF EXISTS [dbo].[datasets]
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[countries]') AND type in (N'U'))
DROP TABLE IF EXISTS [dbo].[countries]
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[contacts]') AND type in (N'U'))
DROP TABLE IF EXISTS [dbo].[contacts]
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[addresses]') AND type in (N'U'))
DROP TABLE IF EXISTS [dbo].[addresses]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[addresses](
	[addressID] [nvarchar](450) NOT NULL,
	[datasetID] [nvarchar](450) NULL,
	[addL1] [nvarchar](450) NULL,
	[addL2] [nvarchar](450) NULL,
	[city] [nvarchar](450) NULL,
	[stateProvReg] [nvarchar](450) NULL,
	[pCode] [nvarchar](450) NULL,
	[country] [nvarchar](450) NULL,
	[lastEdited] [datetime2] NULL,
	[notes] [nvarchar](4000) NULL,
 CONSTRAINT [PK __addresse__ 26A1118D36EF0D50] PRIMARY KEY CLUSTERED 
(
	[addressID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[contacts](
	[contactID] [nvarchar](450) NOT NULL,
	[datasetID] [nvarchar](450) NULL,
	[organizationID] [nvarchar](450) NULL,
	[firstName] [nvarchar](450) NULL,
	[lastName] [nvarchar](450) NULL,
	[email] [nvarchar](450) NULL,
	[phone] [nvarchar](450) NULL,
	[role] [nvarchar](450) NULL,
	[lastEdited] [datetime2] NULL,
	[notes] [nvarchar](4000) NULL,
 CONSTRAINT [PK __contacts__ 7121FD15AD0C0727] PRIMARY KEY CLUSTERED 
(
	[contactID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[countries](
	[isoCode] [nvarchar](450) NOT NULL,
	[isoCodeX] [nvarchar](450) NULL,
	[numCode] [nvarchar](450) NULL,
	[tld] [nvarchar](450) NULL,
	[nameEngl] [nvarchar](450) NULL,
	[nameOffical] [nvarchar](450) NULL,
	[sovereignity] [nvarchar](450) NULL,
	[countryExonym] [nvarchar](450) NULL,
	[capitalExonym] [nvarchar](450) NULL,
	[countryEndonym] [nvarchar](450) NULL,
	[capitalEndonym] [nvarchar](450) NULL,
	[langScript] [nvarchar](450) NULL,
	[phone] [nvarchar](450) NULL,
	[utc] [nvarchar](450) NULL,
	[utcDST] [nvarchar](450) NULL,
 CONSTRAINT [PK __countrie__ 659C56C34FC2A72C] PRIMARY KEY CLUSTERED 
(
	[isoCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[datasets](
	[parDatasetID] [nvarchar](450) NULL,
	[datasetID] [nvarchar](450) NOT NULL,
	[datasetDate] [datetime2] NULL,
	[name] [nvarchar](450) NULL,
	[license] [nvarchar](450) NULL,
	[descr] [nvarchar](450) NULL,
	[refLink] [nvarchar](4000) NULL,
	[langID] [int] NULL,
	[funderCont] [nvarchar](450) NULL,
	[custodyCont] [nvarchar](450) NULL,
	[funderID] [nvarchar](450) NULL,
	[custodyID] [nvarchar](450) NULL,
	[lastEdited] [datetime2] NULL,
	[notes] [nvarchar](4000) NULL,
 CONSTRAINT [PK __datasets__ 02DFF0A143515AEA] PRIMARY KEY CLUSTERED 
(
	[datasetID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[instruments](
	[InstrumentID] [nvarchar](450) NOT NULL,
	[datasetID] [nvarchar](450) NULL,
	[name] [nvarchar](450) NULL,
	[model] [nvarchar](450) NULL,
	[manufacturer] [nvarchar](450) NULL,
	[contactID] [nvarchar](450) NULL,
	[organizationID] [nvarchar](450) NULL,
	[descr] [nvarchar](450) NULL,
	[refLink] [nvarchar](4000) NULL,
	[instType] [nvarchar](450) NULL,
	[insTypeOth] [nvarchar](450) NULL,
	[index] [int] NULL,
	[lastEdited] [datetime2] NULL,
	[notes] [nvarchar](4000) NULL,
 CONSTRAINT [PK __instrume__ 430A5366C687BEB1] PRIMARY KEY CLUSTERED 
(
	[InstrumentID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[languages](
	[lang] [nvarchar](450) NOT NULL,
	[langFam] [nvarchar](450) NULL,
	[langName] [nvarchar](450) NULL,
	[natName] [nvarchar](450) NULL,
	[ISO6391] [nvarchar](450) NULL,
	[ISO6392B] [nvarchar](450) NULL,
	[ISO6392T] [nvarchar](450) NULL,
	[ISO6393] [nvarchar](450) NULL,
	[ISO6396] [nvarchar](450) NULL,
	[firstRelased] [datetime2] NULL,
	[lastUpdated] [datetime2] NULL,
	[changes] [nvarchar](450) NULL,
	[notes] [nvarchar](4000) NULL,
 CONSTRAINT [PK __language__ A448E7831DC30204] PRIMARY KEY CLUSTERED 
(
	[lang] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[measures](
	[measureRepID] [nvarchar](450) NOT NULL,
	[protocolID] [nvarchar](450) NULL,
	[sampleID] [nvarchar](450) NULL,
	[purposeID] [nvarchar](450) NULL,
	[polygonID] [nvarchar](450) NULL,
	[siteID] [nvarchar](450) NULL,
	[datasetID] [nvarchar](450) NULL,
	[measureSetRepID] [nvarchar](450) NULL,
	[aDateStart] [datetime2] NULL,
	[aDateEnd] [datetime2] NULL,
	[reportDate] [datetime2] NULL,
	[compartment] [nvarchar](450) NULL,
	[specimenID] [nvarchar](450) NULL,
	[fraction] [nvarchar](450) NULL,
	[group] [nvarchar](450) NULL,
	[class] [nvarchar](450) NULL,
	[measure] [nvarchar](450) NULL,
	[value] [nvarchar](450) NULL,
	[unit] [nvarchar](450) NULL,
	[aggregation] [nvarchar](450) NULL,
	[nomenclature] [nvarchar](450) NULL,
	[index] [int] NULL,
	[measureLic] [nvarchar](450) NULL,
	[reportable] [int] NULL,
	[organizationID] [nvarchar](450) NULL,
	[contactID] [nvarchar](450) NULL,
	[refLink] [nvarchar](4000) NULL,
	[lastEdited] [datetime2] NULL,
 CONSTRAINT [PK __measures__ EEB5CFA1A2FB28AE] PRIMARY KEY CLUSTERED 
(
	[measureRepID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[measureSets](
	[measureSetRepID] [nvarchar](450) NOT NULL,
	[protocolID] [nvarchar](450) NULL,
	[name] [nvarchar](450) NULL,
	[organizationID] [nvarchar](450) NULL,
	[contactID] [nvarchar](450) NULL,
	[lastEdited] [datetime2] NULL,
	[notes] [nvarchar](4000) NULL,
 CONSTRAINT [PK __measureS__ 6BF4F5F3ED304632] PRIMARY KEY CLUSTERED 
(
	[measureSetRepID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[organizations](
	[organizationID] [nvarchar](450) NOT NULL,
	[name] [nvarchar](450) NULL,
	[descr] [nvarchar](450) NULL,
	[addressID] [nvarchar](450) NULL,
	[datasetID] [nvarchar](450) NULL,
	[orgType] [nvarchar](450) NULL,
	[orgLevel] [nvarchar](450) NULL,
	[orgSector] [nvarchar](450) NULL,
	[lastEdited] [datetime2] NULL,
	[notes] [nvarchar](4000) NULL,
 CONSTRAINT [PK __organiza__ 29747D79B216482A] PRIMARY KEY CLUSTERED 
(
	[organizationID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[parts](
	[partID] [nvarchar](450) NOT NULL,
	[partLabel] [nvarchar](450) NULL,
	[partType] [nvarchar](450) NULL,
	[shortName] [nvarchar](450) NULL,
	[partDesc] [nvarchar](4000) NULL,
	[partInstr] [nvarchar](450) NULL,
	[domain] [nvarchar](450) NULL,
	[specimenSet] [nvarchar](450) NULL,
	[compartmentSet] [nvarchar](450) NULL,
	[group] [nvarchar](450) NULL,
	[class] [nvarchar](450) NULL,
	[nomenclature] [nvarchar](450) NULL,
	[ontologyRef] [nvarchar](450) NULL,
	[latExp] [nvarchar](450) NULL,
	[mmaSet] [nvarchar](450) NULL,
	[unitSet] [nvarchar](450) NULL,
	[aggreationScale] [nvarchar](450) NULL,
	[aggregationSet] [nvarchar](450) NULL,
	[qualitySet] [nvarchar](450) NULL,
	[missingnessSet] [nvarchar](450) NULL,
	[status] [nvarchar](450) NULL,
	[firstReleased] [datetime2] NULL,
	[lastUpdated] [datetime2] NULL,
	[changes] [nvarchar](450) NULL,
	[protocolSteps] [nvarchar](450) NULL,
	[protocolStepsRequired] [nvarchar](450) NULL,
	[protocolStepsOrder] [int] NULL,
	[protocolRelationships] [nvarchar](450) NULL,
	[protocolRelationshipsRequired] [nvarchar](450) NULL,
	[protocolRelationshipsOrder] [int] NULL,
	[measures] [nvarchar](450) NULL,
	[measuresRequired] [nvarchar](450) NULL,
	[measuresOrder] [int] NULL,
	[measureSets] [nvarchar](450) NULL,
	[measureSetsRequired] [nvarchar](450) NULL,
	[measureSetsOrder] [int] NULL,
	[datasets] [nvarchar](450) NULL,
	[datasetsRequired] [nvarchar](450) NULL,
	[datasetsOrder] [int] NULL,
	[sites] [nvarchar](450) NULL,
	[sitesRequired] [nvarchar](450) NULL,
	[sitesOrder] [int] NULL,
	[samples] [nvarchar](450) NULL,
	[samplesRequired] [nvarchar](450) NULL,
	[samplesOrder] [int] NULL,
	[addresses] [nvarchar](450) NULL,
	[addressesRequired] [nvarchar](450) NULL,
	[addressesOrder] [int] NULL,
	[contacts] [nvarchar](450) NULL,
	[contactsRequired] [nvarchar](450) NULL,
	[contactsOrder] [int] NULL,
	[organizations] [nvarchar](450) NULL,
	[organizationsRequired] [nvarchar](450) NULL,
	[organizationsOrder] [int] NULL,
	[instruments] [nvarchar](450) NULL,
	[instrumentsRequired] [nvarchar](450) NULL,
	[instrumentsOrder] [int] NULL,
	[polygons] [nvarchar](450) NULL,
	[polygonsRequired] [nvarchar](450) NULL,
	[polygonsOrder] [int] NULL,
	[languages] [nvarchar](450) NULL,
	[languagesRequired] [nvarchar](450) NULL,
	[languagesOrder] [int] NULL,
	[translations] [nvarchar](450) NULL,
	[translationsRequired] [nvarchar](450) NULL,
	[translationsOrder] [int] NULL,
	[parts] [nvarchar](450) NULL,
	[partsRequired] [nvarchar](450) NULL,
	[partsOrder] [int] NULL,
	[sets] [nvarchar](450) NULL,
	[setsRquired] [nvarchar](450) NULL,
	[setsOrder] [int] NULL,
	[qualityReports] [nvarchar](450) NULL,
	[qualityReportsRequired] [nvarchar](450) NULL,
	[qualityReportsOrder] [int] NULL,
	[sampleRelationships] [nvarchar](450) NULL,
	[sampleRelationshipsRequired] [nvarchar](450) NULL,
	[sampleRelationshipsOrder] [int] NULL,
	[protocols] [nvarchar](450) NULL,
	[protocolsRequired] [nvarchar](450) NULL,
	[protocolsOrder] [int] NULL,
	[countries] [nvarchar](450) NULL,
	[countriesRequired] [nvarchar](450) NULL,
	[countriesOrder] [int] NULL,
	[zones] [nvarchar](450) NULL,
	[zonesRequired] [nvarchar](450) NULL,
	[zonesOrder] [int] NULL,
	[refLink] [nvarchar](4000) NULL,
	[dataType] [nvarchar](450) NULL,
	[minValue] [int] NULL,
	[maxValue] [int] NULL,
	[minLength] [int] NULL,
	[maxLength] [int] NULL,
 CONSTRAINT [PK __parts__ 7FDB1448E687C5F1] PRIMARY KEY CLUSTERED 
(
	[partID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[polygons](
	[polygonID] [nvarchar](450) NOT NULL,
	[datasetID] [nvarchar](450) NULL,
	[name] [nvarchar](450) NULL,
	[descr] [nvarchar](4000) NULL,
	[polyPop] [int] NULL,
	[geoType] [nvarchar](450) NULL,
	[geoEPSG] [nvarchar](450) NULL,
	[geoWKT] [nvarchar](MAX) NULL,
	[fileLocation] [nvarchar](450) NULL,
	[refLink] [nvarchar](4000) NULL,
	[organizationID] [nvarchar](450) NULL,
	[contactID] [nvarchar](450) NULL,
	[lastEdited] [datetime2] NULL,
	[notes] [nvarchar](4000) NULL,
 CONSTRAINT [PK __polygons__ 28246249F5ECDE1B] PRIMARY KEY CLUSTERED 
(
	[polygonID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[protocolRelationships](
	[protocolIDContainer] [nvarchar](450) NULL,
	[protocolIDObj] [nvarchar](450) NULL,
	[stepIDObj] [nvarchar](450) NULL,
	[relationshipID] [nvarchar](450) NULL,
	[protocolIDSub] [nvarchar](450) NULL,
	[stepIDSub] [nvarchar](450) NULL,
	[lastEdited] [datetime2] NULL,
	[notes] [nvarchar](4000) NULL
) ON [PRIMARY]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[protocols](
	[sourceProtocol] [nvarchar](450) NULL,
	[protocolID] [nvarchar](450) NOT NULL,
	[datasetID] [nvarchar](450) NULL,
	[name] [nvarchar](450) NULL,
	[summ] [nvarchar](450) NULL,
	[refLink] [nvarchar](4000) NULL,
	[organizationID] [nvarchar](450) NULL,
	[contactID] [nvarchar](450) NULL,
	[protocolVersion] [nvarchar](450) NULL,
	[lastEdited] [datetime2] NULL,
	[notes] [nvarchar](4000) NULL,
 CONSTRAINT [PK __protocol__ 48F3B4094A743A35] PRIMARY KEY CLUSTERED 
(
	[protocolID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[protocolSteps](
	[stepID] [nvarchar](450) NOT NULL,
	[method] [nvarchar](450) NULL,
	[measure] [nvarchar](450) NULL,
	[summ] [nvarchar](450) NULL,
	[sourceStep] [nvarchar](450) NULL,
	[stepVer] [nvarchar](450) NULL,
	[refLink] [nvarchar](4000) NULL,
	[organizationID] [nvarchar](450) NULL,
	[contactID] [nvarchar](450) NULL,
	[InstrumentID] [nvarchar](450) NULL,
	[value] [nvarchar](450) NULL,
	[unit] [nvarchar](450) NULL,
	[aggregation] [nvarchar](450) NULL,
	[lastEdited] [datetime2] NULL,
	[notes] [nvarchar](4000) NULL,
 CONSTRAINT [PK __protocol__ 4E25C23D392861EE] PRIMARY KEY CLUSTERED 
(
	[stepID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[qualityReports](
	[quality] [nvarchar](450) NOT NULL,
	[measureRepID] [nvarchar](450) NULL,
	[sampleID] [nvarchar](450) NULL,
	[measureSetRepID] [nvarchar](450) NULL,
	[qualityFlag] [nvarchar](450) NULL,
	[severity] [nvarchar](450) NULL,
	[lastEdited] [datetime2] NULL,
	[notes] [nvarchar](4000) NULL,
 CONSTRAINT [PK __qualityR__ B9B86FE6DA3E7440] PRIMARY KEY CLUSTERED 
(
	[quality] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[sampleRelationships](
	[sampleIDSubject] [nvarchar](450) NULL,
	[sampleRelID] [nvarchar](450) NOT NULL,
	[sampleIDObject] [nvarchar](450) NULL,
	[lastEdited] [datetime2] NULL,
	[notes] [nvarchar](4000) NULL,
 CONSTRAINT [PK __sampleRe__ 67C1F34DBC078CEA] PRIMARY KEY CLUSTERED 
(
	[sampleRelID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[samples](
	[sampleID] [nvarchar](450) NOT NULL,
	[protocolID] [nvarchar](450) NULL,
	[organizationID] [nvarchar](450) NULL,
	[contactID] [nvarchar](450) NULL,
	[siteID] [nvarchar](450) NULL,
	[purposeID] [nvarchar](450) NULL,
	[saMaterial] [nvarchar](450) NULL,
	[datasetID] [nvarchar](450) NULL,
	[origin] [nvarchar](450) NULL,
	[repType] [nvarchar](450) NULL,
	[collType] [nvarchar](450) NULL,
	[collPer] [float] NULL,
	[collNum] [int] NULL,
	[pooled] [int] NULL,
	[collDT] [datetime2] NULL,
	[collDTStart] [datetime2] NULL,
	[collDTEnd] [datetime2] NULL,
	[sentDate] [datetime2] NULL,
	[recDate] [datetime2] NULL,
	[reportable] [nvarchar](450) NULL,
	[lastEdited] [datetime2] NULL,
	[notes] [nvarchar](4000) NULL,
 CONSTRAINT [PK __samples__ 3FD4F24B6428A88E] PRIMARY KEY CLUSTERED 
(
	[sampleID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[sets](
	[setID] [nvarchar](450) NOT NULL,
	[setType] [nvarchar](450) NULL,
	[partID] [nvarchar](450) NULL,
	[partLabel] [nvarchar](450) NULL,
	[status] [nvarchar](450) NULL,
	[firstReleased] [datetime2] NULL,
	[lastUpdated] [datetime2] NULL,
	[changes] [nvarchar](450) NULL,
	[notes] [nvarchar](4000) NULL,
 CONSTRAINT [PK __sets__ DA8A697A917E076A] PRIMARY KEY CLUSTERED 
(
	[setID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[sites](
	[parSiteID] [nvarchar](450) NULL,
	[siteID] [nvarchar](450) NOT NULL,
	[datasetID] [nvarchar](450) NULL,
	[polygonID] [nvarchar](450) NULL,
	[siteType] [nvarchar](450) NULL,
	[sampleShed] [nvarchar](450) NULL,
	[addressID] [nvarchar](450) NULL,
	[organizationID] [nvarchar](450) NULL,
	[contactID] [nvarchar](450) NULL,
	[name] [nvarchar](450) NULL,
	[descr] [nvarchar](450) NULL,
	[repOrg1] [nvarchar](450) NULL,
	[repOrg2] [nvarchar](450) NULL,
	[healthReg] [nvarchar](450) NULL,
	[popServ] [int] NULL,
	[geoLat] [nvarchar](450) NULL,
	[geoLong] [nvarchar](450) NULL,
	[geoEPSG] [nvarchar](450) NULL,
	[lastEdited] [datetime2] NULL,
	[notes] [nvarchar](4000) NULL,
 CONSTRAINT [PK __sites__ EAF19B59301A5C75] PRIMARY KEY CLUSTERED 
(
	[siteID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[translations](
	[lang] [nvarchar](450) NOT NULL,
	[part] [nvarchar](450) NULL,
	[partLabel] [nvarchar](450) NULL,
	[partDesc] [nvarchar](450) NULL,
	[partInstr] [nvarchar](450) NULL,
	[firstReleased] [datetime2] NULL,
	[lastUpdated] [datetime2] NULL,
	[changes] [nvarchar](450) NULL,
	[notes] [nvarchar](4000) NULL,
 CONSTRAINT [PK __translat__ A448E783E94932F1] PRIMARY KEY CLUSTERED 
(
	[lang] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[wideNames](
	[wideName] [nvarchar](450) NOT NULL,
	[label] [nvarchar](450) NULL,
	[charLength] [int] NULL,
	[descr] [nvarchar](4000) NULL,
	[source] [nvarchar](450) NULL,
	[wideMeasure] [nvarchar](450) NULL,
	[wideProtocol] [nvarchar](450) NULL,
	[wideAttribute] [nvarchar](450) NULL,
	[wideNameType] [nvarchar](450) NULL,
	[reportTableName] [nvarchar](450) NULL,
	[reportTableInput] [nvarchar](450) NULL,
	[partTypeName] [nvarchar](450) NULL,
	[partTypeInput] [nvarchar](450) NULL,
	[compartmentName] [nvarchar](450) NULL,
	[compartmentInput] [nvarchar](450) NULL,
	[specimenName] [nvarchar](450) NULL,
	[specimenInput] [nvarchar](450) NULL,
	[fractionName] [nvarchar](450) NULL,
	[fractionInput] [nvarchar](450) NULL,
	[measureName] [nvarchar](450) NULL,
	[measureInput] [nvarchar](450) NULL,
	[methodName] [nvarchar](450) NULL,
	[methodInput] [nvarchar](450) NULL,
	[unitName] [nvarchar](450) NULL,
	[unitInput] [nvarchar](450) NULL,
	[aggregationName] [nvarchar](450) NULL,
	[aggregationInput] [nvarchar](450) NULL,
	[index] [int] NULL,
	[attributeName] [nvarchar](450) NULL,
	[attributeInput] [nvarchar](450) NULL,
 CONSTRAINT [PK __wideName__ D8C8E8B9053BAAC2] PRIMARY KEY CLUSTERED 
(
	[wideName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[zones](
	[isoCode] [nvarchar](450) NULL,
	[isoZone] [nvarchar](450) NOT NULL,
	[zoneName] [nvarchar](450) NULL,
 CONSTRAINT [PK __zones__ 4D598FEE4AF22C86] PRIMARY KEY CLUSTERED 
(
	[isoZone] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

```

---

<div class="post-metadata">

**Author:** ![Sorin](https://yyz2.discourse-cdn.com/free1/user_avatar/odm.discourse.group/sorin/32/52_2.png) [@Sorin](https://odm.discourse.group/u/Sorin)\
**Post date:** [September 2, 2023, 12:13pm UTC](https://odm.discourse.group/t/cycles-in-the-code-generated-by-lucid/135/3 "2023-09-02T12:13:16Z")

</div>

…continued:

```auto
ALTER TABLE [dbo].[addresses] WITH CHECK ADD CONSTRAINT [FK_addresses.country] FOREIGN KEY([country])
REFERENCES [dbo].[countries] ([isoCode])
GO
ALTER TABLE [dbo].[addresses] CHECK CONSTRAINT [FK_addresses.country]
GO
ALTER TABLE [dbo].[addresses] WITH CHECK ADD CONSTRAINT [FK_addresses.datasetID] FOREIGN KEY([datasetID])
REFERENCES [dbo].[datasets] ([datasetID])
GO
ALTER TABLE [dbo].[addresses] CHECK CONSTRAINT [FK_addresses.datasetID]
GO
ALTER TABLE [dbo].[addresses] WITH CHECK ADD CONSTRAINT [FK_addresses.stateProvReg] FOREIGN KEY([stateProvReg])
REFERENCES [dbo].[zones] ([isoZone])
GO
ALTER TABLE [dbo].[addresses] CHECK CONSTRAINT [FK_addresses.stateProvReg]
GO
ALTER TABLE [dbo].[contacts] WITH CHECK ADD CONSTRAINT [FK_contacts.datasetID] FOREIGN KEY([datasetID])
REFERENCES [dbo].[datasets] ([datasetID])
GO
ALTER TABLE [dbo].[contacts] CHECK CONSTRAINT [FK_contacts.datasetID]
GO
ALTER TABLE [dbo].[contacts] WITH CHECK ADD CONSTRAINT [FK_contacts.organizationID] FOREIGN KEY([organizationID])
REFERENCES [dbo].[organizations] ([organizationID])
GO
ALTER TABLE [dbo].[contacts] CHECK CONSTRAINT [FK_contacts.organizationID]
GO
ALTER TABLE [dbo].[datasets] WITH CHECK ADD CONSTRAINT [FK_datasets.parDatasetID] FOREIGN KEY([datasetID])
REFERENCES [dbo].[datasets] ([datasetID])
GO
ALTER TABLE [dbo].[datasets] CHECK CONSTRAINT [FK_datasets.parDatasetID]
GO
ALTER TABLE [dbo].[instruments] WITH CHECK ADD CONSTRAINT [FK_instruments.contactID] FOREIGN KEY([contactID])
REFERENCES [dbo].[contacts] ([contactID])
GO
ALTER TABLE [dbo].[instruments] CHECK CONSTRAINT [FK_instruments.contactID]
GO
ALTER TABLE [dbo].[instruments] WITH CHECK ADD CONSTRAINT [FK_instruments.datasetID] FOREIGN KEY([datasetID])
REFERENCES [dbo].[datasets] ([datasetID])
GO
ALTER TABLE [dbo].[instruments] CHECK CONSTRAINT [FK_instruments.datasetID]
GO
ALTER TABLE [dbo].[instruments] WITH CHECK ADD CONSTRAINT [FK_instruments.instType] FOREIGN KEY([instType])
REFERENCES [dbo].[parts] ([partID])
GO
ALTER TABLE [dbo].[instruments] CHECK CONSTRAINT [FK_instruments.instType]
GO
ALTER TABLE [dbo].[instruments] WITH CHECK ADD CONSTRAINT [FK_instruments.organizationID] FOREIGN KEY([organizationID])
REFERENCES [dbo].[organizations] ([organizationID])
GO
ALTER TABLE [dbo].[instruments] CHECK CONSTRAINT [FK_instruments.organizationID]
GO
ALTER TABLE [dbo].[measures] WITH CHECK ADD CONSTRAINT [FK_measures.aggregation] FOREIGN KEY([aggregation])
REFERENCES [dbo].[parts] ([partID])
GO
ALTER TABLE [dbo].[measures] CHECK CONSTRAINT [FK_measures.aggregation]
GO
ALTER TABLE [dbo].[measures] WITH CHECK ADD CONSTRAINT [FK_measures.class] FOREIGN KEY([class])
REFERENCES [dbo].[parts] ([partID])
GO
ALTER TABLE [dbo].[measures] CHECK CONSTRAINT [FK_measures.class]
GO
ALTER TABLE [dbo].[measures] WITH CHECK ADD CONSTRAINT [FK_measures.contactID] FOREIGN KEY([contactID])
REFERENCES [dbo].[contacts] ([contactID])
GO
ALTER TABLE [dbo].[measures] CHECK CONSTRAINT [FK_measures.contactID]
GO
ALTER TABLE [dbo].[measures] WITH CHECK ADD CONSTRAINT [FK_measures.datasetID] FOREIGN KEY([datasetID])
REFERENCES [dbo].[datasets] ([datasetID])
GO
ALTER TABLE [dbo].[measures] CHECK CONSTRAINT [FK_measures.datasetID]
GO
ALTER TABLE [dbo].[measures] WITH CHECK ADD CONSTRAINT [FK_measures.fraction] FOREIGN KEY([fraction])
REFERENCES [dbo].[parts] ([partID])
GO
ALTER TABLE [dbo].[measures] CHECK CONSTRAINT [FK_measures.fraction]
GO
ALTER TABLE [dbo].[measures] WITH CHECK ADD CONSTRAINT [FK_measures.group] FOREIGN KEY([group])
REFERENCES [dbo].[parts] ([partID])
GO
ALTER TABLE [dbo].[measures] CHECK CONSTRAINT [FK_measures.group]
GO
ALTER TABLE [dbo].[measures] WITH CHECK ADD CONSTRAINT [FK_measures.measure] FOREIGN KEY([measure])
REFERENCES [dbo].[parts] ([partID])
GO
ALTER TABLE [dbo].[measures] CHECK CONSTRAINT [FK_measures.measure]
GO
ALTER TABLE [dbo].[measures] WITH CHECK ADD CONSTRAINT [FK_measures.measureLic] FOREIGN KEY([measureLic])
REFERENCES [dbo].[parts] ([partID])
GO
ALTER TABLE [dbo].[measures] CHECK CONSTRAINT [FK_measures.measureLic]
GO
ALTER TABLE [dbo].[measures] WITH CHECK ADD CONSTRAINT [FK_measures.measureSetRepID] FOREIGN KEY([measureSetRepID])
REFERENCES [dbo].[measureSets] ([measureSetRepID])
GO
ALTER TABLE [dbo].[measures] CHECK CONSTRAINT [FK_measures.measureSetRepID]
GO
ALTER TABLE [dbo].[measures] WITH CHECK ADD CONSTRAINT [FK_measures.nomenclature] FOREIGN KEY([nomenclature])
REFERENCES [dbo].[parts] ([partID])
GO
ALTER TABLE [dbo].[measures] CHECK CONSTRAINT [FK_measures.nomenclature]
GO
ALTER TABLE [dbo].[measures] WITH CHECK ADD CONSTRAINT [FK_measures.organizationID] FOREIGN KEY([organizationID])
REFERENCES [dbo].[organizations] ([organizationID])
GO
ALTER TABLE [dbo].[measures] CHECK CONSTRAINT [FK_measures.organizationID]
GO
ALTER TABLE [dbo].[measures] WITH CHECK ADD CONSTRAINT [FK_measures.polygonID] FOREIGN KEY([polygonID])
REFERENCES [dbo].[polygons] ([polygonID])
GO
ALTER TABLE [dbo].[measures] CHECK CONSTRAINT [FK_measures.polygonID]
GO
ALTER TABLE [dbo].[measures] WITH CHECK ADD CONSTRAINT [FK_measures.protocolID] FOREIGN KEY([protocolID])
REFERENCES [dbo].[protocols] ([protocolID])
GO
ALTER TABLE [dbo].[measures] CHECK CONSTRAINT [FK_measures.protocolID]
GO
ALTER TABLE [dbo].[measures] WITH CHECK ADD CONSTRAINT [FK_measures.purposeID] FOREIGN KEY([purposeID])
REFERENCES [dbo].[parts] ([partID])
GO
ALTER TABLE [dbo].[measures] CHECK CONSTRAINT [FK_measures.purposeID]
GO
ALTER TABLE [dbo].[measures] WITH CHECK ADD CONSTRAINT [FK_measures.sampleID] FOREIGN KEY([sampleID])
REFERENCES [dbo].[samples] ([sampleID])
GO
ALTER TABLE [dbo].[measures] CHECK CONSTRAINT [FK_measures.sampleID]
GO
ALTER TABLE [dbo].[measures] WITH CHECK ADD CONSTRAINT [FK_measures.siteID] FOREIGN KEY([siteID])
REFERENCES [dbo].[sites] ([siteID])
GO
ALTER TABLE [dbo].[measures] CHECK CONSTRAINT [FK_measures.siteID]
GO
ALTER TABLE [dbo].[measures] WITH CHECK ADD CONSTRAINT [FK_measures.specimenID] FOREIGN KEY([specimenID])
REFERENCES [dbo].[parts] ([partID])
GO
ALTER TABLE [dbo].[measures] CHECK CONSTRAINT [FK_measures.specimenID]
GO
ALTER TABLE [dbo].[measures] WITH CHECK ADD CONSTRAINT [FK_measures.unit] FOREIGN KEY([unit])
REFERENCES [dbo].[parts] ([partID])
GO
ALTER TABLE [dbo].[measures] CHECK CONSTRAINT [FK_measures.unit]
GO
ALTER TABLE [dbo].[measureSets] WITH CHECK ADD CONSTRAINT [FK_measureSets.contactID] FOREIGN KEY([contactID])
REFERENCES [dbo].[contacts] ([contactID])
GO
ALTER TABLE [dbo].[measureSets] CHECK CONSTRAINT [FK_measureSets.contactID]
GO
ALTER TABLE [dbo].[measureSets] WITH CHECK ADD CONSTRAINT [FK_measureSets.organizationID] FOREIGN KEY([organizationID])
REFERENCES [dbo].[organizations] ([organizationID])
GO
ALTER TABLE [dbo].[measureSets] CHECK CONSTRAINT [FK_measureSets.organizationID]
GO
ALTER TABLE [dbo].[measureSets] WITH CHECK ADD CONSTRAINT [FK_measureSets.protocolID] FOREIGN KEY([protocolID])
REFERENCES [dbo].[protocols] ([protocolID])
GO
ALTER TABLE [dbo].[measureSets] CHECK CONSTRAINT [FK_measureSets.protocolID]
GO
ALTER TABLE [dbo].[organizations] WITH CHECK ADD CONSTRAINT [FK_organizations.addressID] FOREIGN KEY([addressID])
REFERENCES [dbo].[addresses] ([addressID])
GO
ALTER TABLE [dbo].[organizations] CHECK CONSTRAINT [FK_organizations.addressID]
GO
ALTER TABLE [dbo].[organizations] WITH CHECK ADD CONSTRAINT [FK_organizations.datasetID] FOREIGN KEY([datasetID])
REFERENCES [dbo].[datasets] ([datasetID])
GO
ALTER TABLE [dbo].[organizations] CHECK CONSTRAINT [FK_organizations.datasetID]
GO
ALTER TABLE [dbo].[parts] WITH CHECK ADD CONSTRAINT [FK_parts.aggregationSet] FOREIGN KEY([aggregationSet])
REFERENCES [dbo].[sets] ([setID])
GO
ALTER TABLE [dbo].[parts] CHECK CONSTRAINT [FK_parts.aggregationSet]
GO
ALTER TABLE [dbo].[parts] WITH CHECK ADD CONSTRAINT [FK_parts.compartmentSet] FOREIGN KEY([compartmentSet])
REFERENCES [dbo].[sets] ([setID])
GO
ALTER TABLE [dbo].[parts] CHECK CONSTRAINT [FK_parts.compartmentSet]
GO
ALTER TABLE [dbo].[parts] WITH CHECK ADD CONSTRAINT [FK_parts.missingnessSet] FOREIGN KEY([missingnessSet])
REFERENCES [dbo].[sets] ([setID])
GO
ALTER TABLE [dbo].[parts] CHECK CONSTRAINT [FK_parts.missingnessSet]
GO
ALTER TABLE [dbo].[parts] WITH CHECK ADD CONSTRAINT [FK_parts.qualitySet] FOREIGN KEY([qualitySet])
REFERENCES [dbo].[sets] ([setID])
GO
ALTER TABLE [dbo].[parts] CHECK CONSTRAINT [FK_parts.qualitySet]
GO
ALTER TABLE [dbo].[parts] WITH CHECK ADD CONSTRAINT [FK_parts.specimenSet] FOREIGN KEY([specimenSet])
REFERENCES [dbo].[sets] ([setID])
GO
ALTER TABLE [dbo].[parts] CHECK CONSTRAINT [FK_parts.specimenSet]
GO
ALTER TABLE [dbo].[parts] WITH CHECK ADD CONSTRAINT [FK_parts.unitSet] FOREIGN KEY([unitSet])
REFERENCES [dbo].[sets] ([setID])
GO
ALTER TABLE [dbo].[parts] CHECK CONSTRAINT [FK_parts.unitSet]
GO
ALTER TABLE [dbo].[polygons] WITH CHECK ADD CONSTRAINT [FK_polygons.contactID] FOREIGN KEY([contactID])
REFERENCES [dbo].[contacts] ([contactID])
GO
ALTER TABLE [dbo].[polygons] CHECK CONSTRAINT [FK_polygons.contactID]
GO
ALTER TABLE [dbo].[polygons] WITH CHECK ADD CONSTRAINT [FK_polygons.datasetID] FOREIGN KEY([datasetID])
REFERENCES [dbo].[datasets] ([datasetID])
GO
ALTER TABLE [dbo].[polygons] CHECK CONSTRAINT [FK_polygons.datasetID]
GO
ALTER TABLE [dbo].[polygons] WITH CHECK ADD CONSTRAINT [FK_polygons.organizationID] FOREIGN KEY([organizationID])
REFERENCES [dbo].[organizations] ([organizationID])
GO
ALTER TABLE [dbo].[polygons] CHECK CONSTRAINT [FK_polygons.organizationID]
GO
ALTER TABLE [dbo].[protocolRelationships] WITH CHECK ADD CONSTRAINT [FK_protocolRelationships.protocolIDContainer] FOREIGN KEY([protocolIDContainer])
REFERENCES [dbo].[protocols] ([protocolID])
GO
ALTER TABLE [dbo].[protocolRelationships] CHECK CONSTRAINT [FK_protocolRelationships.protocolIDContainer]
GO
ALTER TABLE [dbo].[protocolRelationships] WITH CHECK ADD CONSTRAINT [FK_protocolRelationships.protocolIDObj] FOREIGN KEY([protocolIDObj])
REFERENCES [dbo].[protocols] ([protocolID])
GO
ALTER TABLE [dbo].[protocolRelationships] CHECK CONSTRAINT [FK_protocolRelationships.protocolIDObj]
GO
ALTER TABLE [dbo].[protocolRelationships] WITH CHECK ADD CONSTRAINT [FK_protocolRelationships.protocolIDSub] FOREIGN KEY([protocolIDSub])
REFERENCES [dbo].[protocols] ([protocolID])
GO
ALTER TABLE [dbo].[protocolRelationships] CHECK CONSTRAINT [FK_protocolRelationships.protocolIDSub]
GO
ALTER TABLE [dbo].[protocolRelationships] WITH CHECK ADD CONSTRAINT [FK_protocolRelationships.stepIDObj] FOREIGN KEY([stepIDObj])
REFERENCES [dbo].[protocolSteps] ([stepID])
GO
ALTER TABLE [dbo].[protocolRelationships] CHECK CONSTRAINT [FK_protocolRelationships.stepIDObj]
GO
ALTER TABLE [dbo].[protocolRelationships] WITH CHECK ADD CONSTRAINT [FK_protocolRelationships.stepIDSub] FOREIGN KEY([stepIDSub])
REFERENCES [dbo].[protocolSteps] ([stepID])
GO
ALTER TABLE [dbo].[protocolRelationships] CHECK CONSTRAINT [FK_protocolRelationships.stepIDSub]
GO
ALTER TABLE [dbo].[protocols] WITH CHECK ADD CONSTRAINT [FK_protocols.contactID] FOREIGN KEY([contactID])
REFERENCES [dbo].[contacts] ([contactID])
GO
ALTER TABLE [dbo].[protocols] CHECK CONSTRAINT [FK_protocols.contactID]
GO
ALTER TABLE [dbo].[protocols] WITH CHECK ADD CONSTRAINT [FK_protocols.datasetID] FOREIGN KEY([datasetID])
REFERENCES [dbo].[datasets] ([datasetID])
GO
ALTER TABLE [dbo].[protocols] CHECK CONSTRAINT [FK_protocols.datasetID]
GO
ALTER TABLE [dbo].[protocols] WITH CHECK ADD CONSTRAINT [FK_protocols.organizationID] FOREIGN KEY([organizationID])
REFERENCES [dbo].[organizations] ([organizationID])
GO
ALTER TABLE [dbo].[protocols] CHECK CONSTRAINT [FK_protocols.organizationID]
GO
ALTER TABLE [dbo].[protocols] WITH CHECK ADD CONSTRAINT [FK_protocols.sourceProtocol] FOREIGN KEY([protocolID])
REFERENCES [dbo].[protocols] ([protocolID])
GO
ALTER TABLE [dbo].[protocols] CHECK CONSTRAINT [FK_protocols.sourceProtocol]
GO
ALTER TABLE [dbo].[protocolSteps] WITH CHECK ADD CONSTRAINT [FK_protocolSteps.aggregation] FOREIGN KEY([aggregation])
REFERENCES [dbo].[parts] ([partID])
GO
ALTER TABLE [dbo].[protocolSteps] CHECK CONSTRAINT [FK_protocolSteps.aggregation]
GO
ALTER TABLE [dbo].[protocolSteps] WITH CHECK ADD CONSTRAINT [FK_protocolSteps.contactID] FOREIGN KEY([contactID])
REFERENCES [dbo].[contacts] ([contactID])
GO
ALTER TABLE [dbo].[protocolSteps] CHECK CONSTRAINT [FK_protocolSteps.contactID]
GO
ALTER TABLE [dbo].[protocolSteps] WITH CHECK ADD CONSTRAINT [FK_protocolSteps.InstrumentID] FOREIGN KEY([InstrumentID])
REFERENCES [dbo].[instruments] ([InstrumentID])
GO
ALTER TABLE [dbo].[protocolSteps] CHECK CONSTRAINT [FK_protocolSteps.InstrumentID]
GO
ALTER TABLE [dbo].[protocolSteps] WITH CHECK ADD CONSTRAINT [FK_protocolSteps.organizationID] FOREIGN KEY([organizationID])
REFERENCES [dbo].[organizations] ([organizationID])
GO
ALTER TABLE [dbo].[protocolSteps] CHECK CONSTRAINT [FK_protocolSteps.organizationID]
GO
ALTER TABLE [dbo].[protocolSteps] WITH CHECK ADD CONSTRAINT [FK_protocolSteps.sourceStep] FOREIGN KEY([sourceStep])
REFERENCES [dbo].[protocolSteps] ([stepID])
GO
ALTER TABLE [dbo].[protocolSteps] CHECK CONSTRAINT [FK_protocolSteps.sourceStep]
GO
ALTER TABLE [dbo].[protocolSteps] WITH CHECK ADD CONSTRAINT [FK_protocolSteps.unit] FOREIGN KEY([unit])
REFERENCES [dbo].[parts] ([partID])
GO
ALTER TABLE [dbo].[protocolSteps] CHECK CONSTRAINT [FK_protocolSteps.unit]
GO
ALTER TABLE [dbo].[qualityReports] WITH CHECK ADD CONSTRAINT [FK_qualityReports.measureRepID] FOREIGN KEY([measureRepID])
REFERENCES [dbo].[measures] ([measureRepID])
GO
ALTER TABLE [dbo].[qualityReports] CHECK CONSTRAINT [FK_qualityReports.measureRepID]
GO
ALTER TABLE [dbo].[qualityReports] WITH CHECK ADD CONSTRAINT [FK_qualityReports.measureSetRepID] FOREIGN KEY([measureSetRepID])
REFERENCES [dbo].[measureSets] ([measureSetRepID])
GO
ALTER TABLE [dbo].[qualityReports] CHECK CONSTRAINT [FK_qualityReports.measureSetRepID]
GO
ALTER TABLE [dbo].[qualityReports] WITH CHECK ADD CONSTRAINT [FK_qualityReports.quality] FOREIGN KEY([quality])
REFERENCES [dbo].[parts] ([partID])
GO
ALTER TABLE [dbo].[qualityReports] CHECK CONSTRAINT [FK_qualityReports.quality]
GO
ALTER TABLE [dbo].[qualityReports] WITH CHECK ADD CONSTRAINT [FK_qualityReports.sampleID] FOREIGN KEY([sampleID])
REFERENCES [dbo].[samples] ([sampleID])
GO
ALTER TABLE [dbo].[qualityReports] CHECK CONSTRAINT [FK_qualityReports.sampleID]
GO
ALTER TABLE [dbo].[sampleRelationships] WITH CHECK ADD CONSTRAINT [FK_sampleRelationships.sampleIDObject] FOREIGN KEY([sampleIDObject])
REFERENCES [dbo].[samples] ([sampleID])
GO
ALTER TABLE [dbo].[sampleRelationships] CHECK CONSTRAINT [FK_sampleRelationships.sampleIDObject]
GO
ALTER TABLE [dbo].[sampleRelationships] WITH CHECK ADD CONSTRAINT [FK_sampleRelationships.sampleIDSubject] FOREIGN KEY([sampleIDSubject])
REFERENCES [dbo].[samples] ([sampleID])
GO
ALTER TABLE [dbo].[sampleRelationships] CHECK CONSTRAINT [FK_sampleRelationships.sampleIDSubject]
GO
ALTER TABLE [dbo].[sampleRelationships] WITH CHECK ADD CONSTRAINT [FK_sampleRelationships.sampleRelID] FOREIGN KEY([sampleRelID])
REFERENCES [dbo].[parts] ([partID])
GO
ALTER TABLE [dbo].[sampleRelationships] CHECK CONSTRAINT [FK_sampleRelationships.sampleRelID]
GO
ALTER TABLE [dbo].[samples] WITH CHECK ADD CONSTRAINT [FK_samples.collType] FOREIGN KEY([collType])
REFERENCES [dbo].[parts] ([partID])
GO
ALTER TABLE [dbo].[samples] CHECK CONSTRAINT [FK_samples.collType]
GO
ALTER TABLE [dbo].[samples] WITH CHECK ADD CONSTRAINT [FK_samples.contactID] FOREIGN KEY([contactID])
REFERENCES [dbo].[contacts] ([contactID])
GO
ALTER TABLE [dbo].[samples] CHECK CONSTRAINT [FK_samples.contactID]
GO
ALTER TABLE [dbo].[samples] WITH CHECK ADD CONSTRAINT [FK_samples.datasetID] FOREIGN KEY([datasetID])
REFERENCES [dbo].[datasets] ([datasetID])
GO
ALTER TABLE [dbo].[samples] CHECK CONSTRAINT [FK_samples.datasetID]
GO
ALTER TABLE [dbo].[samples] WITH CHECK ADD CONSTRAINT [FK_samples.organizationID] FOREIGN KEY([organizationID])
REFERENCES [dbo].[organizations] ([organizationID])
GO
ALTER TABLE [dbo].[samples] CHECK CONSTRAINT [FK_samples.organizationID]
GO
ALTER TABLE [dbo].[samples] WITH CHECK ADD CONSTRAINT [FK_samples.protocolID] FOREIGN KEY([protocolID])
REFERENCES [dbo].[protocols] ([protocolID])
GO
ALTER TABLE [dbo].[samples] CHECK CONSTRAINT [FK_samples.protocolID]
GO
ALTER TABLE [dbo].[samples] WITH CHECK ADD CONSTRAINT [FK_samples.protocolID_partID] FOREIGN KEY([protocolID])
REFERENCES [dbo].[parts] ([partID])
GO
ALTER TABLE [dbo].[samples] CHECK CONSTRAINT [FK_samples.protocolID_partID]
GO
ALTER TABLE [dbo].[samples] WITH CHECK ADD CONSTRAINT [FK_samples.purposeID] FOREIGN KEY([purposeID])
REFERENCES [dbo].[parts] ([partID])
GO
ALTER TABLE [dbo].[samples] CHECK CONSTRAINT [FK_samples.purposeID]
GO
ALTER TABLE [dbo].[samples] WITH CHECK ADD CONSTRAINT [FK_samples.repType] FOREIGN KEY([repType])
REFERENCES [dbo].[parts] ([partID])
GO
ALTER TABLE [dbo].[samples] CHECK CONSTRAINT [FK_samples.repType]
GO
ALTER TABLE [dbo].[samples] WITH CHECK ADD CONSTRAINT [FK_samples.saMaterial] FOREIGN KEY([saMaterial])
REFERENCES [dbo].[parts] ([partID])
GO
ALTER TABLE [dbo].[samples] CHECK CONSTRAINT [FK_samples.saMaterial]
GO
ALTER TABLE [dbo].[sites] WITH CHECK ADD CONSTRAINT [FK_sites.datasetID] FOREIGN KEY([datasetID])
REFERENCES [dbo].[datasets] ([datasetID])
GO
ALTER TABLE [dbo].[sites] CHECK CONSTRAINT [FK_sites.datasetID]
GO
ALTER TABLE [dbo].[sites] WITH CHECK ADD CONSTRAINT [FK_sites.organizationID] FOREIGN KEY([organizationID])
REFERENCES [dbo].[organizations] ([organizationID])
GO
ALTER TABLE [dbo].[sites] CHECK CONSTRAINT [FK_sites.organizationID]
GO
ALTER TABLE [dbo].[sites] WITH CHECK ADD CONSTRAINT [FK_sites.parSiteID] FOREIGN KEY([siteID])
REFERENCES [dbo].[sites] ([siteID])
GO
ALTER TABLE [dbo].[sites] CHECK CONSTRAINT [FK_sites.parSiteID]
GO
ALTER TABLE [dbo].[sites] WITH CHECK ADD CONSTRAINT [FK_sites.polygonID] FOREIGN KEY([polygonID])
REFERENCES [dbo].[polygons] ([polygonID])
GO
ALTER TABLE [dbo].[sites] CHECK CONSTRAINT [FK_sites.polygonID]
GO
ALTER TABLE [dbo].[sites] WITH CHECK ADD CONSTRAINT [FK_sites.repOrg1] FOREIGN KEY([repOrg1])
REFERENCES [dbo].[organizations] ([organizationID])
GO
ALTER TABLE [dbo].[sites] CHECK CONSTRAINT [FK_sites.repOrg1]
GO
ALTER TABLE [dbo].[sites] WITH CHECK ADD CONSTRAINT [FK_sites.repOrg2] FOREIGN KEY([repOrg2])
REFERENCES [dbo].[organizations] ([organizationID])
GO
ALTER TABLE [dbo].[sites] CHECK CONSTRAINT [FK_sites.repOrg2]
GO
ALTER TABLE [dbo].[sites] WITH CHECK ADD CONSTRAINT [FK_sites.siteType] FOREIGN KEY([siteType])
REFERENCES [dbo].[parts] ([partID])
GO
ALTER TABLE [dbo].[sites] CHECK CONSTRAINT [FK_sites.siteType]
GO
ALTER TABLE [dbo].[translations] WITH CHECK ADD CONSTRAINT [FK_translations.lang] FOREIGN KEY([lang])
REFERENCES [dbo].[languages] ([lang])
GO
ALTER TABLE [dbo].[translations] CHECK CONSTRAINT [FK_translations.lang]
GO
ALTER TABLE [dbo].[translations] WITH CHECK ADD CONSTRAINT [FK_translations.part] FOREIGN KEY([part])
REFERENCES [dbo].[parts] ([partID])
GO
ALTER TABLE [dbo].[translations] CHECK CONSTRAINT [FK_translations.part]
GO
ALTER TABLE [dbo].[wideNames] WITH CHECK ADD CONSTRAINT [FK_wideNames.aggregationInput] FOREIGN KEY([aggregationInput])
REFERENCES [dbo].[parts] ([partID])
GO
ALTER TABLE [dbo].[wideNames] CHECK CONSTRAINT [FK_wideNames.aggregationInput]
GO
ALTER TABLE [dbo].[wideNames] WITH CHECK ADD CONSTRAINT [FK_wideNames.attributeInput] FOREIGN KEY([attributeInput])
REFERENCES [dbo].[parts] ([partID])
GO
ALTER TABLE [dbo].[wideNames] CHECK CONSTRAINT [FK_wideNames.attributeInput]
GO
ALTER TABLE [dbo].[wideNames] WITH CHECK ADD CONSTRAINT [FK_wideNames.compartmentInput] FOREIGN KEY([compartmentInput])
REFERENCES [dbo].[parts] ([partID])
GO
ALTER TABLE [dbo].[wideNames] CHECK CONSTRAINT [FK_wideNames.compartmentInput]
GO
ALTER TABLE [dbo].[wideNames] WITH CHECK ADD CONSTRAINT [FK_wideNames.fractionInput] FOREIGN KEY([fractionInput])
REFERENCES [dbo].[parts] ([partID])
GO
ALTER TABLE [dbo].[wideNames] CHECK CONSTRAINT [FK_wideNames.fractionInput]
GO
ALTER TABLE [dbo].[wideNames] WITH CHECK ADD CONSTRAINT [FK_wideNames.measureInput] FOREIGN KEY([measureInput])
REFERENCES [dbo].[parts] ([partID])
GO
ALTER TABLE [dbo].[wideNames] CHECK CONSTRAINT [FK_wideNames.measureInput]
GO
ALTER TABLE [dbo].[wideNames] WITH CHECK ADD CONSTRAINT [FK_wideNames.methodInput] FOREIGN KEY([methodInput])
REFERENCES [dbo].[parts] ([partID])
GO
ALTER TABLE [dbo].[wideNames] CHECK CONSTRAINT [FK_wideNames.methodInput]
GO
ALTER TABLE [dbo].[wideNames] WITH CHECK ADD CONSTRAINT [FK_wideNames.partTypeInput] FOREIGN KEY([partTypeInput])
REFERENCES [dbo].[parts] ([partID])
GO
ALTER TABLE [dbo].[wideNames] CHECK CONSTRAINT [FK_wideNames.partTypeInput]
GO
ALTER TABLE [dbo].[wideNames] WITH CHECK ADD CONSTRAINT [FK_wideNames.reportTableInput] FOREIGN KEY([reportTableInput])
REFERENCES [dbo].[parts] ([partID])
GO
ALTER TABLE [dbo].[wideNames] CHECK CONSTRAINT [FK_wideNames.reportTableInput]
GO
ALTER TABLE [dbo].[wideNames] WITH CHECK ADD CONSTRAINT [FK_wideNames.specimenInput] FOREIGN KEY([specimenInput])
REFERENCES [dbo].[parts] ([partID])
GO
ALTER TABLE [dbo].[wideNames] CHECK CONSTRAINT [FK_wideNames.specimenInput]
GO
ALTER TABLE [dbo].[wideNames] WITH CHECK ADD CONSTRAINT [FK_wideNames.unitInput] FOREIGN KEY([unitInput])
REFERENCES [dbo].[parts] ([partID])
GO
ALTER TABLE [dbo].[wideNames] CHECK CONSTRAINT [FK_wideNames.unitInput]
GO
ALTER TABLE [dbo].[zones] WITH CHECK ADD CONSTRAINT [FK_zones.isoCode] FOREIGN KEY([isoCode])
REFERENCES [dbo].[countries] ([isoCode])
GO
ALTER TABLE [dbo].[zones] CHECK CONSTRAINT [FK_zones.isoCode]
GO

```

---

<div class="post-metadata">

**Author:** ![matthomson](https://avatars.discourse-cdn.com/v4/letter/m/dec6dc/32.png) [@matthomson](https://odm.discourse.group/u/matthomson)\
**Post date:** [October 5, 2023, 9:02pm UTC](https://odm.discourse.group/t/cycles-in-the-code-generated-by-lucid/135/4 "2023-10-05T21:02:07Z")

</div>

Thanks for this detailed review, Sorin - apologies that it took me so long to look it over.

It makes sense now, thinking about the `sets_setID-parts_partID` caused the cycle. The `int` vs `bool` makes sense too. I’ll make a note in the dictionary, maybe, though perhaps @jeandavidt and @dmanuel and I should also chat about it. I know that we had long chats about boolean variables before, so this is maybe an inevitable sequel.

For the setting of `sets_setID`, `translations_lang` and `sampleRelationships_sampleRelID` as PKs, I think this is all fine as well. My only real concern is about consistency across the ERD in terms of how we codify the relationship between `parts_partID` and the various parts in the model, particularly without redundancy. But I think if we make note of the rules that guide these decisions (i.e. that every table needs at least one PK, so in such an instance, the partID reference may become a PK), we should be good. I don’t see any other issues. I’ll mainline these changes in lucid chart right away.

On a slight tangential note - I have a memory that PKs are supposed to be unique across all rows in order to be “true” PKs. Is this true? Is this a concern we need to take action on?

If so, I don’t think it’ll take too much to fix; we can add a `saReportID` (sample relationship report ID) to be a unique identifier for describing a sample relationship, for example, or adding a “sets entry field” and “translation entry field” that are unique codes for each entry. If this is a non-issue though, please let me know and I’ll disregard these thoughts.

---

<div class="post-metadata">

**Author:** ![matthomson](https://avatars.discourse-cdn.com/v4/letter/m/dec6dc/32.png) [@matthomson](https://odm.discourse.group/u/matthomson)\
**Post date:** [October 5, 2023, 9:04pm UTC](https://odm.discourse.group/t/cycles-in-the-code-generated-by-lucid/135/5 "2023-10-05T21:04:36Z")

</div>

Also odd that there are weird little glitches like the relationship reversal and duplication of some relationships. Good to be conscious of it, though. Thank you for catching these things!

---

<div class="post-metadata">

**Author:** ![Sorin](https://yyz2.discourse-cdn.com/free1/user_avatar/odm.discourse.group/sorin/32/52_2.png) [@Sorin](https://odm.discourse.group/u/Sorin)\
**Post date:** [October 5, 2023, 9:36pm UTC](https://odm.discourse.group/t/cycles-in-the-code-generated-by-lucid/135/6 "2023-10-05T21:36:10Z")

</div>

Indeed, PK’s should be unique for each row. If we don’t have a “true” PK automatically generated or derived from the content of the table, by uniqueness of the records in some column, we should strive to generate a composite one by combining multiple columns that together would ensure uniqueness. Anyhow, to maintain compatibility with Lucid and easily extract (almost) workable SQL code from it, it is best to define primary keys by identifying the naturally unique attributes (but non-values) in each table and I think at this stage we acquired that, at least in the model, so no need to get fancy with the **sampleRelationships** table and such. The moment of truth will come when real data will start pouring in the structure - aka “when the s\*\*t hits the fan” anyhow 🙂