0

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

Maroun Nasra
  • 3
  • 2
  • 1
    Possible duplicate of [Foreign key referencing a 2 columns primary key in SQL Server](https://stackoverflow.com/questions/3178709/foreign-key-referencing-a-2-columns-primary-key-in-sql-server) – Xedni Apr 01 '18 at 19:13

1 Answers1

1

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
);
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Does this solution gonna be accepted if the given ERD diagram shows that the two fields ( maker & model) from the table products act as a primary key of the table? – Maroun Nasra Apr 01 '18 at 19:06
  • @MarounNasra . . . I prefer a synthetic key. The two columns together are a candidate primary key and by declaring them `not null` and `unique` you get essentially the same behavior. – Gordon Linoff Apr 01 '18 at 19:20
  • I prefer this too .. I think I will do this it's much easier and understandable. Thank you for your time – Maroun Nasra Apr 01 '18 at 19:28