BETYdb Schema With Uniqueness and Foreign Key Constraints

betydb_schema.sql

CREATE TABLE "citations" (
"id" int4 NOT NULL DEFAULT nextval('citations_id_seq'::regclass),
"author" varchar(255) COLLATE "default" NOT NULL,
"year" int4 NOT NULL,
"title" varchar(255) COLLATE "default" NOT NULL,
"journal" varchar(255) COLLATE "default",
"vol" int4,
"pg" varchar(255) COLLATE "default",
"url" varchar(512) COLLATE "default",
"pdf" varchar(255) COLLATE "default",
"created_at" timestamp(6),
"updated_at" timestamp(6),
"doi" varchar(255) COLLATE "default",
"user_id" int4,
CONSTRAINT "citations_pkey" PRIMARY KEY ("id") ,
UNIQUE ("author", "year", "title")
);

CREATE INDEX "index_citations_on_user_id" ON "citations" ("user_id" ASC);
COMMENT ON COLUMN "citations"."author" IS 'last name of first author';
COMMENT ON COLUMN "citations"."year" IS 'year of publication';
COMMENT ON COLUMN "citations"."title" IS 'article title';
COMMENT ON COLUMN "citations"."journal" IS 'Journal name';
COMMENT ON COLUMN "citations"."pg" IS 'page range of article';
COMMENT ON COLUMN "citations"."url" IS 'link to article url';
COMMENT ON COLUMN "citations"."pdf" IS 'link to pdf version of article';
COMMENT ON COLUMN "citations"."doi" IS 'Digital Object Identifier';

CREATE TABLE "citations_sites" (
"citation_id" int4 NOT NULL,
"site_id" int4 NOT NULL,
"created_at" timestamp(6),
"updated_at" timestamp(6),
UNIQUE ("citation_id", "site_id")
);

CREATE UNIQUE INDEX "index_citations_sites_on_citation_id_and_site_id" ON "citations_sites" ("citation_id" ASC, "site_id" ASC);

CREATE TABLE "covariates" (
"id" int4 NOT NULL DEFAULT nextval('covariates_id_seq'::regclass),
"trait_id" int4 NOT NULL,
"variable_id" int4 NOT NULL,
"level" numeric(16,4),
"created_at" timestamp(6),
"updated_at" timestamp(6),
"n" int4,
"statname" varchar(255) COLLATE "default",
"stat" numeric(16,4),
CONSTRAINT "covariates_pkey" PRIMARY KEY ("id") ,
UNIQUE ("trait_id", "variable_id")
);

CREATE INDEX "index_covariates_on_trait_id_and_variable_id" ON "covariates" ("trait_id" ASC, "variable_id" ASC);
COMMENT ON COLUMN "covariates"."level" IS 'Value of covariate, units are determined in variables table by the variable_id foreign key.';

CREATE TABLE "cultivars" (
"id" int4 NOT NULL DEFAULT nextval('cultivars_id_seq'::regclass),
"specie_id" int4 NOT NULL,
"name" varchar(255) COLLATE "default" NOT NULL,
"ecotype" varchar(255) COLLATE "default",
"notes" text COLLATE "default",
"created_at" timestamp(6),
"updated_at" timestamp(6),
"previous_id" varchar(255) COLLATE "default",
CONSTRAINT "cultivars_pkey" PRIMARY KEY ("id") ,
UNIQUE ("specie_id", "name")
);

CREATE INDEX "index_cultivars_on_specie_id" ON "cultivars" ("specie_id" ASC);
COMMENT ON COLUMN "cultivars"."name" IS 'Cultivar name given by breeder or reported in citation.';
COMMENT ON COLUMN "cultivars"."ecotype" IS 'Does not apply for all species, used in the case of switchgrass to differentiate lowland and upland genotypes.';

CREATE TABLE "dbfiles" (
"id" int4 NOT NULL DEFAULT nextval('dbfiles_id_seq'::regclass),
"file_name" varchar(255) COLLATE "default" NOT NULL,
"file_path" varchar(255) COLLATE "default" NOT NULL,
"md5" varchar(255) COLLATE "default",
"created_user_id" int4,
"updated_user_id" int4,
"machine_id" int4 NOT NULL,
"created_at" timestamp(6),
"updated_at" timestamp(6),
"container_type" varchar(255) COLLATE "default" NOT NULL,
"container_id" int4 NOT NULL DEFAULT foreign key for either inputs or posteriors (table specified by container_type),
CONSTRAINT "dbfiles_pkey" PRIMARY KEY ("id") ,
CONSTRAINT "container" UNIQUE ("container_type", "container_id"),
CONSTRAINT "file" UNIQUE ("file_name", "file_path", "machine_id")
);

CREATE INDEX "index_dbfiles_on_container_id_and_container_type" ON "dbfiles" ("container_type" ASC);
CREATE INDEX "index_dbfiles_on_created_user_id" ON "dbfiles" ("created_user_id" ASC);
CREATE INDEX "index_dbfiles_on_machine_id" ON "dbfiles" ("machine_id" ASC);
CREATE INDEX "index_dbfiles_on_updated_user_id" ON "dbfiles" ("updated_user_id" ASC);
COMMENT ON COLUMN "dbfiles"."container_type" IS 'this and container_id are part of a polymorphic relationship, specifies table and primary key of that table';

CREATE TABLE "ensembles" (
"id" int4 NOT NULL DEFAULT nextval('ensembles_id_seq'::regclass),
"notes" text COLLATE "default",
"created_at" timestamp(6),
"updated_at" timestamp(6),
"runtype" varchar(255) COLLATE "default",
"workflow_id" int4 NOT NULL,
CONSTRAINT "ensembles_pkey" PRIMARY KEY ("id") 
);

CREATE TABLE "entities" (
"id" int4 NOT NULL DEFAULT nextval('entities_id_seq'::regclass),
"parent_id" int4,
"name" varchar(255) COLLATE "default",
"notes" text COLLATE "default",
"created_at" timestamp(6),
"updated_at" timestamp(6),
CONSTRAINT "entities_pkey" PRIMARY KEY ("id") 
);

CREATE INDEX "index_entities_on_parent_id" ON "entities" ("parent_id" ASC);

CREATE TABLE "formats" (
"id" int4 NOT NULL DEFAULT nextval('formats_id_seq'::regclass),
"dataformat" text COLLATE "default",
"notes" text COLLATE "default",
"created_at" timestamp(6),
"updated_at" timestamp(6),
"name" varchar(255) COLLATE "default",
"header" varchar(255) COLLATE "default",
"skip" varchar(255) COLLATE "default",
"mimetype_id" int4,
CONSTRAINT "formats_pkey" PRIMARY KEY ("id") ,
UNIQUE ("dataformat")
);

CREATE TABLE "formats_variables" (
"id" int4 NOT NULL DEFAULT nextval('formats_variables_id_seq'::regclass),
"format_id" int4,
"variable_id" int4,
"name" varchar(255) COLLATE "default",
"unit" varchar(255) COLLATE "default",
"storage_type" varchar(255) COLLATE "default",
"column_number" int4,
"created_at" timestamp(6),
"updated_at" timestamp(6),
CONSTRAINT "formats_variables_pkey" PRIMARY KEY ("id") 
);

CREATE INDEX "index_formats_variables_on_format_id_and_variable_id" ON "formats_variables" ("format_id" ASC, "variable_id" ASC);

CREATE TABLE "inputs" (
"id" int4 NOT NULL DEFAULT nextval('inputs_id_seq'::regclass),
"site_id" int4,
"notes" text COLLATE "default",
"created_at" timestamp(6),
"updated_at" timestamp(6),
"start_date" timestamp(6),
"end_date" timestamp(6),
"name" varchar(255) COLLATE "default" NOT NULL,
"parent_id" int4,
"user_id" int4,
"access_level" int4 NOT NULL,
"raw" bool,
"format_id" int4 NOT NULL,
CONSTRAINT "inputs_pkey" PRIMARY KEY ("id") ,
UNIQUE ("site_id", "start_date", "end_date", "format_id")
);

CREATE INDEX "index_inputs_on_format_id" ON "inputs" ("format_id" ASC);
CREATE INDEX "index_inputs_on_parent_id" ON "inputs" ("parent_id" ASC);
CREATE INDEX "index_inputs_on_site_id" ON "inputs" ("site_id" ASC);
CREATE INDEX "index_inputs_on_user_id" ON "inputs" ("user_id" ASC);

CREATE TABLE "inputs_runs" (
"input_id" int4 NOT NULL,
"run_id" int4 NOT NULL,
"created_at" timestamp(6),
"updated_at" timestamp(6),
UNIQUE ("input_id", "run_id")
);

CREATE UNIQUE INDEX "index_inputs_runs_on_input_id_and_run_id" ON "inputs_runs" ("input_id" ASC, "run_id" ASC);

CREATE TABLE "inputs_variables" (
"input_id" int4 NOT NULL,
"variable_id" int4 NOT NULL,
"created_at" timestamp(6),
"updated_at" timestamp(6),
UNIQUE ("input_id", "variable_id")
);

CREATE UNIQUE INDEX "index_inputs_variables_on_input_id_and_variable_id" ON "inputs_variables" ("input_id" ASC, "variable_id" ASC);

CREATE TABLE "likelihoods" (
"id" int4 NOT NULL DEFAULT nextval('likelihoods_id_seq'::regclass),
"run_id" int4 NOT NULL,
"variable_id" int4 NOT NULL,
"input_id" int4 NOT NULL,
"loglikelihood" numeric(10),
"n_eff" numeric(10),
"weight" numeric(10),
"residual" numeric(10),
"created_at" timestamp(6),
"updated_at" timestamp(6),
CONSTRAINT "likelihoods_pkey" PRIMARY KEY ("id") ,
UNIQUE ("run_id", "variable_id", "input_id")
);

CREATE INDEX "index_likelihoods_on_input_id" ON "likelihoods" ("input_id" ASC);
CREATE INDEX "index_likelihoods_on_run_id" ON "likelihoods" ("run_id" ASC);
CREATE INDEX "index_likelihoods_on_variable_id" ON "likelihoods" ("variable_id" ASC);

CREATE TABLE "machines" (
"id" int4 NOT NULL DEFAULT nextval('machines_id_seq'::regclass),
"hostname" varchar(255) COLLATE "default" NOT NULL,
"created_at" timestamp(6),
"updated_at" timestamp(6),
CONSTRAINT "machines_pkey" PRIMARY KEY ("id") ,
UNIQUE ("hostname")
);

CREATE INDEX "index_machines_on_hostname" ON "machines" ("hostname" ASC);

CREATE TABLE "managements" (
"id" int4 NOT NULL DEFAULT nextval('managements_id_seq'::regclass),
"citation_id" int4,
"date" date NOT NULL,
"dateloc" numeric(4,2),
"mgmttype" varchar(255) COLLATE "default" NOT NULL,
"level" numeric(16,4),
"units" varchar(255) COLLATE "default",
"notes" text COLLATE "default",
"created_at" timestamp(6),
"updated_at" timestamp(6),
"user_id" int4,
CONSTRAINT "managements_pkey" PRIMARY KEY ("id") ,
UNIQUE ("date", "mgmttype")
);

CREATE INDEX "index_managements_on_user_id" ON "managements" ("user_id" ASC);
CREATE INDEX "index_managements_on_citation_id" ON "managements" ("citation_id" ASC);
COMMENT ON COLUMN "managements"."date" IS 'Date on which management was conducted.';
COMMENT ON COLUMN "managements"."dateloc" IS 'Level of confidence in value given as date. See documentation for details.';
COMMENT ON COLUMN "managements"."mgmttype" IS 'Type of management';
COMMENT ON COLUMN "managements"."level" IS 'Amount applied, not always required.';
COMMENT ON COLUMN "managements"."units" IS 'units, standardized for each management type.';

CREATE TABLE "managements_treatments" (
"treatment_id" int4 NOT NULL,
"management_id" int4 NOT NULL,
"created_at" timestamp(6),
"updated_at" timestamp(6),
UNIQUE ("treatment_id", "management_id")
);

CREATE UNIQUE INDEX "index_managements_treatments_on_management_id_and_treatment_id" ON "managements_treatments" ("management_id" ASC, "treatment_id" ASC);

CREATE TABLE "methods" (
"id" int4 NOT NULL DEFAULT nextval('methods_id_seq'::regclass),
"name" varchar(255) COLLATE "default" NOT NULL,
"description" text COLLATE "default" NOT NULL,
"citation_id" int4 NOT NULL,
"created_at" timestamp(6),
"updated_at" timestamp(6),
CONSTRAINT "methods_pkey" PRIMARY KEY ("id") ,
UNIQUE ("name", "citation_id")
);

CREATE INDEX "index_methods_on_citation_id" ON "methods" ("citation_id" ASC);

CREATE TABLE "mimetypes" (
"id" int4 NOT NULL DEFAULT nextval('mimetypes_id_seq'::regclass),
"type_string" varchar(255) COLLATE "default" NOT NULL,
CONSTRAINT "mimetypes_pkey" PRIMARY KEY ("id") ,
UNIQUE ("type_string")
);

CREATE TABLE "models" (
"id" int4 NOT NULL DEFAULT nextval('models_id_seq'::regclass),
"model_name" varchar(255) COLLATE "default" NOT NULL,
"model_path" varchar(255) COLLATE "default" NOT NULL,
"revision" varchar(255) COLLATE "default" NOT NULL,
"parent_id" int4,
"created_at" timestamp(6),
"updated_at" timestamp(6),
"model_type" varchar(255) COLLATE "default" NOT NULL,
CONSTRAINT "models_pkey" PRIMARY KEY ("id") ,
UNIQUE ("model_path")
);

CREATE INDEX "index_models_on_parent_id" ON "models" ("parent_id" ASC);

CREATE TABLE "pfts" (
"id" int4 NOT NULL DEFAULT nextval('pfts_id_seq'::regclass),
"definition" text COLLATE "default" NOT NULL,
"created_at" timestamp(6),
"updated_at" timestamp(6),
"name" varchar(255) COLLATE "default" NOT NULL,
CONSTRAINT "pfts_pkey" PRIMARY KEY ("id") ,
UNIQUE ("name")
);

COMMENT ON COLUMN "pfts"."definition" IS 'Defines the creator and context under which the pft will be used.';
COMMENT ON COLUMN "pfts"."name" IS 'unique identifier used by PEcAn.';

CREATE TABLE "pfts_priors" (
"pft_id" int4 NOT NULL,
"prior_id" int4 NOT NULL,
"created_at" timestamp(6),
"updated_at" timestamp(6),
UNIQUE ("pft_id", "prior_id")
);

CREATE UNIQUE INDEX "index_pfts_priors_on_pft_id_and_prior_id" ON "pfts_priors" ("pft_id" ASC, "prior_id" ASC);

CREATE TABLE "pfts_species" (
"pft_id" int4 NOT NULL,
"specie_id" int4 NOT NULL,
"created_at" timestamp(6),
"updated_at" timestamp(6),
UNIQUE ("pft_id", "specie_id")
);

CREATE UNIQUE INDEX "index_pfts_species_on_pft_id_and_specie_id" ON "pfts_species" ("pft_id" ASC, "specie_id" ASC);

CREATE TABLE "posteriors" (
"id" int4 NOT NULL DEFAULT nextval('posteriors_id_seq'::regclass),
"pft_id" int4 NOT NULL,
"created_at" timestamp(6),
"updated_at" timestamp(6),
"format_id" int4 NOT NULL,
CONSTRAINT "posteriors_pkey" PRIMARY KEY ("id") ,
UNIQUE ("pft_id", "format_id")
);

CREATE INDEX "index_posteriors_on_pft_id" ON "posteriors" ("pft_id" ASC);

CREATE TABLE "posteriors_runs" (
"posterior_id" int4 NOT NULL,
"run_id" int4 NOT NULL,
"created_at" timestamp(6),
"updated_at" timestamp(6),
UNIQUE ("posterior_id", "run_id")
);

CREATE UNIQUE INDEX "index_posteriors_runs_on_posterior_id_and_run_id" ON "posteriors_runs" ("posterior_id" ASC, "run_id" ASC);

CREATE TABLE "priors" (
"id" int4 NOT NULL DEFAULT nextval('priors_id_seq'::regclass),
"citation_id" int4 NOT NULL,
"variable_id" int4 NOT NULL,
"phylogeny" varchar(255) COLLATE "default",
"distn" varchar(255) COLLATE "default" NOT NULL,
"parama" numeric(16,4) NOT NULL,
"paramb" numeric(16,4) NOT NULL,
"paramc" numeric(16,4),
"n" int4,
"notes" text COLLATE "default",
"created_at" timestamp(6),
"updated_at" timestamp(6),
CONSTRAINT "priors_pkey" PRIMARY KEY ("id") ,
CONSTRAINT "phylogeny_variable_notes" UNIQUE ("variable_id", "phylogeny", "notes"),
CONSTRAINT "phylogeny_variable_citation" UNIQUE ("citation_id", "variable_id", "phylogeny")
);

COMMENT ON CONSTRAINT "phylogeny_variable_notes" ON "priors" IS 'ensures that if there are two priors for the same group of plants, there will be an explanation of the difference in the notes; alternatively, there will be distinct citations that define the source';
COMMENT ON CONSTRAINT "phylogeny_variable_citation" ON "priors" IS 'phylogeny-variable is either unique b/c of citation or difference is written into the notes';
CREATE INDEX "index_priors_on_citation_id" ON "priors" ("citation_id" ASC);
CREATE INDEX "index_priors_on_variable_id" ON "priors" ("variable_id" ASC);
COMMENT ON COLUMN "priors"."phylogeny" IS 'Used to note the group of plants for which the prior was specified, often the group of plants represented by the data used to specify the prior.';
COMMENT ON COLUMN "priors"."distn" IS 'Name of the probability distribution, using R naming convention (e.g. ''beta'',''f'', ''gamma'', ''lnorm'', ''norm'', ''pois'', ''t'', ''unif'', ''weibull''.';
COMMENT ON COLUMN "priors"."parama" IS 'First parameter for distribution, as specified by R.';
COMMENT ON COLUMN "priors"."paramb" IS 'Second parameter for distribution, as specified by R.';
COMMENT ON COLUMN "priors"."paramc" IS 'A third parameter, if required.';
COMMENT ON COLUMN "priors"."n" IS 'number of observations used to specify prior.';

CREATE TABLE "runs" (
"id" int4 NOT NULL DEFAULT nextval('runs_id_seq'::regclass),
"model_id" int4 NOT NULL,
"site_id" int4 NOT NULL,
"start_time" timestamp(6) NOT NULL,
"finish_time" timestamp(6) NOT NULL,
"outdir" varchar(255) COLLATE "default" NOT NULL,
"outprefix" varchar(255) COLLATE "default" NOT NULL,
"setting" varchar(255) COLLATE "default" NOT NULL,
"parameter_list" text COLLATE "default" NOT NULL,
"created_at" timestamp(6),
"updated_at" timestamp(6),
"started_at" timestamp(6) NOT NULL,
"finished_at" timestamp(6),
"ensemble_id" int4 NOT NULL,
CONSTRAINT "runs_pkey" PRIMARY KEY ("id") ,
UNIQUE ("model_id", "site_id", "start_time", "finish_time", "parameter_list", "ensemble_id")
);

CREATE INDEX "index_runs_on_ensemble_id" ON "runs" ("ensemble_id" ASC);
CREATE INDEX "index_runs_on_model_id" ON "runs" ("model_id" ASC);
CREATE INDEX "index_runs_on_site_id" ON "runs" ("site_id" ASC);
COMMENT ON COLUMN "runs"."start_time" IS 'beginning of time period being simulated';
COMMENT ON COLUMN "runs"."finish_time" IS 'end of time period being simulated';
COMMENT ON COLUMN "runs"."started_at" IS 'system time when run ends';
COMMENT ON COLUMN "runs"."finished_at" IS 'system time when run ends; can be null when record is created';

CREATE TABLE "schema_migrations" (
"version" varchar(255) COLLATE "default" NOT NULL,
PRIMARY KEY ("version") 
);

CREATE UNIQUE INDEX "unique_schema_migrations" ON "schema_migrations" ("version" ASC);

CREATE TABLE "sites" (
"id" int4 NOT NULL DEFAULT nextval('sites_id_seq'::regclass),
"city" varchar(255) COLLATE "default",
"state" varchar(255) COLLATE "default",
"country" varchar(255) COLLATE "default",
"lat" numeric(9,6) NOT NULL,
"lon" numeric(9,6) NOT NULL,
"mat" int4,
"map" int4,
"masl" int4,
"soil" varchar(255) COLLATE "default",
"som" numeric(4,2),
"notes" text COLLATE "default",
"soilnotes" text COLLATE "default",
"created_at" timestamp(6),
"updated_at" timestamp(6),
"sitename" varchar(255) COLLATE "default" NOT NULL,
"greenhouse" bool NOT NULL,
"user_id" int4,
"local_time" int4,
"sand_pct" numeric(9,5),
"clay_pct" numeric(9,5),
"espg" varchar(255) COLLATE "default",
CONSTRAINT "sites_pkey" PRIMARY KEY ("id") ,
UNIQUE ("lat", "lon", "sitename")
);

CREATE INDEX "index_sites_on_user_id" ON "sites" ("user_id" ASC);
COMMENT ON COLUMN "sites"."city" IS 'Nearest city to site.';
COMMENT ON COLUMN "sites"."state" IS 'If in the United States, state in which study is conducted.';
COMMENT ON COLUMN "sites"."lat" IS 'Latitude, in decimal degrees';
COMMENT ON COLUMN "sites"."lon" IS 'Longitude, in decimal degrees.';
COMMENT ON COLUMN "sites"."mat" IS 'Mean Annual Temperature (C)';
COMMENT ON COLUMN "sites"."map" IS 'Mean Annual Precipitation (mm)';
COMMENT ON COLUMN "sites"."masl" IS 'Elevation (m above sea level)';
COMMENT ON COLUMN "sites"."soil" IS 'Soil type, as described in documentation.';
COMMENT ON COLUMN "sites"."som" IS 'Depreciated';
COMMENT ON COLUMN "sites"."greenhouse" IS 'Boolean: indicates if study was conducted in a field (0) or greenhouse, pot, or growth chamber (1)';

CREATE TABLE "species" (
"id" int4 NOT NULL DEFAULT nextval('species_id_seq'::regclass),
"spcd" int4,
"genus" varchar(255) COLLATE "default" NOT NULL,
"species" varchar(255) COLLATE "default" NOT NULL,
"scientificname" varchar(255) COLLATE "default" NOT NULL,
"commonname" varchar(255) COLLATE "default",
"notes" varchar(255) COLLATE "default",
"created_at" timestamp(6),
"updated_at" timestamp(6),
"AcceptedSymbol" varchar(255) COLLATE "default",
"SynonymSymbol" varchar(255) COLLATE "default",
"Symbol" varchar(255) COLLATE "default",
"PLANTS_Floristic_Area" text COLLATE "default",
"State" text COLLATE "default",
"Category" varchar(255) COLLATE "default",
"Family" varchar(255) COLLATE "default",
"FamilySymbol" varchar(255) COLLATE "default",
"FamilyCommonName" varchar(255) COLLATE "default",
"xOrder" varchar(255) COLLATE "default",
"SubClass" varchar(255) COLLATE "default",
"Class" varchar(255) COLLATE "default",
"SubDivision" varchar(255) COLLATE "default",
"Division" varchar(255) COLLATE "default",
"SuperDivision" varchar(255) COLLATE "default",
"SubKingdom" varchar(255) COLLATE "default",
"Kingdom" varchar(255) COLLATE "default",
"ITIS_TSN" int4,
"Duration" varchar(255) COLLATE "default",
"GrowthHabit" varchar(255) COLLATE "default",
"NativeStatus" varchar(255) COLLATE "default",
"NationalWetlandIndicatorStatus" varchar(255) COLLATE "default",
"RegionalWetlandIndicatorStatus" varchar(255) COLLATE "default",
"ActiveGrowthPeriod" varchar(255) COLLATE "default",
"AfterHarvestRegrowthRate" varchar(255) COLLATE "default",
"Bloat" varchar(255) COLLATE "default",
"C2N_Ratio" varchar(255) COLLATE "default",
"CoppicePotential" varchar(255) COLLATE "default",
"FallConspicuous" varchar(255) COLLATE "default",
"FireResistance" varchar(255) COLLATE "default",
"FoliageTexture" varchar(255) COLLATE "default",
"GrowthForm" varchar(255) COLLATE "default",
"GrowthRate" varchar(255) COLLATE "default",
"MaxHeight20Yrs" int4,
"MatureHeight" int4,
"KnownAllelopath" varchar(255) COLLATE "default",
"LeafRetention" varchar(255) COLLATE "default",
"Lifespan" varchar(255) COLLATE "default",
"LowGrowingGrass" varchar(255) COLLATE "default",
"NitrogenFixation" varchar(255) COLLATE "default",
"ResproutAbility" varchar(255) COLLATE "default",
"AdaptedCoarseSoils" varchar(255) COLLATE "default",
"AdaptedMediumSoils" varchar(255) COLLATE "default",
"AdaptedFineSoils" varchar(255) COLLATE "default",
"AnaerobicTolerance" varchar(255) COLLATE "default",
"CaCO3Tolerance" varchar(255) COLLATE "default",
"ColdStratification" varchar(255) COLLATE "default",
"DroughtTolerance" varchar(255) COLLATE "default",
"FertilityRequirement" varchar(255) COLLATE "default",
"FireTolerance" varchar(255) COLLATE "default",
"MinFrostFreeDays" int4,
"HedgeTolerance" varchar(255) COLLATE "default",
"MoistureUse" varchar(255) COLLATE "default",
"pH_Minimum" numeric(5,2),
"pH_Maximum" numeric(5,2),
"Min_PlantingDensity" int4,
"Max_PlantingDensity" int4,
"Precipitation_Minimum" int4,
"Precipitation_Maximum" int4,
"RootDepthMinimum" int4,
"SalinityTolerance" varchar(255) COLLATE "default",
"ShadeTolerance" varchar(255) COLLATE "default",
"TemperatureMinimum" int4,
"BloomPeriod" varchar(255) COLLATE "default",
"CommercialAvailability" varchar(255) COLLATE "default",
"FruitSeedPeriodBegin" varchar(255) COLLATE "default",
"FruitSeedPeriodEnd" varchar(255) COLLATE "default",
"Propogated_by_BareRoot" varchar(255) COLLATE "default",
"Propogated_by_Bulbs" varchar(255) COLLATE "default",
"Propogated_by_Container" varchar(255) COLLATE "default",
"Propogated_by_Corms" varchar(255) COLLATE "default",
"Propogated_by_Cuttings" varchar(255) COLLATE "default",
"Propogated_by_Seed" varchar(255) COLLATE "default",
"Propogated_by_Sod" varchar(255) COLLATE "default",
"Propogated_by_Sprigs" varchar(255) COLLATE "default",
"Propogated_by_Tubers" varchar(255) COLLATE "default",
"Seeds_per_Pound" int4,
"SeedSpreadRate" varchar(255) COLLATE "default",
"SeedlingVigor" varchar(255) COLLATE "default",
CONSTRAINT "species_pkey" PRIMARY KEY ("id") ,
UNIQUE ("scientificname")
);

CREATE TABLE "traits" (
"id" int4 NOT NULL DEFAULT nextval('traits_id_seq'::regclass),
"site_id" int4,
"specie_id" int4 NOT NULL,
"citation_id" int4 NOT NULL,
"cultivar_id" int4,
"treatment_id" int4 NOT NULL,
"date" timestamp(6),
"dateloc" numeric(4,2),
"time" time(6),
"timeloc" numeric(4,2),
"mean" numeric(16,4) NOT NULL,
"n" int4,
"statname" varchar(255) COLLATE "default",
"stat" numeric(16,4),
"notes" text COLLATE "default",
"created_at" timestamp(6),
"updated_at" timestamp(6),
"variable_id" int4 NOT NULL,
"user_id" int4,
"checked" int4 NOT NULL,
"access_level" int4 NOT NULL,
"entity_id" int4,
"method_id" int4,
"date_year" int4,
"date_month" int4,
"date_day" int4,
"time_hour" int4,
"time_minute" int4,
CONSTRAINT "traits_pkey" PRIMARY KEY ("id") ,
UNIQUE ("site_id", "specie_id", "citation_id", "cultivar_id", "treatment_id", "date", "time", "variable_id", "entity_id", "method_id", "date_year", "date_month", "date_day", "time_hour", "time_minute")
);

CREATE INDEX "index_traits_on_citation_id" ON "traits" ("citation_id" ASC);
CREATE INDEX "index_traits_on_cultivar_id" ON "traits" ("cultivar_id" ASC);
CREATE INDEX "index_traits_on_entity_id" ON "traits" ("entity_id" ASC);
CREATE INDEX "index_traits_on_method_id" ON "traits" ("method_id" ASC);
CREATE INDEX "index_traits_on_site_id" ON "traits" ("site_id" ASC);
CREATE INDEX "index_traits_on_specie_id" ON "traits" ("specie_id" ASC);
CREATE INDEX "index_traits_on_treatment_id" ON "traits" ("treatment_id" ASC);
CREATE INDEX "index_traits_on_user_id" ON "traits" ("user_id" ASC);
CREATE INDEX "index_traits_on_variable_id" ON "traits" ("variable_id" ASC);
COMMENT ON COLUMN "traits"."site_id" IS 'Site at which measurement was taken.';
COMMENT ON COLUMN "traits"."specie_id" IS 'Species on which measurement was taken.';
COMMENT ON COLUMN "traits"."citation_id" IS 'Citation in which data was originally reported.';
COMMENT ON COLUMN "traits"."cultivar_id" IS 'Cultivar information, if any.';
COMMENT ON COLUMN "traits"."treatment_id" IS 'Experimental treatment identification. Required, can indicate observational study.';
COMMENT ON COLUMN "traits"."date" IS 'Date on which measurement was made.';
COMMENT ON COLUMN "traits"."dateloc" IS 'Level of confidence in date. See documentation.';
COMMENT ON COLUMN "traits"."time" IS 'Time at which measurement was taken. Sometimes necessary, e.g. for photosynthesis measurements.';
COMMENT ON COLUMN "traits"."timeloc" IS 'Level of confidence in time.';
COMMENT ON COLUMN "traits"."mean" IS 'Mean value of trait.';
COMMENT ON COLUMN "traits"."n" IS 'Number of experimental replicates used to estimate mean and statistical summary.';
COMMENT ON COLUMN "traits"."statname" IS 'Name of reported statistic.';
COMMENT ON COLUMN "traits"."stat" IS 'Value of reported statistic.';
COMMENT ON COLUMN "traits"."variable_id" IS 'Links to information in variables table that describes trait being measured. ';
COMMENT ON COLUMN "traits"."user_id" IS 'ID of user who entered data.';
COMMENT ON COLUMN "traits"."checked" IS 'accepts values [-1, 0, 1]. 0 is default, and means that data have not been checked. 1 indicates that the data have been checked and are correct, -1 indicates that data have been checked and found to be incorrect or suspicious, e.g. outside of the acceptab';
COMMENT ON COLUMN "traits"."access_level" IS 'Level of access required to view data.';

CREATE TABLE "treatments" (
"id" int4 NOT NULL DEFAULT nextval('treatments_id_seq'::regclass),
"name" varchar(255) COLLATE "default" NOT NULL,
"definition" varchar(255) COLLATE "default",
"created_at" timestamp(6),
"updated_at" timestamp(6),
"control" bool NOT NULL,
"user_id" int4,
CONSTRAINT "treatments_pkey" PRIMARY KEY ("id") 
);

CREATE INDEX "index_treatments_on_user_id" ON "treatments" ("user_id" ASC);
COMMENT ON COLUMN "treatments"."name" IS 'Name of treatment, should be easy to associate with treatment name in original study.';
COMMENT ON COLUMN "treatments"."definition" IS 'Description of treatment, e.g. levels of fertilizer applied, etc. This information may be redundant with ''levels'' information recorded in Managements table.';
COMMENT ON COLUMN "treatments"."control" IS 'Boolean, indicates if treatment is a control or observational (1) or experimental treatment (0).';

CREATE TABLE "users" (
"id" int4 NOT NULL DEFAULT nextval('users_id_seq'::regclass),
"login" varchar(40) COLLATE "default" NOT NULL,
"name" varchar(100) COLLATE "default" NOT NULL,
"email" varchar(100) COLLATE "default" NOT NULL,
"city" varchar(255) COLLATE "default",
"country" varchar(255) COLLATE "default",
"area" varchar(255) COLLATE "default",
"crypted_password" varchar(40) COLLATE "default" NOT NULL,
"salt" varchar(40) COLLATE "default" NOT NULL,
"created_at" timestamp(6),
"updated_at" timestamp(6),
"remember_token" varchar(40) COLLATE "default",
"remember_token_expires_at" timestamp(6),
"access_level" int4 NOT NULL,
"page_access_level" int4 NOT NULL,
"apikey" varchar(255) COLLATE "default" NOT NULL,
"state_prov" varchar(255) COLLATE "default",
"postal_code" varchar(255) COLLATE "default",
CONSTRAINT "users_pkey" PRIMARY KEY ("id") ,
UNIQUE ("login"),
UNIQUE ("email"),
UNIQUE ("crypted_password"),
UNIQUE ("salt"),
UNIQUE ("apikey")
);

CREATE UNIQUE INDEX "index_users_on_login" ON "users" ("login" ASC);
COMMENT ON COLUMN "users"."login" IS 'login id';
COMMENT ON COLUMN "users"."name" IS 'User name';
COMMENT ON COLUMN "users"."email" IS 'email address';
COMMENT ON COLUMN "users"."access_level" IS 'data to which user has access';
COMMENT ON COLUMN "users"."page_access_level" IS 'Determines the extent of data, if any, that user can edit.';

CREATE TABLE "variables" (
"id" int4 NOT NULL DEFAULT nextval('variables_id_seq'::regclass),
"description" varchar(255) COLLATE "default",
"units" varchar(255) COLLATE "default" NOT NULL,
"notes" text COLLATE "default",
"created_at" timestamp(6),
"updated_at" timestamp(6),
"name" varchar(255) COLLATE "default" NOT NULL,
"max" varchar(255) COLLATE "default",
"min" varchar(255) COLLATE "default",
CONSTRAINT "variables_pkey" PRIMARY KEY ("id") ,
UNIQUE ("name")
);

COMMENT ON COLUMN "variables"."description" IS 'Description or definition of variable.';
COMMENT ON COLUMN "variables"."units" IS 'units in which data must be entered.';
COMMENT ON COLUMN "variables"."name" IS 'variable name, this is the name used by PEcAn and in other modeling contexts.';

CREATE TABLE "workflows" (
"id" int4 NOT NULL DEFAULT nextval('workflows_id_seq'::regclass),
"folder" varchar(255) COLLATE "default" NOT NULL,
"started_at" timestamp(6) NOT NULL,
"finished_at" timestamp(6) NOT NULL,
"created_at" timestamp(6),
"updated_at" timestamp(6),
"site_id" int4 NOT NULL,
"model_id" int4 NOT NULL,
"hostname" varchar(255) COLLATE "default" NOT NULL,
"params" text COLLATE "default" NOT NULL,
"advanced_edit" bool NOT NULL DEFAULT false,
"start_date" timestamp(6) NOT NULL,
"end_date" timestamp(6) NOT NULL,
CONSTRAINT "workflows_pkey" PRIMARY KEY ("id") ,
UNIQUE ("site_id", "model_id", "params", "advanced_edit", "start_date", "end_date")
);

CREATE TABLE "yields" (
"id" int4 NOT NULL DEFAULT nextval('yields_id_seq'::regclass),
"citation_id" int4 NOT NULL,
"site_id" int4,
"specie_id" int4 NOT NULL,
"treatment_id" int4 NOT NULL,
"cultivar_id" int4,
"date" date,
"dateloc" numeric(4,2),
"statname" varchar(255) COLLATE "default",
"stat" numeric(16,4),
"mean" numeric(16,4) NOT NULL,
"n" int4,
"notes" text COLLATE "default",
"created_at" timestamp(6),
"updated_at" timestamp(6),
"user_id" int4,
"checked" int4 NOT NULL,
"access_level" int4 NOT NULL,
"method_id" int4,
"entity_id" int4,
"date_year" int4,
"date_month" int4,
"date_day" int4,
CONSTRAINT "yields_pkey" PRIMARY KEY ("id") ,
UNIQUE ("citation_id", "site_id", "specie_id", "treatment_id", "cultivar_id", "method_id", "entity_id", "date_year", "date_month", "date_day")
);

CREATE INDEX "index_yields_on_citation_id" ON "yields" ("citation_id" ASC);
CREATE INDEX "index_yields_on_cultivar_id" ON "yields" ("cultivar_id" ASC);
CREATE INDEX "index_yields_on_method_id" ON "yields" ("method_id" ASC);
CREATE INDEX "index_yields_on_site_id" ON "yields" ("site_id" ASC);
CREATE INDEX "index_yields_on_specie_id" ON "yields" ("specie_id" ASC);
CREATE INDEX "index_yields_on_treatment_id" ON "yields" ("treatment_id" ASC);
CREATE INDEX "index_yields_on_user_id" ON "yields" ("user_id" ASC);
COMMENT ON COLUMN "yields"."citation_id" IS 'Citation in which data originally reported.';
COMMENT ON COLUMN "yields"."site_id" IS 'Site at which crop was harvested.';
COMMENT ON COLUMN "yields"."specie_id" IS 'Species for which yield was measured.';
COMMENT ON COLUMN "yields"."treatment_id" IS 'Experimental treatment identification. Required, can indicate observational study.';
COMMENT ON COLUMN "yields"."cultivar_id" IS 'Cultivar information, if any.';
COMMENT ON COLUMN "yields"."date" IS 'Date on which crop was harvested.';
COMMENT ON COLUMN "yields"."dateloc" IS 'Level of confidence in harvest date. See documentation.';
COMMENT ON COLUMN "yields"."statname" IS 'Name of reported statistic.';
COMMENT ON COLUMN "yields"."stat" IS 'Value of reported statistic.';
COMMENT ON COLUMN "yields"."mean" IS 'Mean yield reported. ';
COMMENT ON COLUMN "yields"."n" IS 'Number of replicates used to estimate mean and statistical summary.';
COMMENT ON COLUMN "yields"."user_id" IS 'ID of user who entered data.';
COMMENT ON COLUMN "yields"."checked" IS 'accepts values [-1, 0, 1]. 0 is default, and means that data have not been checked. 1 indicates that the data have been checked and are correct, -1 indicates that data have been checked and found to be incorrect or suspicious, e.g. outside of the acceptab';
COMMENT ON COLUMN "yields"."access_level" IS 'Level of access required to view data.';

CREATE TABLE "citations_treatments" (
"citation_id" int8 NOT NULL,
"treatment_id" int8 NOT NULL,
"created_at" timestamp(6),
"updated_at" timestamp(6),
UNIQUE ("citation_id", "treatment_id")
);

CREATE UNIQUE INDEX "index_citations_treatments_on_citation_id_and_treatment_id" ON "citations_treatments" ("citation_id" ASC, "treatment_id" ASC);


ALTER TABLE "traits" ADD CONSTRAINT "fk_traits_species_1" FOREIGN KEY ("specie_id") REFERENCES "species" ("id");
ALTER TABLE "yields" ADD CONSTRAINT "fk_yields_species_1" FOREIGN KEY ("specie_id") REFERENCES "species" ("id");
ALTER TABLE "traits" ADD CONSTRAINT "fk_traits_sites_1" FOREIGN KEY ("site_id") REFERENCES "sites" ("id");
ALTER TABLE "yields" ADD CONSTRAINT "fk_yields_sites_1" FOREIGN KEY ("site_id") REFERENCES "sites" ("id");
ALTER TABLE "traits" ADD CONSTRAINT "fk_traits_citations_1" FOREIGN KEY ("citation_id") REFERENCES "citations" ("id");
ALTER TABLE "yields" ADD CONSTRAINT "fk_yields_citations_1" FOREIGN KEY ("citation_id") REFERENCES "citations" ("id");
ALTER TABLE "traits" ADD CONSTRAINT "fk_traits_cultivars_1" FOREIGN KEY ("cultivar_id") REFERENCES "cultivars" ("id");
ALTER TABLE "yields" ADD CONSTRAINT "fk_yields_cultivars_1" FOREIGN KEY ("cultivar_id") REFERENCES "cultivars" ("id");
ALTER TABLE "traits" ADD CONSTRAINT "fk_traits_treatments_1" FOREIGN KEY ("treatment_id") REFERENCES "treatments" ("id");
ALTER TABLE "yields" ADD CONSTRAINT "fk_yields_treatments_1" FOREIGN KEY ("treatment_id") REFERENCES "treatments" ("id");
ALTER TABLE "treatments" ADD CONSTRAINT "fk_treatments_users_1" FOREIGN KEY ("user_id") REFERENCES "users" ("id");
ALTER TABLE "citations" ADD CONSTRAINT "fk_citations_users_1" FOREIGN KEY ("user_id") REFERENCES "users" ("id");
ALTER TABLE "yields" ADD CONSTRAINT "fk_yields_users_1" FOREIGN KEY ("user_id") REFERENCES "users" ("id");
ALTER TABLE "traits" ADD CONSTRAINT "fk_traits_users_1" FOREIGN KEY ("user_id") REFERENCES "users" ("id");
ALTER TABLE "yields" ADD CONSTRAINT "fk_yields_entities_1" FOREIGN KEY ("entity_id") REFERENCES "entities" ("id");
ALTER TABLE "traits" ADD CONSTRAINT "fk_traits_entities_1" FOREIGN KEY ("entity_id") REFERENCES "entities" ("id");
ALTER TABLE "yields" ADD CONSTRAINT "fk_yields_methods_1" FOREIGN KEY ("method_id") REFERENCES "methods" ("id");
ALTER TABLE "traits" ADD CONSTRAINT "fk_traits_methods_1" FOREIGN KEY ("method_id") REFERENCES "methods" ("id");
ALTER TABLE "cultivars" ADD CONSTRAINT "fk_cultivars_cultivars_1" FOREIGN KEY ("previous_id") REFERENCES "cultivars" ("id");
ALTER TABLE "cultivars" ADD CONSTRAINT "fk_cultivars_species_1" FOREIGN KEY ("specie_id") REFERENCES "species" ("id");
ALTER TABLE "sites" ADD CONSTRAINT "fk_sites_users_1" FOREIGN KEY ("user_id") REFERENCES "users" ("id");
ALTER TABLE "pfts_priors" ADD CONSTRAINT "fk_pfts_priors_pfts_1" FOREIGN KEY ("pft_id") REFERENCES "pfts" ("id");
ALTER TABLE "pfts_priors" ADD CONSTRAINT "fk_pfts_priors_priors_1" FOREIGN KEY ("prior_id") REFERENCES "priors" ("id");
ALTER TABLE "pfts_species" ADD CONSTRAINT "fk_pfts_species_pfts_1" FOREIGN KEY ("pft_id") REFERENCES "pfts" ("id");
ALTER TABLE "pfts_species" ADD CONSTRAINT "fk_pfts_species_species_1" FOREIGN KEY ("specie_id") REFERENCES "species" ("id");
ALTER TABLE "covariates" ADD CONSTRAINT "fk_covariates_traits_1" FOREIGN KEY ("trait_id") REFERENCES "traits" ("id");
ALTER TABLE "covariates" ADD CONSTRAINT "fk_covariates_variables_1" FOREIGN KEY ("variable_id") REFERENCES "variables" ("id");
ALTER TABLE "traits" ADD CONSTRAINT "fk_traits_variables_1" FOREIGN KEY ("variable_id") REFERENCES "variables" ("id");
ALTER TABLE "managements_treatments" ADD CONSTRAINT "fk_managements_treatments_managements_1" FOREIGN KEY ("management_id") REFERENCES "managements" ("id");
ALTER TABLE "managements_treatments" ADD CONSTRAINT "fk_managements_treatments_treatments_1" FOREIGN KEY ("treatment_id") REFERENCES "treatments" ("id");
ALTER TABLE "citations_sites" ADD CONSTRAINT "fk_citations_sites_citations_1" FOREIGN KEY ("citation_id") REFERENCES "citations" ("id");
ALTER TABLE "citations_sites" ADD CONSTRAINT "fk_citations_sites_sites_1" FOREIGN KEY ("site_id") REFERENCES "sites" ("id");
ALTER TABLE "inputs" ADD CONSTRAINT "fk_inputs_sites_1" FOREIGN KEY ("site_id") REFERENCES "sites" ("id");
ALTER TABLE "inputs" ADD CONSTRAINT "fk_inputs_users_1" FOREIGN KEY ("user_id") REFERENCES "users" ("id");
ALTER TABLE "inputs" ADD CONSTRAINT "fk_inputs_inputs_1" FOREIGN KEY ("parent_id") REFERENCES "inputs" ("id");
ALTER TABLE "inputs" ADD CONSTRAINT "fk_inputs_formats_1" FOREIGN KEY ("format_id") REFERENCES "formats" ("id");
ALTER TABLE "dbfiles" ADD CONSTRAINT "fk_dbfiles_users_1" FOREIGN KEY ("created_user_id") REFERENCES "users" ("id");
ALTER TABLE "dbfiles" ADD CONSTRAINT "fk_dbfiles_users_2" FOREIGN KEY ("updated_user_id") REFERENCES "users" ("id");
ALTER TABLE "dbfiles" ADD CONSTRAINT "fk_dbfiles_machines_1" FOREIGN KEY ("machine_id") REFERENCES "machines" ("id");
ALTER TABLE "inputs_variables" ADD CONSTRAINT "fk_inputs_variables_variables_1" FOREIGN KEY ("variable_id") REFERENCES "variables" ("id");
ALTER TABLE "inputs_variables" ADD CONSTRAINT "fk_inputs_variables_inputs_1" FOREIGN KEY ("input_id") REFERENCES "inputs" ("id");
ALTER TABLE "inputs_runs" ADD CONSTRAINT "fk_inputs_runs_inputs_1" FOREIGN KEY ("input_id") REFERENCES "inputs" ("id");
ALTER TABLE "inputs_runs" ADD CONSTRAINT "fk_inputs_runs_runs_1" FOREIGN KEY ("run_id") REFERENCES "runs" ("id");
ALTER TABLE "runs" ADD CONSTRAINT "fk_runs_models_1" FOREIGN KEY ("model_id") REFERENCES "models" ("id");
ALTER TABLE "models" ADD CONSTRAINT "fk_models_models_1" FOREIGN KEY ("parent_id") REFERENCES "models" ("id");
ALTER TABLE "posteriors_runs" ADD CONSTRAINT "fk_posteriors_runs_posteriors_1" FOREIGN KEY ("posterior_id") REFERENCES "posteriors" ("id");
ALTER TABLE "posteriors_runs" ADD CONSTRAINT "fk_posteriors_runs_runs_1" FOREIGN KEY ("run_id") REFERENCES "runs" ("id");
ALTER TABLE "formats_variables" ADD CONSTRAINT "fk_formats_variables_variables_1" FOREIGN KEY ("variable_id") REFERENCES "variables" ("id");
ALTER TABLE "likelihoods" ADD CONSTRAINT "fk_likelihoods_variables_1" FOREIGN KEY ("variable_id") REFERENCES "variables" ("id");
ALTER TABLE "likelihoods" ADD CONSTRAINT "fk_likelihoods_runs_1" FOREIGN KEY ("run_id") REFERENCES "runs" ("id");
ALTER TABLE "likelihoods" ADD CONSTRAINT "fk_likelihoods_inputs_1" FOREIGN KEY ("input_id") REFERENCES "inputs" ("id");
ALTER TABLE "posteriors" ADD CONSTRAINT "fk_posteriors_pfts_1" FOREIGN KEY ("pft_id") REFERENCES "pfts" ("id");
ALTER TABLE "posteriors" ADD CONSTRAINT "fk_posteriors_formats_1" FOREIGN KEY ("format_id") REFERENCES "formats" ("id");
ALTER TABLE "formats_variables" ADD CONSTRAINT "fk_formats_variables_formats_1" FOREIGN KEY ("format_id") REFERENCES "formats" ("id");
ALTER TABLE "ensembles" ADD CONSTRAINT "fk_ensembles_workflows_1" FOREIGN KEY ("workflow_id") REFERENCES "workflows" ("id");
ALTER TABLE "workflows" ADD CONSTRAINT "fk_workflows_models_1" FOREIGN KEY ("model_id") REFERENCES "models" ("id");
ALTER TABLE "workflows" ADD CONSTRAINT "fk_workflows_sites_1" FOREIGN KEY ("site_id") REFERENCES "sites" ("id");
ALTER TABLE "runs" ADD CONSTRAINT "fk_runs_sites_1" FOREIGN KEY ("site_id") REFERENCES "sites" ("id");
ALTER TABLE "runs" ADD CONSTRAINT "fk_runs_ensembles_1" FOREIGN KEY ("ensemble_id") REFERENCES "ensembles" ("id");
ALTER TABLE "priors" ADD CONSTRAINT "fk_priors_variables_1" FOREIGN KEY ("variable_id") REFERENCES "variables" ("id");
ALTER TABLE "priors" ADD CONSTRAINT "fk_priors_citations_1" FOREIGN KEY ("citation_id") REFERENCES "citations" ("id");
ALTER TABLE "formats" ADD CONSTRAINT "fk_formats_mimetypes_1" FOREIGN KEY ("mimetype_id") REFERENCES "mimetypes" ("id");
ALTER TABLE "entities" ADD CONSTRAINT "fk_entities_entities_1" FOREIGN KEY ("parent_id") REFERENCES "entities" ("id");
ALTER TABLE "citations_treatments" ADD CONSTRAINT "fk_citations_treatments_treatments_1" FOREIGN KEY ("treatment_id") REFERENCES "treatments" ("id");
ALTER TABLE "citations_treatments" ADD CONSTRAINT "fk_citations_treatments_citations_1" FOREIGN KEY ("citation_id") REFERENCES "citations" ("id");
ALTER TABLE "managements" ADD CONSTRAINT "fk_managements_citations_1" FOREIGN KEY ("citation_id") REFERENCES "citations" ("id");