Unique Foreign Key Integrity Constraint?
in [SAS]
|
Prev: Hostname
Next: Automatic Email using SAS
From: Gerhard Hellriegel on 9 Feb 2010 14:06 in DI-Studio yes, in base-SAS not automaticly. There you must implement it in application logic, I think. Gerhard On Tue, 9 Feb 2010 12:35:42 -0600, Craig Johnson <cjohns38(a)GMAIL.COM> wrote: >Is it possible to set a foreign key integrity constraint as unique? The FK >I'm using appears to be a one-to-many relationship when I'd rather it be a >one-to-one. Is there a way to mimic a one-to-one PK to FK constrained >relationship in SAS? Thoughts?
From: Mike Rhoads on 9 Feb 2010 15:38 Hmmm -- could you use the UNIQUE constraint on the column, as well as having a foreign key constraint? 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 1:36 PM To: SAS-L(a)LISTSERV.UGA.EDU Subject: Unique Foreign Key Integrity Constraint? Is it possible to set a foreign key integrity constraint as unique? The FK I'm using appears to be a one-to-many relationship when I'd rather it be a one-to-one. Is there a way to mimic a one-to-one PK to FK constrained relationship in SAS? Thoughts?
From: Craig Johnson on 9 Feb 2010 15:51 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: Craig Johnson on 9 Feb 2010 16:27 I've ran across that several times now. The problem is that works if you are dealing with two variables but I'm dealing with one, a participant unique identifier. All I want to be able to do is link the master list of ID's with the entry and make sure it's in the master list and it hasn't already been entered.
From: Craig Johnson on 9 Feb 2010 16:24
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; > |