Unique Foreign Key Integrity Constraint?
in [SAS]
|
Prev: Hostname
Next: Automatic Email using SAS
From: Randy Herbison on 9 Feb 2010 16:24 This seems to work, but it uses 2 variables for the constraints. Switching the order of the variables specified for the (2nd) constraint definition seems to do the trick: 409 proc datasets library=work nolist; 410 modify Singers_0001; 411 ic create primary key (FirstName LastName); NOTE: Integrity constraint _PK0001_ defined. NOTE: MODIFY was successful for WORK.SINGERS_0001.DATA. 412 modify Singers_0002; 413 ic create foreign key (FirstName LastName) references Singers_0001 414 on delete restrict on update restrict; NOTE: Integrity constraint _FK0001_ defined. NOTE: MODIFY was successful for WORK.SINGERS_0002.DATA. 415 modify Singers_0002; 416 ic create unique ( LastName FirstName); NOTE: Integrity constraint _UN0001_ defined. 417 quit; -----Original Message----- From: SAS(r) Discussion [mailto:SAS-L(a)LISTSERV.UGA.EDU] On Behalf Of Craig Johnson Sent: Tuesday, February 09, 2010 3:52 PM To: SAS-L(a)LISTSERV.UGA.EDU Subject: Re: Unique Foreign Key Integrity Constraint? I've tried to put to IC's on a single variable and it throws an error that basically says you can't two indices on a single variable. ============ Code ============ PROC datasets library=mdsveri nolist; modify ZKPQ; ic create unique (motherid); ic create foreign key (motherid) references IDLists.Temp_ZKPQ; quit; ============ Error ============ 108 PROC datasets library=mdsveri nolist; 109 modify ZKPQ; 110 ic create unique (motherid); NOTE: Integrity constraint _UN0001_ defined. 111 ic create foreign key (motherid) references IDLists.Temp_ZKPQ; ERROR: An index named MotherID with the same definition but different characteristics exists for file MDSVERI.ZKPQ.DATA. 112 quit;
From: Mike Rhoads on 9 Feb 2010 16:08 Phooey! Giving each of the constraints a separate name doesn't help either ... Mike Rhoads RhoadsM1(a)Westat.com -----Original Message----- From: SAS(r) Discussion [mailto:SAS-L(a)LISTSERV.UGA.EDU] On Behalf Of Craig Johnson Sent: Tuesday, February 09, 2010 3:52 PM To: SAS-L(a)LISTSERV.UGA.EDU Subject: Re: Unique Foreign Key Integrity Constraint? I've tried to put to IC's on a single variable and it throws an error that basically says you can't two indices on a single variable. ============ Code ============ PROC datasets library=mdsveri nolist; modify ZKPQ; ic create unique (motherid); ic create foreign key (motherid) references IDLists.Temp_ZKPQ; quit; ============ Error ============ 108 PROC datasets library=mdsveri nolist; 109 modify ZKPQ; 110 ic create unique (motherid); NOTE: Integrity constraint _UN0001_ defined. 111 ic create foreign key (motherid) references IDLists.Temp_ZKPQ; ERROR: An index named MotherID with the same definition but different characteristics exists for file MDSVERI.ZKPQ.DATA. 112 quit;
From: xlr82sas on 9 Feb 2010 19:38 On Feb 9, 1:24 pm, RandyHerbi...(a)WESTAT.COM (Randy Herbison) wrote: > This seems to work, but it uses 2 variables for the constraints. Switching the order of the variables specified for the (2nd) constraint definition seems to do the trick: > > 409 proc datasets library=work nolist; > 410 modify Singers_0001; > 411 ic create primary key (FirstName LastName); > NOTE: Integrity constraint _PK0001_ defined. > NOTE: MODIFY was successful for WORK.SINGERS_0001.DATA. > 412 modify Singers_0002; > 413 ic create foreign key (FirstName LastName) references Singers_0001 > 414 on delete restrict on update restrict; > NOTE: Integrity constraint _FK0001_ defined. > NOTE: MODIFY was successful for WORK.SINGERS_0002.DATA. > 415 modify Singers_0002; > 416 ic create unique ( LastName FirstName); > NOTE: Integrity constraint _UN0001_ defined. > 417 quit; > > > > -----Original Message----- > From: SAS(r) Discussion [mailto:SA...(a)LISTSERV.UGA.EDU] On Behalf Of Craig Johnson > Sent: Tuesday, February 09, 2010 3:52 PM > To: SA...(a)LISTSERV.UGA.EDU > Subject: Re: Unique Foreign Key Integrity Constraint? > > I've tried to put to IC's on a single variable and it throws an error that > basically says you can't two indices on a single variable. > > ============ > Code > ============ > PROC datasets library=mdsveri nolist; > modify ZKPQ; > ic create unique (motherid); > ic create foreign key (motherid) references IDLists.Temp_ZKPQ; > quit; > > ============ > Error > ============ > 108 PROC datasets library=mdsveri nolist; > 109 modify ZKPQ; > 110 ic create unique (motherid); > NOTE: Integrity constraint _UN0001_ defined. > 111 ic create foreign key (motherid) references IDLists.Temp_ZKPQ; > ERROR: An index named MotherID with the same definition but different > characteristics exists for > file MDSVERI.ZKPQ.DATA. > 112 quit;- Hide quoted text - > > - Show quoted text - Hi No luck with SQL either data fact; set sashelp.retail; * pk year month ; run; data dimension; set sashelp.retail; * pk year month ; run; /* useful for reruns */ proc sql; alter table fact drop foreign key _fk0001_,_pk0001_; alter table dimension drop foreign key _fk0001_,_pk0001_; quit; /* SAME ERROR AS DATASETS */ proc sql; alter table dimension add primary key (date); alter table fact add primary key (date) add foreign key (date) references dimension; ;quit; As mentioned above this works proc sql; alter table fact drop foreign key _fk0001_,_pk0001_; alter table dimension drop foreign key _fk0001_,_pk0001_; quit; proc sql; alter table dimension add primary key (year,month); alter table fact add foreign key (year,month) references dimension add primary key (month,year) ; ;quit;
From: Sigurd Hermansen on 9 Feb 2010 22:36
Craig: Not so odd if you think it through .... A foreign key constraint assures referential integrity; that is, if a value of a FK exists, then the same value of the primary key (PK) exists (and continues to exist) on the table to which the FK refers, and all of the tuples with the FK belong to the entity identified by the PK. If the FK and the PK have a 1:1 relationship, that means that combining two relations by linking on the same key value preserves all functional dependencies. In other words, two tables with the same primary (distinct) key essentially amount to an alternative view of a single relation. This situation doesn't change materially when a foreign key in a table has a 1:1 relation to the primary key in that table; e.g., SSN (PK),DriversLicenseID (FK), ... --> DriversLicenseID (PK), ... Assuming a 1:1 relation between SSN and DriversLicenseID, the two keys would be "candidate keys": they could switch PK and FK roles. But that merely suggests that the usual reasons for referential integrity constraints don't hold in this situation ... Why would defining primary and foreign key constraints on the same attribute generate an error? Because the foreign key constraint belongs on the other table.... It requires, for example, that a primary key value exist on the primary table prior to an insert of a tuple with that foreign key value on the other table, and that so long as that value exists in the foreign key on the other table, nothing can delete or change the corresponding primary key value in the primary table. On the other hand, the primary table may have a primary key value that doesn't correspond to a foreign key value on the other table. The foreign key constraint applies to the primary table only when a change in the primary table would affect its relation to the other table. S -----Original Message----- From: SAS(r) Discussion [mailto:SAS-L(a)LISTSERV.UGA.EDU] On Behalf Of Craig Johnson Sent: Tuesday, February 09, 2010 4:24 PM To: SAS-L(a)LISTSERV.UGA.EDU Subject: Re: Unique Foreign Key Integrity Constraint? It just seems kind of odd to be able to set unique values but not unique FK's. :( On Tue, Feb 9, 2010 at 3:08 PM, Mike Rhoads <RHOADSM1(a)westat.com> wrote: > Phooey! Giving each of the constraints a separate name doesn't help either > ... > > > Mike Rhoads > RhoadsM1(a)Westat.com > > > > -----Original Message----- > From: SAS(r) Discussion [mailto:SAS-L(a)LISTSERV.UGA.EDU] On Behalf Of Craig > Johnson > Sent: Tuesday, February 09, 2010 3:52 PM > To: SAS-L(a)LISTSERV.UGA.EDU > Subject: Re: Unique Foreign Key Integrity Constraint? > > I've tried to put to IC's on a single variable and it throws an error that > basically says you can't two indices on a single variable. > > ============ > Code > ============ > PROC datasets library=mdsveri nolist; > modify ZKPQ; > ic create unique (motherid); > ic create foreign key (motherid) references IDLists.Temp_ZKPQ; quit; > > ============ > Error > ============ > 108 PROC datasets library=mdsveri nolist; > 109 modify ZKPQ; > 110 ic create unique (motherid); > NOTE: Integrity constraint _UN0001_ defined. > 111 ic create foreign key (motherid) references IDLists.Temp_ZKPQ; > ERROR: An index named MotherID with the same definition but different > characteristics exists for > file MDSVERI.ZKPQ.DATA. > 112 quit; > |