SQL Server - foreign key reference error

Question

I have two tables, Table_1 with 4 columns (3 primary key) and Table_2 with 2 column. When I try to create a foreign key constraint in Table_2, I am getting this error:

enter image description here

Here are the definitions of my tables:

Table_1

CREATE TABLE [dbo].[Table_1]
(
    [Field_1] [tinyint] NOT NULL,
    [Field_2] [tinyint] NOT NULL,
    [Field_3] [tinyint] NOT NULL,
    [Field_4] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED 
(
    [Field_1] ASC,
    [Field_2] ASC,
    [Field_3] ASC
)

Table_2

CREATE TABLE [dbo].[Table_2]
(
    [Field_1] [tinyint] NOT NULL,
    [Field_2] [tinyint] NOT NULL
) ON [PRIMARY]

Do you have any idea on how to solve this? Thanks -

`Field_1` is not a primary key. You should either make it a primary key through the visual thingy or change the script for `Table_1` to `[Field_1] [tinyint] PRIMARY KEY`, — Lieven Keersmaekers, Apr 27 '12 at 10:24
Hi Lieven, I'm sorry I forgot to add the primary keys on my sample table. Anyway, I have updated my table, now with primary keys defined. But I'm still getting the same error. — neztreh, Apr 27 '12 at 10:35
For SQL Server to be able to create a foreign key, it has to be sure that there can only be **one** record returned for a given foreign key. As you have a primary key on multiple fields, there's no guarantee that `Field_1` will be unique. If you can't change your primary key, all you can do is add a unique index on `Table_1.Field_1`. If a unique index is also not possible, you can't create a foreign key. — Lieven Keersmaekers, Apr 27 '12 at 10:41

score 2 · Accepted Answer · answered Apr 27 '12 at 10:41

The primary key of Table_1 is all three of Field_1, Field_2 and Field_3. In order to reference a key in another table, you have to reference all of the columns in that key - so you would need to add Field_2 and Field_3 to Table_2, and have all three columns included when you attempt to create the foreign key constraint.

Alternatively, if Field_1, by itself, is a key for Table_1, then declare it as such, either by redefining the primary key, or adding a UNIQUE constraint on just Field_1 to Table_1. You would then be able to create the foreign key you're attempting to.

Adding a UNIQUE constraint solves the problem. Thanks Damien! — neztreh, Apr 27 '12 at 13:11

SQL Server - foreign key reference error

1 Answers1