November 1, 2012 at 7:36 am
If I have a table T1 with colA, ColB, ColC combined as primary key and another table T2 onant to define dependency but this table should be dependent on table T1 only on column ColB and not on ColA and ColC. How can I achieve this ? On creating foreign key constraint on T2 only for ColB, it says
"There are no primary or candidate keys in the referenced table T1 that match the referencing column list in the foreign key 'Foreign_Key_T1'."
Any suggestions ?
November 1, 2012 at 7:42 am
Either create a unique constraint on T1 ColB or add in ColA and ColC to T2
November 1, 2012 at 7:47 am
Thanks Anthony. Would it be advised to implement it through trigger ? I mean to check the dependency before any kind of insert, updte or delete operation.
November 1, 2012 at 7:48 am
The column in the referenced table must be unique, a foreign key defines a 1-n relationship, not an n-m. So if ColB is unique, you can do this (but if ColB is unique, why is the pk on 3 columns). If colB is not unique, the foreign key cannot be defined and it's likely that you're missing a table or have some other design problem.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 1, 2012 at 7:49 am
Dependencies should be enforced with foreign keys, not with triggers.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 1, 2012 at 7:51 am
No, if you want relationships they should be done via FK's, I would seriously go away and re-think the design on this one.
November 1, 2012 at 8:39 am
Great. Thanks. There was slight confusion. There is actually a unique non clustered key on my table T1 for ColA, ColB and ColC. Now I think I should create primary key first on T1 on column ColB and then FK with cascade on update and delete on T2.
Howzz that ?
November 1, 2012 at 8:41 am
Is there already a PK on the table? Are the values of ColB unique or is there a requirement going forward which means that you can have the same value in ColB going forward?
If the unique constraint is across A,B,C then the business logic must have defined that A,B,C is unique and that you can have duplicate values in the columns but not the rows.
November 1, 2012 at 8:59 am
Nope, there is no PK on the table. Even i assume going forward, there is also possibility of getting duplicate values in column ColB to the table T1.
What should be done in such scenario ? Table T2 contains only ColB from T1 along with it's own other columns. Now how can I deifne the relationship ? Creating any key like unique rowidentifier ?
November 1, 2012 at 9:03 am
sqlnaive (11/1/2012)
Even i assume going forward, there is also possibility of getting duplicate values in column ColB to the table T1.
Then you cannot define a foreign key and should probably revisit the design. A foreign key relationship is a 1-many relationship, one row in the parent table referencing 0 or more in the child. There cannot be multiple rows with the same value in the parent table (how would you know which one is the correct parent, which can or cannot be deleted, etc)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 1, 2012 at 9:03 am
Seriously go and redefine the design of the database.
Add A and C to T2 and then create a foreign key, or create some kind of identifier on T1 which is unique and then add the same identifier to T2 and foreign key it, or model the database correctly.
November 1, 2012 at 9:31 am
Thanks a lot Gail and Anthony. Looking forward to redesigning the tables and/or introducing new column with some unique identifier.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply