SQL Server – Size of Primary Key and Foreign Key

foreign keyprimary-keysql-server-2008

Do a primary key and a foreign key referencing it must have the same column size in sql server 2008 ? could someone indicate me a link to a documentation explaining this ?

Thank you for help.

Best Answer

Yes. It's in the MSDN documentation pages: Foreign Key relationships

A FOREIGN KEY constraint specified at the table level must have the same number of reference columns as the number of columns in the constraint column list. The data type of each reference column must also be the same as the corresponding column in the column list.

That page does not provide much more details but testing reveals that "same datatype" means same type and same size. Also, if one tries to make a FOREIGN KEY constraint between for example, a VARCHAR(20) and a VARCHAR(30) column, 

CREATE TABLE a
( aid VARCHAR(20) PRIMARY KEY ) ;

CREATE TABLE b
( bid INT PRIMARY KEY,
  aID VARCHAR(30) ) ;


ALTER TABLE b
  ADD CONSTRAINT test_different_sizes
    FOREIGN KEY (aid)
    REFERENCES a (aid) ;

we get the explanatory error:

Schema Creation Failed: Column a.aid is not the same length or scale as referencing column b.aid in foreign key test_different_sizes. Columns participating in a foreign key relationship must be defined with the same length and scale.

Also note that some DBMS (like Oracle, MySQL, Postgres) allow foreign keys between columns of same datatype (i.e. VARCHAR or CHAR) but different size.

Postgres is even less strict. It allows foreign keys between columns of CHAR, VARCHAR or TEXT datatypes in any combination.

Related Question