Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2003
    Posts
    37

    Cool Unanswered: Can't 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.

    tam

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Add 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

  3. #3
    Join Date
    Oct 2003
    Posts
    37

    Red face

    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

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    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 data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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

  6. #6
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    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.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •