January 31, 2012 at 4:10 pm
I have a table that is studentHomeRoom history table. It gets data from other sources, it doesn't a Primary key, but it has a country code in it, I am pretty sure the country code in this refers to a domain table that called Country_Code, so i would like to add a foreign key to the column in the homeroomHistory table which somehow like a staging table.
But I would like to know if it is ok to add a forgin key to a table that doesn't have a prirmy key.
Thanks
January 31, 2012 at 8:10 pm
For a foreign key to be created you must have a constraint that forces uniqueness on some column(s). This could be a unique primary key or a unique constraint.
Without a declared unique constraint of some form, you will not be able to create the foreign key
January 31, 2012 at 11:29 pm
happycat59 (1/31/2012)
For a foreign key to be created you must have a constraint that forces uniqueness on some column(s). This could be a unique primary key or a unique constraint.Without a declared unique constraint of some form, you will not be able to create the foreign key
Doesn't have the uniqueness constraint have to be in the Country_Code table, and not in the studentHomeRoom history table?
@sqlfriends: why doesn't the studentHomeRoom history table have a primary key? You are violating first normal form, which can lead to data inconsistency.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 1, 2012 at 5:12 am
Yes, the unique contraint would need to be on the Country table - no information supplied on the definition of that table so I gave a generic reply
There is a suggestion that studentHomeRoom history table is a staging table. As far as I concerned, a staging table may contain data that requires cleansing and may contain data that violates all of the accepted integrity rules. Once cleansed (and loaded into another table), you would expect primary keys etc.
February 1, 2012 at 5:14 am
happycat59 (2/1/2012)
Yes, the unique contraint would need to be on the Country table - no information supplied on the definition of that table so I gave a generic replyThere is a suggestion that studentHomeRoom history table is a staging table. As far as I concerned, a staging table may contain data that requires cleansing and may contain data that violates all of the accepted integrity rules. Once cleansed (and loaded into another table), you would expect primary keys etc.
If it's a staging table, which doesn't need a primary key, then it also doesn't need foreign keys 🙂
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply