FK in DataSetAttributeAnnotation references wrong columns in DataSetAttributes

[atn38]

lines 81-82 in https://github.com/lter/LTER-core-metabase/blob/migration/sql/33_semantic_annotation.sql establishes a foreign key "fk_SAA_DataSetID"

CREATE TABLE semantic_annotation."DataSetAttributeAnnotation"
(
  "DataSetID"   integer,
  "EntitySortOrder" integer,
  "ColumnPosition"  int, -- alternatively, AttributeName.
  "TermID"      character varying(40),
  "ObjectPropertyID"    character varying(200) DEFAULT 'containsMeasurementsOfType',
  CONSTRAINT "pk_DataSetAttributeAnnotation_DataSetID_TermID" PRIMARY KEY ("DataSetID","EntitySortOrder","ColumnPosition","TermID"),
  CONSTRAINT "fk_SAA_DataSetID" FOREIGN KEY ("DataSetID","EntitySortOrder","ColumnPosition") REFERENCES lter_metabase."DataSetAttributes" ("DataSetID","EntitySortOrder","ColumnPosition")
  MATCH SIMPLE ON UPDATE CASCADE ON DELETE NO ACTION,
  CONSTRAINT "fk_SAA_TermID" FOREIGN KEY ("TermID") REFERENCES semantic_annotation."EMLSemanticAnnotationTerms" ("TermID")
  MATCH SIMPLE ON UPDATE CASCADE ON DELETE NO ACTION,
  CONSTRAINT "fk_SAA_ObjPropID" FOREIGN KEY ("ObjectPropertyID") REFERENCES semantic_annotation."EMLObjectProperties" ("ObjectPropertyID")
  MATCH SIMPLE ON UPDATE CASCADE ON DELETE NO ACTION
);

however, DSAttributes itself uses ColumnName as part of its PK, thus rendering above FK invalid

solution1: change PK in DSAttributes to use ColumnPosition
effects: need to change the same in the enumeration and missing codes tables and their corresponding views

solution2: change PK in DSAttributeAnnotation to use ColumnName
effects: need to change its corresponding view

[gastil]

No change needed. I added a unique constraint specifically to avoid that issue. Did my unique not get installed? Also no need to propagate a bad choice of key column. Key Column for one FK need not be same as for other FKs.

…

On Monday, November 25, 2019, An T. Nguyen ***@***.***> wrote: lines 81-82 in https://github.com/lter/LTER-core-metabase/blob/migration/ sql/33_semantic_annotation.sql establishes a foreign key "fk_SAA_DataSetID" CREATE TABLE semantic_annotation."DataSetAttributeAnnotation" ( "DataSetID" integer, "EntitySortOrder" integer, "ColumnPosition" int, -- alternatively, AttributeName. "TermID" character varying(40), "ObjectPropertyID" character varying(200) DEFAULT 'containsMeasurementsOfType', CONSTRAINT "pk_DataSetAttributeAnnotation_DataSetID_TermID" PRIMARY KEY ("DataSetID","EntitySortOrder","ColumnPosition","TermID"), CONSTRAINT "fk_SAA_DataSetID" FOREIGN KEY ("DataSetID","EntitySortOrder","ColumnPosition") REFERENCES lter_metabase."DataSetAttributes" ("DataSetID","EntitySortOrder","ColumnPosition") MATCH SIMPLE ON UPDATE CASCADE ON DELETE NO ACTION, CONSTRAINT "fk_SAA_TermID" FOREIGN KEY ("TermID") REFERENCES semantic_annotation."EMLSemanticAnnotationTerms" ("TermID") MATCH SIMPLE ON UPDATE CASCADE ON DELETE NO ACTION, CONSTRAINT "fk_SAA_ObjPropID" FOREIGN KEY ("ObjectPropertyID") REFERENCES semantic_annotation."EMLObjectProperties" ("ObjectPropertyID") MATCH SIMPLE ON UPDATE CASCADE ON DELETE NO ACTION ); however, DSAttributes itself uses ColumnName as part of its PK, thus rendering above FK invalid solution1: change PK in DSAttributes to use ColumnPosition effects: need to change the same in the enumeration and missing codes tables and their corresponding views solution2: change PK in DSAttributeAnnotation to use ColumnName effects: need to change its corresponding view — You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub <#93?email_source=notifications&email_token=AGTM6FSSNS3ZFRUZJZ5UVGDQVQK5FA5CNFSM4JRNA3H2YY3PNVWWK3TUL52HS4DFUVEXG43VMWVGG33NNVSW45C7NFSM4H34UVRA>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AGTM6FTWRRVBODLQUK4BZKLQVQK5FANCNFSM4JRNA3HQ> .

[atn38]

Ah, you are right. There's a UNIQUE constraint in the same source file. Not sure how I missed that. False alarm.

…

On Mon, Nov 25, 2019 at 12:38 PM Gastil ***@***.***> wrote: No change needed. I added a unique constraint specifically to avoid that issue. Did my unique not get installed? Also no need to propagate a bad choice of key column. Key Column for one FK need not be same as for other FKs. On Monday, November 25, 2019, An T. Nguyen ***@***.***> wrote: > lines 81-82 in https://github.com/lter/LTER-core-metabase/blob/migration/ > sql/33_semantic_annotation.sql establishes a foreign key > "fk_SAA_DataSetID" > > CREATE TABLE semantic_annotation."DataSetAttributeAnnotation" > ( > "DataSetID" integer, > "EntitySortOrder" integer, > "ColumnPosition" int, -- alternatively, AttributeName. > "TermID" character varying(40), > "ObjectPropertyID" character varying(200) DEFAULT 'containsMeasurementsOfType', > CONSTRAINT "pk_DataSetAttributeAnnotation_DataSetID_TermID" PRIMARY KEY ("DataSetID","EntitySortOrder","ColumnPosition","TermID"), > CONSTRAINT "fk_SAA_DataSetID" FOREIGN KEY ("DataSetID","EntitySortOrder","ColumnPosition") REFERENCES lter_metabase."DataSetAttributes" ("DataSetID","EntitySortOrder","ColumnPosition") > MATCH SIMPLE ON UPDATE CASCADE ON DELETE NO ACTION, > CONSTRAINT "fk_SAA_TermID" FOREIGN KEY ("TermID") REFERENCES semantic_annotation."EMLSemanticAnnotationTerms" ("TermID") > MATCH SIMPLE ON UPDATE CASCADE ON DELETE NO ACTION, > CONSTRAINT "fk_SAA_ObjPropID" FOREIGN KEY ("ObjectPropertyID") REFERENCES semantic_annotation."EMLObjectProperties" ("ObjectPropertyID") > MATCH SIMPLE ON UPDATE CASCADE ON DELETE NO ACTION > ); > > however, DSAttributes itself uses ColumnName as part of its PK, thus > rendering above FK invalid > > solution1: change PK in DSAttributes to use ColumnPosition > effects: need to change the same in the enumeration and missing codes > tables and their corresponding views > > solution2: change PK in DSAttributeAnnotation to use ColumnName > effects: need to change its corresponding view > > — > You are receiving this because you are subscribed to this thread. > Reply to this email directly, view it on GitHub > < #93?email_source=notifications&email_token=AGTM6FSSNS3ZFRUZJZ5UVGDQVQK5FA5CNFSM4JRNA3H2YY3PNVWWK3TUL52HS4DFUVEXG43VMWVGG33NNVSW45C7NFSM4H34UVRA >, > or unsubscribe > < https://github.com/notifications/unsubscribe-auth/AGTM6FTWRRVBODLQUK4BZKLQVQK5FANCNFSM4JRNA3HQ > > . > — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub <#93?email_source=notifications&email_token=AKAZD5V2JNGFAXORJVQ62ETQVQLSJA5CNFSM4JRNA3H2YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOEFDMELQ#issuecomment-558285358>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AKAZD5TQQFGKOKU773H7MQLQVQLSJANCNFSM4JRNA3HQ> .