Is it possible to make a foreign key without the field being a primary key?

Question

For example

create table Equipment (

Equipmenttype nvarchar(1,
Description nvarchar(10),
Primary Key (Equipmenttype)
)

Create table Room (

RoomID nvarchar(8),
Capacity numeric(3),
Roomtype(fk,nvarchar(2)

)

I want to create the following table...

create table RoomEquipment(
(RoomID here)
(Equipmenttype here)

but Equipmenttype is not a primary key.

No. You can't create a foreign key to just any column. That doesn't make any logical sense. And of course in your example you state that EquipmentType is the primary key. — Sean Lange, Nov 06 '15 at 14:49
If you want to do that then you have found a flaw in your database design. — Tomalak, Nov 06 '15 at 14:50
No pk needed, an alternate key (unique constraint) should work as well. — jarlh, Nov 06 '15 at 14:51
any table should contain a PK, already because of the performance of your query's — WiiMaxx, Nov 06 '15 at 15:10

score 2 · Answer 1 · answered Nov 06 '15 at 14:52

2

You can create foreign key on columns that are PK or Unuque:

So you have to create a unique index on RoomID:

CREATE UNIQUE INDEX UI_Room_RoomID ON dbo.Room(RoomID);

Now you can create a foreign key in table RoomEquipment.

answered Nov 06 '15 at 14:52

Giorgi Nakeuri

35,155
8
47
75

score 2 · Answer 2 · edited May 23 '17 at 10:28

2

A FOREIGN KEY constraint does not have to be linked only to a PRIMARY KEY constraint in another table; it can also be defined to reference the columns of a UNIQUE constraint in another table.

Source: Foreign Key to non-primary key

edited May 23 '17 at 10:28

Community

1
1

answered Nov 06 '15 at 14:59

Adish

709
4
12

score 0 · Answer 3 · answered Nov 06 '15 at 14:50

0

Equipmenttype is a primary key, in its own table Equipment, and RoomID should be a primary key for Room.

So RoomEquipment is a standard many-to-many relationship table.

answered Nov 06 '15 at 14:50

Lorenzo Gatti

1,260
1
10
15

Is it possible to make a foreign key without the field being a primary key?

3 Answers3