Creating Foreign Key in SQLServer 2000?

elearnindia · March 9th, 2010, 11:41 AM

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.

Old Pedant · March 9th, 2010, 05:40 PM

You would get the same error in MySQL. (Well, same kind of error.)

It just means that you are trying to create a foreign key but the table and field(s) you are linking *TO* are not guaranteed to be unique.

Example:

Code:

tbl_faculty
name :: dept
Adams :: Engineering
Adams :: History
 
other_table
name FOREIGN KEY to tbl_faculty(name)

But you can't *use* tbl_faculty(name) as a foreign key because there are more than one record with the same value for "name" in tbl_faculty.

NOTE: And even if there are no duplicates at the time you create the foreign key, it will be rejected because there *may be* duplicates created later.

A foreign key *must* link to a field (or set of fields) that is guaranteed to have unique values. Meaning that the field(s) must be the primary key of the other table or must be a indexed as UNIQUE. Same as with MySQL.