Foreign Key Error(length issues)

Question

Here is my code.

CREATE TABLE CATEGORY 
(
    CategoryNo int IDENTITY(1,1) NOT NULL,
    AutoIncCategoryNo AS 'CAT' + RIGHT('00000' + CAST(CategoryNo AS VARCHAR(5)),5) PERSISTED,
    CategoryName varchar(100) NOT NULL,
    PRIMARY KEY (AutoIncCategoryNo)
);

CREATE TABLE PRODUCT 
(
    ProductNo int IDENTITY(1,1) NOT NULL,
    AutoIncProduct AS 'P' + RIGHT('00000' + CAST(ProductNo AS VARCHAR(5)),5) PERSISTED,
    ProductName varchar(100) NOT NULL,
    CategoryCode VARCHAR(5),
    UnitPrice decimal(6,2),
    PRIMARY KEY (AutoIncProduct),
    CONSTRAINT CategoryCode_Constraint
    FOREIGN KEY (CategoryCode)
    REFERENCES CATEGORY(AutoIncCategoryNo) 
);

I have 2 tables as shown above. And here is the error that I got.

Column 'CATEGORY.AutoIncCategoryNo' is not the same length or scale as referencing column 'PRODUCT.CategoryCode' in foreign key 'CategoryCode_Constraint'. Columns participating in a foreign key relationship must be defined with the same length and scale.

How can I fix it?

This might help you [How to put foreign key constraints on a computed fields in sql server?](http://stackoverflow.com/a/2548873/3752496) — Shakeer Mirza, May 19 '17 at 04:48

score 1 · Accepted Answer · answered May 19 '17 at 04:55

When you create foreign key constraints at that time make sure column data type and size are equal. Try this.

CREATE TABLE CATEGORY (
    CategoryNo int IDENTITY(1,1) NOT NULL,
    AutoIncCategoryNo AS 'CAT' + RIGHT('00000' + CAST(CategoryNo AS VARCHAR(5)),5) PERSISTED,
    CategoryName varchar(100) NOT NULL,
    PRIMARY KEY (AutoIncCategoryNo)
);

    CREATE TABLE PRODUCT (
    ProductNo int IDENTITY(1,1) NOT NULL,
    AutoIncProduct AS 'P' + RIGHT('00000' + CAST(ProductNo AS VARCHAR(5)),5) PERSISTED,
    ProductName varchar(100) NOT NULL,
    CategoryCode VARCHAR(8),
    UnitPrice decimal(6,2),
    PRIMARY KEY (AutoIncProduct),
    CONSTRAINT CategoryCode_Constraint
    FOREIGN KEY (CategoryCode)
    REFERENCES CATEGORY(AutoIncCategoryNo) 
);

I am new to sql server, can you explain to me why is it 8? thanks! — bys0202, May 19 '17 at 05:11

score 0 · Answer 2 · answered May 19 '17 at 05:03

0

You are adding 'CAT' with Varchar(5) in the CATEGORY.AutoIncCategoryNo column

but PRODUCT.CategoryCode is VARCHAR(5). you have to MAKE IT AS VARCHAR(8).

answered May 19 '17 at 05:03

Siladitya

175
1
1
9

score 0 · Answer 3 · answered Oct 27 '22 at 10:13

Column 'Student.Admission_Number' is not the same length or scale as referencing column 'atten.Admission_Number' in foreign key 'FK__atten__Admission__75A278F5'. Columns participating in a foreign key relationship must be defined with the same length and scale

Foreign Key Error(length issues)

3 Answers3