CODEWITHSUNDEEP
sqlsql-server
Sirinà Strong
Sirinà Strong

Reputation: 67

Reservation table query

when I execute my reservation table query, I get an error.

Any help?

This is my reservation table query 

CREATE TABLE RESERVATION 
(
    NUMCHAMBRE INT FOREIGN KEY REFERENCES CHAMBRE (NUMCHAMBRE) ,
    NUMHOTEL INT FOREIGN KEY REFERENCES HOTEL (NUMHOTEL),
    NUMCLIENT INT FOREIGN KEY  REFERENCES CLIENT (NUMCLIENT),
    DATEARRIVE DATE,
    DATEDEPART DATE, 

    PRIMARY KEY (NUMHOTEL, NUMCLIENT, DATEARRIVE)
);

This is the error I get:

Msg 1776, Level 16, State 0, Line 2
There are no primary or candidate keys in the referenced table 'CHAMBRE' that match the referencing column list in the foreign key 'FK__RESERVATI__NUMCH__2BFE89A6'.

Msg 1750, Level 16, State 0, Line 2
Could not create constraint or index. See previous errors.

Upvotes: 0

Views: 273

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

According to your comment, the primary key on chambre is composite. So the foreign key reference needs to include all the columns:

CREATE TABLE RESERVATION (
    NUMCHAMBRE int,
    NUMHOTEL int Foreign Key REFERENCES HOTEL (NUMHOTEL),
    NUMCLIENT int Foreign Key  REFERENCES CLIENT (NUMCLIENT),
    DATEARRIVE date,
    DATEDEPART date, 
    foreign key (numhotel, numchambre) references chambre (numhotel, numchambre);
)

Upvotes: 2

muhmud
muhmud

Reputation: 4604

The column NUMCHAMBRE you're referencing in table CHAMBRE must be a primary key column, i.e. you can't just reference any column.

You can declare it as the primary key like this:

alter table CHAMBRE add primary key (NUMCHAMBRE);

Primary key columns need to be not null, so if NUMCHAMBRE is nullable, the above command will fail.

Update:

Based on your comment below, your table definition should be like this, i.e. you need to reference both columns of the primary key:

CREATE TABLE RESERVATION (
NUMCHAMBRE int,
NUMHOTEL int not null Foreign Key REFERENCES HOTEL (NUMHOTEL),
NUMCLIENT int not null Foreign Key  REFERENCES CLIENT (NUMCLIENT),
DATEARRIVE date not null,
DATEDEPART date, 

PRIMARY KEY (NUMHOTEL,NUMCLIENT,DATEARRIVE),
Foreign Key (NUMCHAMBRE,NUMHOTEL) REFERENCES CHAMBRE (NUMCHAMBRE,NUMHOTEL)
);

Note the additional not null constraints, as these will be necessary in order to create the primary key on this table using SQL Server.

Upvotes: 0

Related Questions