Foreign key referencing primary key of multiple values

Question

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) )

So, in MemberBooking, an int, you're somehow storing a char(1) and two ints in order to reference Block, Lvl, rNum? — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Mar 7, 2019 at 6:57
So memberbooking should be what variable type instead? Thanks — Ralph
– Ralph, Commented Mar 7, 2019 at 7:05

Mukesh Arora · Accepted Answer · 2019-03-07 06:53:13Z

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

forpas · Accepted Answer · 2019-03-07 08:13:16Z

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

Understood, able to rewrite the expression in this case? Thanks
You must reference only 1 column and since Block is a different data type than Memberbooking you can do this: CONSTRAINT FK_Booking FOREIGN KEY (Memberbooking) references room (Lvl) or CONSTRAINT FK_Booking FOREIGN KEY (Memberbooking) references room (rNum)

George Menoutis · Accepted Answer · 2019-03-07 08:05:01Z

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?

Gert-Jan · Accepted Answer · 2019-03-07 12:07:25Z

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.

Collectives™ on Stack Overflow

Foreign key referencing primary key of multiple values

4 Answers 4

Comments

2 Comments

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

2 Comments

1 Comment

Comments

Related