diff options
Diffstat (limited to 'migrations')
3 files changed, 14 insertions, 2 deletions
diff --git a/migrations/auth/20230322_02_Ll854-create-phenotype-resources-table.py b/migrations/auth/20230322_02_Ll854-create-phenotype-resources-table.py index 19ee885..7c9e986 100644 --- a/migrations/auth/20230322_02_Ll854-create-phenotype-resources-table.py +++ b/migrations/auth/20230322_02_Ll854-create-phenotype-resources-table.py @@ -12,10 +12,14 @@ steps = [ CREATE TABLE IF NOT EXISTS phenotype_resources -- Link phenotype data to specific resources ( + group_id TEXT NOT NULL, resource_id TEXT NOT NULL, -- A resource can have multiple data items data_link_id TEXT NOT NULL, - PRIMARY KEY(resource_id, data_link_id), + PRIMARY KEY(group_id, resource_id, data_link_id), UNIQUE (data_link_id), -- ensure data is linked to only one resource + FOREIGN KEY (group_id, resource_id) + REFERENCES resources(group_id, resource_id) + ON UPDATE CASCADE ON DELETE RESTRICT, FOREIGN KEY (data_link_id) REFERENCES linked_phenotype_data(data_link_id) ON UPDATE CASCADE ON DELETE RESTRICT diff --git a/migrations/auth/20230404_02_la33P-create-genotype-resources-table.py b/migrations/auth/20230404_02_la33P-create-genotype-resources-table.py index e967ce6..1a865e0 100644 --- a/migrations/auth/20230404_02_la33P-create-genotype-resources-table.py +++ b/migrations/auth/20230404_02_la33P-create-genotype-resources-table.py @@ -12,10 +12,14 @@ steps = [ CREATE TABLE IF NOT EXISTS genotype_resources -- Link genotype data to specific resource ( + group_id TEXT NOT NULL, resource_id TEXT NOT NULL, -- A resource can have multiple items data_link_id TEXT NOT NULL, - PRIMARY KEY (resource_id, data_link_id), + PRIMARY KEY (group_id, resource_id, data_link_id), UNIQUE (data_link_id) -- ensure data is linked to single resource + FOREIGN KEY (group_id, resource_id) + REFERENCES resources(group_id, resource_id) + ON UPDATE CASCADE ON DELETE RESTRICT, FOREIGN KEY (data_link_id) REFERENCES linked_genotype_data(data_link_id) ON UPDATE CASCADE ON DELETE RESTRICT diff --git a/migrations/auth/20230410_02_WZqSf-create-mrna-resources-table.py b/migrations/auth/20230410_02_WZqSf-create-mrna-resources-table.py index de348c0..2ad1056 100644 --- a/migrations/auth/20230410_02_WZqSf-create-mrna-resources-table.py +++ b/migrations/auth/20230410_02_WZqSf-create-mrna-resources-table.py @@ -12,10 +12,14 @@ steps = [ CREATE TABLE IF NOT EXISTS mrna_resources -- Link mRNA data to specific resource ( + group_id TEXT NOT NULL, resource_id TEXT NOT NULL, -- A resource can have multiple items data_link_id TEXT NOT NULL, PRIMARY KEY (resource_id, data_link_id), UNIQUE (data_link_id) -- ensure data is linked to single resource + FOREIGN KEY (group_id, resource_id) + REFERENCES resources(group_id, resource_id) + ON UPDATE CASCADE ON DELETE RESTRICT, FOREIGN KEY (data_link_id) REFERENCES linked_mrna_data(data_link_id) ON UPDATE CASCADE ON DELETE RESTRICT ) WITHOUT ROWID |