2

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.

1 Answer 1

6

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.

4
  • you mean here data type = datatype+size. right ?
    – mounaim
    Aug 6, 2014 at 14:43
  • Yes. I'll edit it to be more clear. Aug 6, 2014 at 14:54
  • you haven't edited the response yet :)
    – mounaim
    Aug 7, 2014 at 9:48
  • I would upvote you twice man ;) thanks for the great explanation :)
    – mounaim
    Aug 7, 2014 at 14:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.