Referentail Integrity

Question

Referentail Integrity

pathankhan

SSC Enthusiast

Points: 167
More actions
April 12, 2012 at 7:44 am

#254667

Can someone please help me out setting up primary and foreign key relationships. Here is my issue...
Lookup_Country Table list all the countries.
1. USA
2. Canada
3. China
4. India
Person_Information Table
Birth_Country_ID
Citizenship_Country_ID
Home_Country_ID
I can create only one foreign key constraint from Lookup_Country to Person_Information table but I have three columns that I need to setup referential integrity for... Is there any way to set primary and foreign key relationship for all three columns in Person_Information table?

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

CapnHector SSCoach Points: 16143 More actions · Answer 1

pathankhan (4/12/2012)
Can someone please help me out setting up primary and foreign key relationships. Here is my issue...
Lookup_Country Table list all the countries.
1. USA
2. Canada
3. China
4. India
Person_Information Table
Birth_Country_ID
Citizenship_Country_ID
Home_Country_ID
I can create only one foreign key constraint from Lookup_Country to Person_Information table but I have three columns that I need to setup referential integrity for... Is there any way to set primary and foreign key relationship for all three columns in Person_Information table?

what do you mean that you can only create one foreign key constraint. im guessing this is either homework or an interview question. we can help you get to the answer but wont answer it outright for you.

For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]

Sean Lange SSC Guru Points: 286609 More actions · Answer 2

Yes you can have more than 1 foreign key constraint on a table.

Here is an example of creating one.

alter table MyTable

add constraint MyTable_MyColumn_FK FOREIGN KEY ( MyColumn ) references SomeOtherTable(ForeignKeyColumn)

I would recommend looking at your Lookup_Country table. Does it have only 1 column with the full country name? You should consider using ISO country codes instead. Holy cow I am starting to sound like CELKO.

_______________________________________________________________

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/

drew.allen SSC Guru Points: 77014 More actions · Answer 3

pathankhan (4/12/2012)
I can create only one foreign key constraint from Lookup_Country to Person_Information table but I have three columns that I need to setup referential integrity for... Is there any way to set primary and foreign key relationship for all three columns in Person_Information table?

It sounds like you are doing it backwards. The foreign key constraint needs to be defined on the Person_Information table referencing the Lookup_Country table, not the other way around.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

pathankhan SSC Enthusiast Points: 167 More actions · Answer 4

Hi,

Thanks all for replying.. Its not an interview question.

Primary Key Table = Lookup_Country

Foreign Key Table = Person_Information

My question is that Can I create the following 3 keys referering to the Person_Information table 3 times?

ALTER TABLE Lookup_Country ADD CONSTRAINT fk_Person_LookupCountry

FOREIGN KEY (country_Id) REFERENCES Person_Information(birth_country_Id)

ALTER TABLE Lookup_Country ADD CONSTRAINT fk_Person_LookupCountry

FOREIGN KEY (country_Id) REFERENCES Person_Information(citizenship_country_Id)

ALTER TABLE Lookup_Country ADD CONSTRAINT fk_Person_LookupCountry

FOREIGN KEY (country_Id) REFERENCES Person_Information(home_country_Id)

Gazareth One Orange Chip Points: 27737 More actions · Answer 5

As Drew says, you've got it backwards.

Swap the table & field names round in the SQL you've posted & it should be good.

Think Foreign Key REFERENCES Primary Key 🙂

CapnHector SSCoach Points: 16143 More actions · Answer 6

as Gazareth said the foreign key references the primary key (the home_country_id references Country_ID) i would also read up on foreign key relationships to make sure you under stant them.

For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]

pathankhan SSC Enthusiast Points: 167 More actions · Answer 7

Really, I was setting it up the opposite way.

So 3 foreign keys on Person_Information table to the same primary key Lookup_Country table will run perfectly fine.

ALTER TABLE Person_Information ADD CONSTRAINT fk_Person_LookupCountry

FOREIGN KEY (birth_country_Id) REFERENCES Lookup_Country(country_Id)

ALTER TABLE Person_Information ADD CONSTRAINT fk_Person_LookupCountry

FOREIGN KEY (citizenship_country_Id) REFERENCES Lookup_Country(country_Id)

ALTER TABLE Person_Information ADD CONSTRAINT fk_Person_LookupCountry

FOREIGN KEY (home_country_Id) REFERENCES Lookup_Country(country_Id)

CapnHector SSCoach Points: 16143 More actions · Answer 8

pathankhan (4/12/2012)
Really, I was setting it up the opposite way.
So 3 foreign keys on Person_Information table to the same primary key Lookup_Country table will run perfectly fine.
ALTER TABLE Person_Information ADD CONSTRAINT fk_Person_LookupCountry
FOREIGN KEY (birth_country_Id) REFERENCES Lookup_Country(country_Id)
ALTER TABLE Person_Information ADD CONSTRAINT fk_Person_LookupCountry
FOREIGN KEY (citizenship_country_Id) REFERENCES Lookup_Country(country_Id)
ALTER TABLE Person_Information ADD CONSTRAINT fk_Person_LookupCountry
FOREIGN KEY (home_country_Id) REFERENCES Lookup_Country(country_Id)

yes thats how you do it. you can reference the primary key in a table as a foreign key in any number of tables (or columns in one table)

For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]

Sean Lange SSC Guru Points: 286609 More actions · Answer 9

But each constraint must have a unique name.

_______________________________________________________________

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/

CapnHector SSCoach Points: 16143 More actions · Answer 10

Sean Lange (4/12/2012)
But each constraint must have a unique name.

good catch sean, im getting ready for work and posting. apparently not a good combination.

For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]

Gazareth One Orange Chip Points: 27737 More actions · Answer 11

capn.hector (4/12/2012)
Sean Lange (4/12/2012)
But each constraint must have a unique name.
good catch sean

Ditto, good catch!

Also, not applicable here, but good to remember/learn - you can reference unique keys in other tables, not just the primary key.

drew.allen SSC Guru Points: 77014 More actions · Answer 12

Gazareth (4/12/2012)
Think Foreign Key REFERENCES Primary Key 🙂

That's not quite accurate. The foreign key only needs to reference a unique key, it does not need to be the primary key, although that is the most typical situation.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Gazareth One Orange Chip Points: 27737 More actions · Answer 13

drew.allen (4/12/2012)
Gazareth (4/12/2012)
Think Foreign Key REFERENCES Primary Key 🙂
That's not quite accurate. The foreign key only needs to reference a unique key, it does not need to be the primary key, although that is the most typical situation.
Drew

Gazareth (4/12/2012)
Also, not applicable here, but good to remember/learn - you can reference unique keys in other tables, not just the primary key.

Indeed 😉

pathankhan SSC Enthusiast Points: 167 More actions · Answer 14

Thanks all of you for the kind support.

I will get back to you once I will test these relationships.