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.
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
Yes. It's in the MSDN documentation pages: Foreign Key relationships
FOREIGN KEYconstraint 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
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) ;
Schema Creation Failed: Column
a.aidis not the same length or scale as referencing column
b.aidin 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.
CHAR) but different size.
Postgres is even less strict. It allows foreign keys between columns of
TEXT datatypes in any combination.