CODEWITHSUNDEEP
sqlforeign-keysconstraints
Alejandro Gomez Guerrero
Alejandro Gomez Guerrero

Reputation: 398

How to limit amount of foreing key per record

I have 3 tables Title, Detail, TypeDetail, and I need to add the foreign key or rule, constraints, etc. How can I control the amount of Details per Title, it only can have detail of each type.

I tried with foreign keys.

My tables

Create table Title(
TitleID int identity(1,1) primary Key not null)

Create table Type(
TypeID int identity(1,1) primary Key not null)

Create table Detail(
DetailID int identity(1,1) primary Key not null,
TitleID int REFERENCES Title(TitleID),
TypeID int REFERENCES Type(TypeID))

Data in Title

TitleID
1
2

Data in Type

TypeID
1
2
3

Data in Detail

DetailID|TitleID|TypeID
1|1|1
2|1|2
3|1|3
4|2|1
5|2|2
6|1|1 // cant because I all ready inserted the Type 1 for Title 1

I want to make this restrction. Smoe help?

Upvotes: 0

Views: 109

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269823

I think you want a unique constraint:

create table Detail (
    DetailID int identity(1,1) primary Key not null,
    TitleID int REFERENCES Title (TitleID),
    TypeID int REFERENCES Type (TypeID),
    constraint unq_detail_title_type unique (Title, Type)
);

This will not allow duplicate pairs in the table.

Upvotes: 1

Related Questions