CODEWITHSUNDEEP
sqlsql-server
Stabbah
Stabbah

Reputation: 73

SQL Server Foreign Key

I'm trying to create this database with the following relations in SQL Server and I get this error:

Msg 1776, Level 16, State 0, Line 11
There are no primary or candidate keys in the referenced table 'Consumable' that match the referencing column list in the foreign key 'FK_Recipe_Ingredie__59FA5E80'.
Msg 1750, Level 16, State 0, Line 11
Could not create constraint. See previous errors.

What am I doing wrong?

Here's my code;

CREATE TABLE Consumable
(
    c_ID int NOT NULL,
    Name varchar(32) NOT NULL,
    Amount int,
    Unit varchar(8) NOT NULL CHECK (unit IN ('ml', 'g', 'pieces', 'unknown')),
    CONSTRAINT PK_Consumable PRIMARY KEY (c_ID, Name)
)

CREATE TABLE Recipe
(
    Name varchar(64) PRIMARY KEY NOT NULL,
    Type varchar(32),
    Description varchar(512),
    IngredientsID int NOT NULL FOREIGN KEY REFERENCES Consumable(c_ID) ON DELETE CASCADE
)

CREATE TABLE Kitchen
(
    K_ID int PRIMARY KEY NOT NULL IDENTITY,
    IngredientsID int FOREIGN KEY REFERENCES Consumable(c_ID) ON DELETE CASCADE
)

Upvotes: 1

Views: 239

Answers (2)

HLGEM
HLGEM

Reputation: 96552

Your Pk is a composite PK and so c_id is not necessarily unique and thus cannot be used in an FK relationship.

You have several choices depending that you had. If you will not have multiple of c_id in the consumables table than you can create a unique index on it and you should be able to create the Fk. BUt in that case really why are you using a compositer OK? The other choice you have in this situation is to make just the c_id the PK and then put a unique index on Name. Never use a composite PK if you don't have to.

If the C_id will into be unique in the Consumable table, then the only choice you have is to add the name column to the Other tables. You would of course have to give it a different name in Recipe since it has a different name.

Ingredients_id doesn't make sense in the recipe table as you are going to have more than one ingredient in a recipe. You should have a child table that contains recipe ingredients.

And On Delete Cascade is a poor choice as well. Very bad thing to do to a nice innocent database. Do you really want to delete the recipes you are out of an ingredient? I don't think so.

Upvotes: 1

marc_s
marc_s

Reputation: 754268

Read the error message!

It's pretty clear: the foreign key you're trying to set up from Recipe.IngredientsID doesn't reference the primary key of your target table (Consumable - primary key is (c_ID, Name) - not just c_ID ....)

To fix this: you must reference the whole compound PK on your target table (e.g. you must have both columns of PK_Consumable in your child table in order to reference it

CREATE TABLE Recipe
(
    Name varchar(64) PRIMARY KEY NOT NULL,
    Type varchar(32),
    Description varchar(512),
    IngredientsID int NOT NULL,
    IngredientsName varchar(32) NOT NULL,

    CONSTRAINT FK_Recipe_Consumable 
    FOREIGN KEY (IngredientsID, IngredientsName) 
    REFERENCES Consumable(c_ID, Name) ON DELETE CASCADE
)

Any foreign key can only ever reference the WHOLE primary key of a parent table - or a unique constraint (again: all columns involved in that unique constraint). You cannot just simply refer to an arbitrary column (or set of columns) in your parent table.

Upvotes: 1

Related Questions