Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
rohaandba
Starting Member
33 Posts |
 Posted - 2011-01-24 : 12:30:29
|
| 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(Only Single Column) 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 tablesPlease help meedit: moved to appropriate forum |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-01-24 : 12:37:08
|
| its not possible as you cant guarantee that any one of columns out of 5 columns will be by itself unique. only composite combination of 5 columns be unique in Root. you might have to implement this using trigger or check constraint if you want this functionality though.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-01-24 : 12:48:29
|
| A foreign key has to reference a unique index.Are you sure about what you are trying to do?For each of these columns you could create a table with the distinct values.This could then be use as the reference column in the foreign keys. Then you have the problem about keeping these tables up to date. You should probably insert into the single column table before the main table and create an fk from the main table. The two FKs will enforce the constraint you are trying to create.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
rohaandba
Starting Member
33 Posts |
Posted - 2011-01-25 : 09:25:55
|
| Actually I would explain in a better wayI 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. |
|
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2011-01-26 : 14:20:00
|
| Maybe you could give a few examples of the child tables and how they should relate to the root table. Could you just use and Indentity Column as a Primary key on the first table?Maybe it sounds like your client is insisting on something that will violate normalization if so you may need to convince him or her why that is not a good idea. |
|
|
|
rohaandba
Starting Member
33 Posts |
Posted - 2011-01-27 : 09:27:20
|
| If I use 'an Indentity Column as a Primary key on the first table (Root Table)'. Then I think all the child tables should refer to that Identity column of the root table. Please correct me if I am wrong.If not then do I need to create an Indentity column in all the child tables so that they refer to the Primary key(Identity Column) of the root table. |
|
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2011-01-27 : 10:01:55
|
| Usually, you want to identify your many-to-ones. And keep in mind children can also have children.It's kind of Hard to say without knowing your business but here is an example:Lets say the Patient stays at a Hospital and during each Stay, the patient is visited by a Doctor several times. Then During each visit, many tests are performed.HospitalStays--------------ID - PK/identityHospitalID FKPatientID FK...DoctorVisits------------ID - PK/identityHospitalStayID FK to HospitalStays.IDDoctorID FK...DoctorVisitTests----------------ID - PK/identityDoctorVisitID FK to DoctorVisits.IDTestNameTestReading... |
|
|
|
|
|
|
|
|
Primary Key (Composite) & Foriegn Key