size of a primary key and foreign key

Question

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.

Answer 1

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.

Answer 2

create Table Table1 ( id int identity(1,1), User_id as 'ABC'+Right('0000'+cast(id as varchar(20)),20) persisted Unique, Salary int );

sp_help Table1 -- here is check Length of User_id & give that length in reference table

create Table Table2 ( id int identity(1,1), User_id_Table2 varchar(23) foreign key references Table1(User_id), name varchar(50)

);