CODEWITHSUNDEEP
sql
seeker
seeker

Reputation: 6991

Can I create a composite foreign key in the following way?

I have a table A that contains the following columns

ID(PK)  id_1   
1         4
2         10
3         15
4         4

Now Im trying to create a table B with columns such that 

ID(PK) Description   id_1_a_id (composite foreign key(ID,id_1))  
1        Apple         (1,4)
2        Orange         (2,10)
3        Banana         (3,15)
4        dog             (4,4)
5

Does this design make sense? Or is there a better way to do this? (SQL rookie)

Upvotes: 0

Views: 32

Answers (1)

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95542

Composite foreign keys are common and useful, but you don't have one. If you did, it would look like this.

ID(PK) Description     A_id     id_1
--
1        Apple         1        4
2        Orange        2        10
3        Banana        3        15
4        dog           4        4

But you wouldn't ordinarily do that, either. Ordinarily, you'd reference a unique set of columns in table A. The unique set of columns is just the single column A.ID. So your table would usually look like this.

ID(PK) Description     A_id
--
1        Apple         1   
2        Orange        2   
3        Banana        3   
4        dog           4

You wouldn't usually duplicate the values of A.id_1 in table B. If you need the values from A.id_1, write a query with a JOIN.

select B.ID, B.Description, A.ID, A.id_1
from B
inner join A on A.ID = B.A_id;

If the only unique constraint you have in your tables is on the ID numbers, you're doing it wrong. But that's a different question.

Upvotes: 1

Related Questions