CODEWITHSUNDEEP
sqlsql-server
Maroun Nasra
Maroun Nasra

Reputation: 3

Add Foreign Key that is part of primary key in the reference table?

How is it possible to add Foreign Key when the primary key in the reference table is two fields. In the ERD diagram, that was given by the lecturer, it shows that the two fields (maker & model ) from the table product are both act as primary key. In the other hand in the DSD diagram it shows that the field model from the table "PC" is the primary key of this table and also its a foreign key that references to field model from the table product.

When trying to execute the code below in SQL server it brings error:

Msg 1774, Level 16, State 0, Line 10 The number of columns in the referencing column list for foreign key 'FK__PC__model__66603565' does not match those of the primary key in the referenced table 'Product'. Msg 1750, Level 16, State 1, Line 10 Could not create constraint or index. See previous errors.

create table Product(
maker nvarchar(50) not null , 
model int not null ,
type nvarchar(50) not null,
CONSTRAINT PK_MAKER_MODEL PRIMARY KEY (maker,model),
CONSTRAINT CH_TYPE  check(type in ('PC','LAPTOP','PRINTER'))
)

-------------------------------------------------------------
create table PC(
model int not null ,
speed nvarchar(50),
ram nvarchar(50),
hd nvarchar(50),
price dec(12,2),
FOREIGN KEY ( model) REFERENCES Product
ON DELETE NO ACTION
)

DSD DIAGRAM ERD DIAGRAM

Upvotes: 0

Views: 682

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

You do not understand how foreign keys work. They need to reference the entire primary key. For this reason, I prefer single-column foreign keys. Something more like this:

create table Products (
    productId int identity(1, 1) primary key,
    maker nvarchar(50) not null, 
    model int not null,
    type nvarchar(50) not null,
    constraint unq_products_maker_model unique (maker, model),
    constraint chk_products_typecheck (type in ('PC', 'LAPTOP', 'PRINTER'))
);

create table PC (
    pcId int identity(1, 1) primary key,
    productId int not null,
    speed nvarchar(50),
    ram nvarchar(50),
    hd nvarchar(50),
    price dec(12,2),
    foreign key (productId) references Products(productId) on delete no action
);

Upvotes: 1

Related Questions