November 19, 2015 at 7:15 am
I have an odd ball situation...
Table A has a 3 part compound key. (GroupID, PayorID, EffectiveDate)
Table B need a check foreign key constraint that references only GroupID & PayorID.
Of course, the following syntax generates the following error:
ALTER TABLE dbo.TableB WITH CHECK ADD CONSTRAINT fk_TableB_GroupID_PayorID FOREIGN KEY (GroupID, PayorID)
REFERENCES dbo.TableA (GroupID, PayorID)
ON UPDATE CASCADE
ON DELETE CASCADE;
GO
Msg 1776, Level 16, State 0, Line 22
There are no primary or candidate keys in the referenced table 'dbo.TableA' that match the referencing column list in the foreign key 'fk_TableB_GroupID_PayorID'.
Msg 1750, Level 16, State 0, Line 22
Could not create constraint or index. See previous errors.
I "feel" like this is something I should know how to get around but I'm drawing a blank...
A push in the right direction would be greatly appreciated.
Thanks in advance,
Jason
November 19, 2015 at 7:23 am
If you create a unique index on GroupID & PayorID, does that fix it?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
November 19, 2015 at 7:34 am
Is there no need or use for a table with the two fields as the primary key? On the face of it, it's more than an odd situation. My assumption would be that something isn't right. It could be even that the FK is unnecessary in this context.
November 19, 2015 at 7:48 am
Phil you sir, are a gentleman and a scholar!!! Thank you!
I had dismissed the idea of a unique index simply because those two columns alone aren't unique... For whatever reason, your suggestion reminded me that I can create a unique index with IGNORE_DUP_KEY = ON...
It worked! and the FK constraint is created.
Thank you again
November 19, 2015 at 7:57 am
RonKyle (11/19/2015)
Is there no need or use for a table with the two fields as the primary key? On the face of it, it's more than an odd situation. My assumption would be that something isn't right. It could be even that the FK is unnecessary in this context.
On the contrary. A composite key is very common and has many perfectly valid uses. Consider an intermediate table that normalizes a many to many relationship. This is the most common place for a composite key and from the table posted in this question is very likely what the table it.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 19, 2015 at 8:02 am
On the contrary. A composite key is very common and has many perfectly valid uses.
I was not commenting on the validity of a composite key. They are of course indispensable unless you are going to use auto ids for everything. That in my opinion is not good design.
I was wondering why the two fields didn't validly make their own table with only those two fields as the primary key. I can't recall that I've ever had a join where the FK referenced only part of the primary key. If the two fields can be made unique, then the next question would seem to be why they shouldn't be the primary key.
November 19, 2015 at 8:05 am
RonKyle (11/19/2015)
Is there no need or use for a table with the two fields as the primary key? On the face of it, it's more than an odd situation. My assumption would be that something isn't right. It could be even that the FK is unnecessary in this context.
This is kind of an odd-ball situation. GroupID & PayorID, would under normal circumstance, be the natural key for TableA... But there is a requirement that these have temporal "windows" of being valid...
For example: The combination of GroupID=3 & PayorID=576 bay be valid from 2013-01-01 to 2014-06-31 and again from 2015-01-01 to current...
Thus the need to include the EffectiveDate.
TableB introduces a 2nd, optional, dependency on some other criteria. It is therefore important to use DRI to make sure that a given GroupID/PayorID combo exists in TableA before allowing it into TableB.
November 19, 2015 at 8:06 am
The inclusion of the effective date in your key indicates that TableA is tracking some entity over time. If this is the case, then the entity will be in that table multiple times with different effective dates. This means the entity itself will not be unique in that table, and is therefore ineligible for a FOREIGN KEY, because a FOREIGN KEY candidate has to be unique. TableA is a bridge table between your entity and time. Your FOREIGN KEY needs to be set up on the table for the entity itself, not to the bridge table.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
November 19, 2015 at 8:06 am
Sean Lange (11/19/2015)
RonKyle (11/19/2015)
Is there no need or use for a table with the two fields as the primary key? On the face of it, it's more than an odd situation. My assumption would be that something isn't right. It could be even that the FK is unnecessary in this context.On the contrary. A composite key is very common and has many perfectly valid uses. Consider an intermediate table that normalizes a many to many relationship. This is the most common place for a composite key and from the table posted in this question is very likely what the table it.
You're spot on Sean. 🙂
November 19, 2015 at 8:11 am
drew.allen (11/19/2015)
The inclusion of the effective date in your key indicates that TableA is tracking some entity over time. If this is the case, then the entity will be in that table multiple times with different effective dates. This means the entity itself will not be unique in that table, and is therefore ineligible for a FOREIGN KEY, because a FOREIGN KEY candidate has to be unique. TableA is a bridge table between your entity and time. Your FOREIGN KEY needs to be set up on the table for the entity itself, not to the bridge table.Drew
I had considered that but the values in TableB (for a given G/P combo) apply to ALL instances of that same combo in TableA. Splitting the FK to point at the respective base tables doesn't insure that a given G/P combination actually exists in TableA.
The following index did the trick...
CREATE UNIQUE NONCLUSTERED INDEX uix_TableA_GroupID_PayorID ON dbo.TableA (
GroupID ASC,
PayorID ASC
)
WITH (IGNORE_DUP_KEY = ON, DATA_COMPRESSION = PAGE, FILLFACTOR = 100);
GO
November 19, 2015 at 8:18 am
This is kind of an odd-ball situation. GroupID & PayorID, would under normal circumstance, be the natural key for TableA... But there is a requirement that these have temporal "windows" of being valid...
For example: The combination of GroupID=3 & PayorID=576 bay be valid from 2013-01-01 to 2014-06-31 and again from 2015-01-01 to current...
Thus the need to include the EffectiveDate.
TableB introduces a 2nd, optional, dependency on some other criteria. It is therefore important to use DRI to make sure that a given GroupID/PayorID combo exists in TableA before allowing it into TableB.
I'm not sure in this case that I would set up a FK. Almost certainly would not set it up in this way. The solution you've adopted is interesting and can't be ruled out. And we should all be open to different ways. But I would need to think more about it before I adopted it.
November 19, 2015 at 8:41 am
Jason A. Long (11/19/2015)
drew.allen (11/19/2015)
The inclusion of the effective date in your key indicates that TableA is tracking some entity over time. If this is the case, then the entity will be in that table multiple times with different effective dates. This means the entity itself will not be unique in that table, and is therefore ineligible for a FOREIGN KEY, because a FOREIGN KEY candidate has to be unique. TableA is a bridge table between your entity and time. Your FOREIGN KEY needs to be set up on the table for the entity itself, not to the bridge table.Drew
I had considered that but the values in TableB (for a given G/P combo) apply to ALL instances of that same combo in TableA. Splitting the FK to point at the respective base tables doesn't insure that a given G/P combination actually exists in TableA.
The following index did the trick...
CREATE UNIQUE NONCLUSTERED INDEX uix_TableA_GroupID_PayorID ON dbo.TableA (
GroupID ASC,
PayorID ASC
)
WITH (IGNORE_DUP_KEY = ON, DATA_COMPRESSION = PAGE, FILLFACTOR = 100);
GO
And all of that points to that table not being normalized properly. The effective dates should be in a separate table. Of course, it's hard to go into detail given the limited amount of information we have to work with.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
November 19, 2015 at 9:27 am
drew.allen (11/19/2015)
Jason A. Long (11/19/2015)
drew.allen (11/19/2015)
The inclusion of the effective date in your key indicates that TableA is tracking some entity over time. If this is the case, then the entity will be in that table multiple times with different effective dates. This means the entity itself will not be unique in that table, and is therefore ineligible for a FOREIGN KEY, because a FOREIGN KEY candidate has to be unique. TableA is a bridge table between your entity and time. Your FOREIGN KEY needs to be set up on the table for the entity itself, not to the bridge table.Drew
I had considered that but the values in TableB (for a given G/P combo) apply to ALL instances of that same combo in TableA. Splitting the FK to point at the respective base tables doesn't insure that a given G/P combination actually exists in TableA.
The following index did the trick...
CREATE UNIQUE NONCLUSTERED INDEX uix_TableA_GroupID_PayorID ON dbo.TableA (
GroupID ASC,
PayorID ASC
)
WITH (IGNORE_DUP_KEY = ON, DATA_COMPRESSION = PAGE, FILLFACTOR = 100);
GO
And all of that points to that table not being normalized properly. The effective dates should be in a separate table. Of course, it's hard to go into detail given the limited amount of information we have to work with.
Drew
I don't want this to come across as argumentative but what's not normalized about it?
For TableA, each combination of GroupID, PayorID & EffectiveDate is a unique entity, all having a common set of attributes, each tuple (row) having it's own unique set of attribute values.
TableB is the result of proper normalization... The set of attributes in TableB apply to GroupID & PayorID ... Not GroupID, PayorID & EffectiveDate, and were therefore "normalized" out into a separate table to eliminate redundancy and guarantee consistency.
November 19, 2015 at 9:46 am
Jason A. Long (11/19/2015)
drew.allen (11/19/2015)
Jason A. Long (11/19/2015)
drew.allen (11/19/2015)
The inclusion of the effective date in your key indicates that TableA is tracking some entity over time. If this is the case, then the entity will be in that table multiple times with different effective dates. This means the entity itself will not be unique in that table, and is therefore ineligible for a FOREIGN KEY, because a FOREIGN KEY candidate has to be unique. TableA is a bridge table between your entity and time. Your FOREIGN KEY needs to be set up on the table for the entity itself, not to the bridge table.Drew
I had considered that but the values in TableB (for a given G/P combo) apply to ALL instances of that same combo in TableA. Splitting the FK to point at the respective base tables doesn't insure that a given G/P combination actually exists in TableA.
The following index did the trick...
CREATE UNIQUE NONCLUSTERED INDEX uix_TableA_GroupID_PayorID ON dbo.TableA (
GroupID ASC,
PayorID ASC
)
WITH (IGNORE_DUP_KEY = ON, DATA_COMPRESSION = PAGE, FILLFACTOR = 100);
GO
And all of that points to that table not being normalized properly. The effective dates should be in a separate table. Of course, it's hard to go into detail given the limited amount of information we have to work with.
Drew
I don't want this to come across as argumentative but what's not normalized about it?
For TableA, each combination of GroupID, PayorID & EffectiveDate is a unique entity, all having a common set of attributes, each tuple (row) having it's own unique set of attribute values.
TableB is the result of proper normalization... The set of attributes in TableB apply to GroupID & PayorID ... Not GroupID, PayorID & EffectiveDate, and were therefore "normalized" out into a separate table to eliminate redundancy and guarantee consistency.
It violates Second Normal Form in that the foreign key is NOT dependent on the whole of the primary key. The fact that you had to specify IGNORE_DUP_KEY in order to create a "unique" constraint is further evidence that you are violating Second Normal Form.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
November 19, 2015 at 9:55 am
It violates Second Normal Form in that the foreign key is NOT dependent on the whole of the primary key. The fact that you had to specify IGNORE_DUP_KEY in order to create a "unique" constraint is further evidence that you are violating Second Normal Form.
It's been a few years since I've studied the normal forms. But I used to know them inside and out and I don't think they apply to anything outside their table. I do agree that something doesn't seem right. But not enough information is known to us.
Is this an OLAP table? It reminds me of a 2SCD dimension table. But as with all dimension tables, it has an auto integer key.
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply