CODEWITHSUNDEEP
sqlsql-serverforeign-keys
Ralph
Ralph

Reputation: 43

Foreign key referencing primary key of multiple values

Im using ms sql server. I get this msg when I reference a primary key that is a composite key of 3 values in the foreign key.

"number of referencing columns in foreign key differs from the number of reference columns". The problem lies in second last line of code in member booking. Any ideas? Thanks in advance.

CREATE TABLE room
(
Block CHAR (1),
Lvl INT,
rNum INT,
RmType VARCHAR (15),
Condition VARCHAR (15),
CONSTRAINT room_PK PRIMARY KEY (Block, Lvl, rNum),
)




CREATE TABLE booking
(
BookingID INT,
BStartDate DATE,
BEndDate DATE,
Fee DECIMAL (8,2) NOT NULL CHECK (fee >= 0),
Memberbooking INT NOT NULL,
MemberID INT NOT NULL,
CONSTRAINT booking_pk PRIMARY KEY (BookingID),
CONSTRAINT FK_Booking FOREIGN KEY (Memberbooking) references room (Block, Lvl, rNum),
CONSTRAINT FK_MemberID FOREIGN KEY (MemberID) references member (ID)
)

Upvotes: 1

Views: 2134

Answers (4)

Gert-Jan
Gert-Jan

Reputation: 347

On table "room", you currently have a compound natural key. This is a valid design decision. The alternative would be to add a single column artificial id, such as an int Identity. That would lead to two keys on the same table (one of them Primary Key, the other one Unique constraint).

In a foreign key constraint, you can reference any Unique constraint or Primary Key. If you want to keep the "room" table the way it is, then you need to mirror those key fields in the referencing table. So that would mean your "booking" table would need a Block char(1), Lvl int and rNum int column.

That is why an artificial key (on room) can be useful, because then your foreign key constraint (on booking) can be single column and reference that Unique constaint.

Upvotes: 0

forpas
forpas

Reputation: 164064

You define this constraint in the table booking:

CONSTRAINT FK_Booking FOREIGN KEY (Memberbooking) references room (Block, Lvl, rNum)

meaning that you want the column Memberbooking to reference 3 columns (!!) in the table room.
Each column from a table can reference one column from another table, not multiple ones.
You can define the same column to reference more than 1 columns in another table, but with different constraints, and always 1 to 1.
Read more here: Create Foreign Key Relationships

Upvotes: 1

George Menoutis
George Menoutis

Reputation: 7240

Since I see from the comments you are intent to implement this type of check, I would propose a check constraint:

CONSTRAINT CK_MemberID CHECK (EXISTS(SELECT 1 FROM room where Memberbooking=room.Block+convert(nchar,room.Lvl)+convert(nchar,room.rNum)))

However, this design is not very good. The lack of seperators in the Membermooking field might cause collisions.

Eg, consider you have a Memberbooking: 'A1101'

Is that Block A, Lvl 11, rNum 01? Or is it Block A, Lvl 1, rNum 101?

Upvotes: 0

Mukesh Arora
Mukesh Arora

Reputation: 1813

You are getting an error because you are trying to map 1 column (FOREIGN KEY (Memberbooking)) with 3 columns room (Block, Lvl, rNum)

it's possible to create a foreign key relationship to a compound (more than one column) primary key, make sure to specify the same number of columns in your FOREIGN KEY

Upvotes: 1

Related Questions