LinqPad Syntax for adding foreign key constraint

Question

This is the SQL I used in LinqPad:

CREATE TABLE Category 
(
    catID int NOT NULL,
    catName NVARCHAR(150) NOT NULL,
    catDesc NVARCHAR(150) NULL,
    catCount int NOT NULL ,
    CONSTRAINT category_pk PRIMARY KEY (catID)
);

CREATE TABLE InventoryTBLC 
(
    itemID int NOT NULL IDENTITY(1,1) CONSTRAINT PK_items PRIMARY KEY,
    itemName NVARCHAR(150) NOT NULL,
    itemDesc NVARCHAR(150) NULL,
    itemQuantity int,
    itemPrice int,
    imagePath NVARCHAR(300),
    correctInsert NVARCHAR(300),
    realImage image,

    CONSTRAINT FK_category FOREIGN KEY (catID) REFERENCES Category(catID)   
);

But I get the following error:

Invalid column ID. [ catID ]

Can someone help me with the LinqPad syntax?

score 1 · Answer 1 · answered Feb 14 '18 at 12:29

If you want to use a column as the foreign key, you have to list that column in your table definition first! Adding the foreign key constraint does NOT add that column to your table!

And this is in no way Linqpad specific - this is standard SQL behavior.

So either you need to add catId to your second table like this:

CREATE TABLE InventoryTBLC 
(
    itemID int NOT NULL IDENTITY(1,1) CONSTRAINT PK_items PRIMARY KEY,
    itemName NVARCHAR(150) NOT NULL,
    itemDesc NVARCHAR(150) NULL,
    itemQuantity int,
    itemPrice int,
    imagePath NVARCHAR(300),
    correctInsert NVARCHAR(300),
    realImage image,
    catID int NOT NULL,   -- this column *MUST* exist to be used as FK

    CONSTRAINT FK_category 
        FOREIGN KEY (catID) REFERENCES Category(catID)   
);

or then maybe you want to use one of the other pre-existing columns in InventoryTBLC as your FK column?

@deelliieeD: if this answer helped you solve your problem, then please [**accept this answer**](http://meta.stackoverflow.com/q/5234/153998). This will show your appreciation for the people who *spent their own time to help you*. — marc_s, Mar 03 '18 at 14:14

LinqPad Syntax for adding foreign key constraint

1 Answers1