Primary Key and Foriegn Key

Question

Post reply

Primary Key and Foriegn Key

rohaandba

Mr or Mrs. 500

Points: 585
More actions
January 20, 2011 at 1:10 pm

#230694

I have a 'Root' table with 8 columns. First 5 columns are to be declared as a Primary key(Composite).
And I have 10 to 15 tables, each with a Foriegn key referencing to this 'Root' table's Composite Primary key.
I am not able to do that as it says that the number of columns in the Primary key doesn't match with the Columns of the Foriegn key.
Can I solve this scenario or do I need to change the structure of the tables
Please help me

Viewing 5 posts - 1 through 4 (of 4 total)

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

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 1

As you notices, a foreign key can only refer to the full primary key.

Can you implement a surrogate key ?

(and create a unique constraint for your current primary key constraint)

Think about the consequences for your queries !

You'll find some articles and other forum threads at SSC !

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

rohaandba Mr or Mrs. 500 Points: 585 More actions · Answer 2

Actually I would explain in a better way

I have a 'Root' table with 8 columns.

First 5 columns in 'Root table ' are used in all 10 child tables of that root table.

I first wanted to make the first 5 columns of the 'root' table as Composite Primary key ,

But the data thats going to come into the 5 columns will not be unique (like a column may have same hospital name with different patient numbers starting from 1,2,3----n in the patient column, When the hospital name changes in the Hospital column then the patient number again starts from 1,2,3----n ).

Note: Both the 'Hospiatal Name' & 'Patient number' are 2 columns among the 5 columns of the root table.

So as per my requirement ,Client wants to use all the first 5 columns of the root table in other 10 child tables also.

I am not able to do that as per the nomalization criteria.

Can anyone please suggest me how to design.

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 3

A primary key _MUST_ be unique !

none of the columns can be NULLable and the combination of all columns must be unique.

Can you elaborate a bit on your "normalization criteria" ?

Why wouldn't you be able to put the data design in at least 3-th normal form ?