Cant create a foreign key using non_primary key fields

Howdy all !
I'm just a bit on the frustrated side cause I want to create a foreign key but SQL Server won't let me. :(

I have table A with a primary key of main_id
I have table B with a primary key of another_id

Table A has a corresponding field called another_id.

I'm trying to create a foreign key between Table A & Table B on another_id but since it's not the Primary Key in Table A I get the following error:

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

Am I missing something totally obvious here? Why should I have to create a foreign key on a primary key?

What I find interesting is that I can create the relationship from enterprise manager but when I script it out is when I run into problems.

Here's the script I'm using:

Begin Code

alter table B add
constraint fk_classB_classA foreign key
(classB) references A (classB)
on delete no action
on update no action

End Code

Any help is greatly appreciated.

tamAdd a UNIQUE constraint and then SQL Server will oblige. It won't let you create an FK relationship to a column that could have more than one row with the "target" value, due to the chaos that can cause.

-PatP|||Hey Pat,
Many thanks for the quick response and I'll give that a try, but could you explain to me why it does it from enterprise manager? If I go into diagrams and drag and drop from one table to the other it comes up as a foreign key not a unique constraint.

Is it actually creating a unique constraint behind the curtain and labeling it as a Foreign key?

I'm more curious than anything. To me it just doesn't make much sense.

Again, thanks for the answer.
tam|||I don't think EM is actually creating FK relationship on the columns that you think it does. Right-mouse click on the relationship and select Properties, and see what fields participate in the relationship.|||The two ideas (UNIQUE CONSTRAINT and Foreign Key) are related, but they are decidedly not the same thing.

A unique constraint means that the column(s) that the constraint applies to are unique within the table, only one row can exist with a particular value. As an example, you might have an employee table that has an EmployeeID column which is the Primary Key, and an SSN column that is Unique. There can be only one row with any given value of EmployeeID, and NULL values are never allowed. There can be NULL values for SSN (which is good, since some employees may not have one), but the database engine won't allow two rows to have the same value.

Various tables in your schema would link to your employee table using the EmployeeID as a Foreign Key. You might get a table from the IRS that has employee data organized by SSN, and you could make that SSN a Foreign Key to the employee table too (because it has a Unique constraint). Not every employee may have this detail data, but the detail can apply to only one employee.

You might get data from a shoe manufacturer telling you about their schwell new work shoes. Even if they provide information by shoe size, you can't make the shoe size a foreign key to employee, because in the employee table the shoe size column couldn't have a unique constraint (at least not in most companies anyway!).

-PatP|||UNIQUE constraint on SSN? Then there may be only 1 employee without a valid unique SSN, because UNIQUE constraint will allow only 1 NULL-valued row for that field.|||Well I'll be horny-swoggled! I'd forgotten that they'd changed that. Good catch!

Ok, so much for my sterling example then... It used to hold water, long ago and far away!

-PatP