June 2, 2016 at 4:14 pm
I need a design suggestion from all you experts
So I have tables as below:
Table 1
( A integer not null
, B char(1) not null
, C integer not null
, D not null
, primary key (A, B, C,D) i.e. composite key
)
Table 2
( A integer not null
, B char(2) not null
, C integer not null
primary key (A, B,C) composite key
)
Table 3
( A integer not null
, B char(2) not null
, D not null
primary key (A, B, D)
)
I want to create a relationship between table 1 and 2 and Table 1 and 3.
Table 1 being the parent table and table 2 and 3 being the child table of Table 1. I can easily create relationship between table 1 and table 2 but I get an error when I try to create relationship between 1 and 3 because foreign key can not be the part of composite key and it has to be the whole composite key. I would highly appreciate any suggestions.
June 2, 2016 at 4:45 pm
You need to reexamine how you are creating your key structure. You can't have a primary key that consists of four columns and then a foreign key that consists of three. It has to be four. So, your options are, use the columns that make up the primary key, or, come up with another design.
Are the four columns necessary to create a unique constraint on the table? Can you identify a row with only A,B,C or A,B,D? If so, you can create two different unique constraints, one for each of those three column combinations and then use those constraints in a foreign key relationship. If those don't uniquely identify the row, then you can't use them as foreign keys anyway.
Another way to do it is to create an artificial key, an identity column is common. Then you can use that key as the foreign key constraint, but, that will change the other tables structures as well. Plus, you'll still want to have a unique constraint on the original four columns if they uniquely identify the row.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply