none
How can I create a one-to-one relationship in a MS SQL Server Management Studio Express Relationship diagram?

    Question

  • How can I create a one-to-one relationship in a MS SQL Server Management Studio Express Relationship diagram?

    For example:
    I have 2 tables, tbl1 and tbl2.

    tbl1 has the following columns:
    id {uniqueidentifier} as PK
    name {nvarchar(50)}

    tbl2 has the following columns:
    id {uniqueidentifier} as PK
    name {nvarchar(50)}
    tbl1_id {uniqueidentifier} as FK linked to tbl1.id


    If I drag and drop the tbl1.id column to tbl2, I end up with a one-to-many relationship. How do I create a one-to-one relationship instead?

    mradlmaier

    Saturday, March 25, 2006 5:30 PM

Answers

  • there is no such thing as an explicit one-to-one relationship.

    But, by the fact that tbl1.id and tbl2.id are primary keys and tbl2.id is a foreign key referenceing tbl1.id, you have created an implicit 1:0..1 relationship. 

    • Proposed as answer by busyAtWork Friday, December 10, 2010 4:06 PM
    • Marked as answer by Kalman TothEditor Friday, February 11, 2011 1:53 PM
    Saturday, March 25, 2006 7:06 PM
  • What if you split a wide table into 2 tables (vertical partitioning) for performance or other reason and you want to keep the relationship 1 to 1?

    In such a setup the secondary table PRIMARY KEY is also a FOREIGN KEY which references the PRIMARY KEY of the main table.


    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012


    Thursday, December 16, 2010 5:59 AM
  • If you are willing to have tbl2 row be dependent on the existance of a tbl1 row, then eliminate the tbl1_id column in tbl2 and have the tbl2 PK be a foreign key to tbl1's PK. Results in a 1:1 with tbl2 row not able to be created without its PK being assigned to a tbl1 PK. Worked for me having a Meetings, Minutes, and Agendas tables with the PK of Minutes and Agendas being foreign keys to the Meetings PK. Ensures only one minutes and one agenda per meeting.
    • Proposed as answer by mousedoc Thursday, July 19, 2012 7:49 PM
    • Marked as answer by Kalman TothEditor Friday, July 20, 2012 12:00 AM
    Thursday, July 19, 2012 7:49 PM

All replies

  • there is no such thing as an explicit one-to-one relationship.

    But, by the fact that tbl1.id and tbl2.id are primary keys and tbl2.id is a foreign key referenceing tbl1.id, you have created an implicit 1:0..1 relationship. 

    • Proposed as answer by busyAtWork Friday, December 10, 2010 4:06 PM
    • Marked as answer by Kalman TothEditor Friday, February 11, 2011 1:53 PM
    Saturday, March 25, 2006 7:06 PM
  • Thank you. That's a great point. I may leverage that in my attempt to "walk" the metadata of existing databases and tables.
    Friday, December 10, 2010 4:06 PM
  • What if you split a wide table into 2 tables (vertical partitioning) for performance or other reason and you want to keep the relationship 1 to 1?

    In such a setup the secondary table PRIMARY KEY is also a FOREIGN KEY which references the PRIMARY KEY of the main table.


    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012


    Thursday, December 16, 2010 5:59 AM
  • Set the foreign key as a primary key, and then set the relationship on both primary key fields. That's it! You should see a key sign on both ends of the relationship line. This represents a one to one. 

     

    Hope this helps!

    Friday, February 04, 2011 9:35 PM
  • Hmmmm... Is it possible to have a cascading delete on the second table when the row on the first table is deleted?
    Wednesday, November 30, 2011 6:24 PM
  • If you are willing to have tbl2 row be dependent on the existance of a tbl1 row, then eliminate the tbl1_id column in tbl2 and have the tbl2 PK be a foreign key to tbl1's PK. Results in a 1:1 with tbl2 row not able to be created without its PK being assigned to a tbl1 PK. Worked for me having a Meetings, Minutes, and Agendas tables with the PK of Minutes and Agendas being foreign keys to the Meetings PK. Ensures only one minutes and one agenda per meeting.
    • Proposed as answer by mousedoc Thursday, July 19, 2012 7:49 PM
    • Marked as answer by Kalman TothEditor Friday, July 20, 2012 12:00 AM
    Thursday, July 19, 2012 7:49 PM
  • Curious Daniel ,

    I would highly appreciate if you look to my post please,

    http://social.msdn.microsoft.com/Forums/en-US/databasedesign/thread/4c8790af-e549-45b7-8557-c48bf17be401

    best regards


    adelr5@yahoo.com

    Friday, August 10, 2012 8:17 PM