Creating foreign key error?

• elearnindia123

  SSC Veteran

  Points: 233

  More actions

  March 9, 2010 at 6:37 am

  #221407

  While trying to create foreign key on a table in SQLServer2000 following error is coming

  "The columns in "tbl_faculty" do not match an existing primary key or UNIQUE constraint"

  I work in MySQL and SQLServer 2000 is new to me.

  Any kind help will be appreciated.Thanks in advance.

• Carl Federl

  One Orange Chip

  Points: 25384

  More actions

  March 9, 2010 at 6:54 am

  #1130619

  "The columns in "tbl_faculty" do not match an existing primary key or UNIQUE constraint"

  The error message with SQL Server 2008 is a little clearer :

  Msg 1776, Level 16, State 0, Line 1

  There are no primary or candidate keys in the referenced table 'dbo.Foo' that match the referencing column list in the foreign key 'Fum_FK_Foo_FooName'.

  Test schema:

  Create table dbo.Foo

  (FooIdinteger not null

  , FooNamevarchar(255) not null

  , constraint Foo_PK primary key (FooId)

  );

  Create table dbo.Fum

  ( FumIdinteger not null

  , FumNamevarchar(255) not null

  , FooIdinteger not null

  , FooNamevarchar(255) not null

  , constraint Fum_PK primary key (FumId)

  );

  This Foreign key definition succeeds because there is a primary key constraint on table Foo for column(s) FooId:

  alter table dbo.Fum

  add constraint Fum_FK_Foo_FooId foreign key

  (FooId )

  references dbo.Foo;

  This Foreign key definition fails because there is no primary key constraint or uniquness constraint on table Foo for column(s) FooName :

  alter table dbo.Fum

  add constraint Fum_FK_Foo_FooName foreign key

  (FooName )

  references dbo.Foo (FooName );

  SQL = Scarcely Qualifies as a Language