locked
ERD 1:1 Relationship RRS feed

  • Question

  • I am trying to create a 1:1 relationship, but not primary key to primary key. In table 1 I have a uniqueidentifier as a primary key. In table 2 I have an int as the primary key and a column that takes the uniqueidentifier from table 1. Everytime I drag and drop the relationship line and link table 1 to table 2 it creates a 1:N relationship: ie. tbl1.primarykey links to tbl2.column2. So I'm not linking primary key to primary key however I still want a 1:1 relationship.

     

    How do I do that?

    Thanks in advance.

    Thursday, November 2, 2006 6:27 AM

Answers

  • There will always be a parent and child in any relationship.  So you will simply have one table reference the other, and then apply a constraint of some sort to enforce the cardinality.  Since you want 1-1, just add a UNIQUE constraint to the foreign key value...

    create table parent
    (
        parentId    int primary key
    )
    create table child
    (
        childId     int primary key,
        parentId    int not null references parent(parentId) unique
    )

    insert into parent
    select 1
    union all
    select 2

    insert into child 
    select 1,1
    union all
    select 2,2


    insert into child
    select 3,2

    --error

    Thursday, November 2, 2006 7:02 AM

All replies

  • There will always be a parent and child in any relationship.  So you will simply have one table reference the other, and then apply a constraint of some sort to enforce the cardinality.  Since you want 1-1, just add a UNIQUE constraint to the foreign key value...

    create table parent
    (
        parentId    int primary key
    )
    create table child
    (
        childId     int primary key,
        parentId    int not null references parent(parentId) unique
    )

    insert into parent
    select 1
    union all
    select 2

    insert into child 
    select 1,1
    union all
    select 2,2


    insert into child
    select 3,2

    --error

    Thursday, November 2, 2006 7:02 AM
  • Thanks a million man, totally worked. I do understand that there always has to be a parent child relationship I just didn't know how to make it a 1:1 on non primary key to primary key.

     

    Thanks again.

    Thursday, November 2, 2006 7:22 AM