locked
Setting up a one-to-one relationship RRS feed

  • Question

  • I know that under normal circumstances a one to one relationship would just mean you store all info in the same table. However, for purposes of logical organization (Equipment and their Warranties for instance), I would like to store the information seperately. Take for instance two tables, Equipment and Warranties. Each piece of equipment can have 0 or 1 warranty record, but no more than that.

    How would I set that up in SQL Management Studio? I know if I leave hte foreign key nullable it can accomodate no associated warranty record, but how do I create a true one-to-one relationship, without use of various "bailing wire & duct tape solutions"?


    Nathon Dalton .NET Software Developer
    Tuesday, January 5, 2010 9:49 PM

Answers

  • Hi Nathon,

    Something like this should work (you don't even have to set the FK to allow nulls):

    tblEquipment
    - pkEquipment [PK]
    - ...other info related to equipment...

    tblWarrenty
    - pkWarrenty [PK]
    - fkEquipment [FK] [UI]  <-- FK to tblEquipment
    - ... other info related to warrenty...


    - Doug
    • Edited by Doug_YMVP Tuesday, January 5, 2010 11:09 PM Typo
    • Marked as answer by Nathon Dalton Wednesday, January 6, 2010 11:31 PM
    Tuesday, January 5, 2010 10:00 PM
  • Yes, you can make a combination of values unique.  This is done by creating a unique constraint at the table level, not at the individual column level.  For example:

    CREATE TABLE Test (
    	col1 int, 
    	col2 int, 
    	CONSTRAINT uq_col1col2 UNIQUE (col1, col2)
    );
    Under the covers, this creates a unique index over the multiple columns.  You could just create an index instead of a constraint, but it's better to keep this marked as a constraint on the table than "just another index".

    The only problem I see with your original question is that having two separate tables makes it harder to enforce the rule that both tables must be kept in synch.  Doug's solution would allow a one-to-zero-or-one relationship, as it's not mandatory to have a warranty for an equipment.  Building on Doug's answer, I'd suggest:

    tblEquipment
    pkEquipment [PK Not Null Unique]
    fkWarranty [FK Not Null Unique]
    ...

    tblWarranty
    pkWarranty [PK Not Null Unique]
    fkEquipment [FK Not Null Unique]
    ...

    This ensures that you cannot put a row in one table without having a matching row in the other table. However, it's a bit of a chicken/egg problem, as you need a dummy value for the fkWarranty until you actually insert the Warranty row, and then you need to update tblEquipment with the actual ID of the Warranty. 

    As you can see, it's hard to create an actual one-to-one relationship, but the one-to-zero-or-one relationship is usually sufficient, and business logic and triggers can keep the data clean.

    The simplest option would be to use the same key value for both tables: 

    tblEquipment
    pkEquipment [PK Not Null Unique]
    ...

    tblWarranty
    pkEquipment [PK Not Null Unique FK Referencing tblEquipment]
    ...

    This still results in a one-to-zero-or-one relationship, but as they have the same key, it's easier to keep track of the two.  It prevents inserts into tblWarranty unless there's a matching row in tblEquipment, but does allow any number of pieces of equipment to not have a warranty.

    Another option would be to create a structure such as this, and then put a view on top, with an inner join between the two tables.  This way, the view will only show those rows that have an entry in both tables, and "incomplete" matches will be excluded from the view.
    • Marked as answer by Nathon Dalton Wednesday, January 6, 2010 11:31 PM
    Tuesday, January 5, 2010 10:57 PM
  • Jim,

    Since you can not have a warranty without an equipment, and you may have an equipment without a warranty, there is no need to store the warrenty PK (as FK) in the equipment table... 

    The following boils down to a personal preference, but I rather not use a compound PK in the warranty table, having a unique indexe on the equipment FKwill prevent having the same equipment listed twice, while making it easier to link the warranty table to other tables with only a single join.
    - Doug
    • Marked as answer by Nathon Dalton Wednesday, January 6, 2010 11:31 PM
    Tuesday, January 5, 2010 11:23 PM

All replies

  • Hi Nathon,

    Something like this should work (you don't even have to set the FK to allow nulls):

    tblEquipment
    - pkEquipment [PK]
    - ...other info related to equipment...

    tblWarrenty
    - pkWarrenty [PK]
    - fkEquipment [FK] [UI]  <-- FK to tblEquipment
    - ... other info related to warrenty...


    - Doug
    • Edited by Doug_YMVP Tuesday, January 5, 2010 11:09 PM Typo
    • Marked as answer by Nathon Dalton Wednesday, January 6, 2010 11:31 PM
    Tuesday, January 5, 2010 10:00 PM
  • Is the UI, unique? So this would be what's limiting the warranty records to only one per equipment record? This makes me think of another question. I have a table where there are two fields that when BOTH are considered, must be unique. So, there can be multiple of Col1 and multiple of Col2, but no other record may have the same value in both Col1 & Col2. Could I use unique constraints to deal with this situation?

    Tuesday, January 5, 2010 10:02 PM
  • Yes, that should work.  See the BOL pages for Unique Constraint and for Unique Indexes .  Of course, you could also use a primary key if that describes the entity more accurately.
    Please mark answered posts. Thanks for your time.
    Tuesday, January 5, 2010 10:35 PM
    Answerer
  • Yes, you can make a combination of values unique.  This is done by creating a unique constraint at the table level, not at the individual column level.  For example:

    CREATE TABLE Test (
    	col1 int, 
    	col2 int, 
    	CONSTRAINT uq_col1col2 UNIQUE (col1, col2)
    );
    Under the covers, this creates a unique index over the multiple columns.  You could just create an index instead of a constraint, but it's better to keep this marked as a constraint on the table than "just another index".

    The only problem I see with your original question is that having two separate tables makes it harder to enforce the rule that both tables must be kept in synch.  Doug's solution would allow a one-to-zero-or-one relationship, as it's not mandatory to have a warranty for an equipment.  Building on Doug's answer, I'd suggest:

    tblEquipment
    pkEquipment [PK Not Null Unique]
    fkWarranty [FK Not Null Unique]
    ...

    tblWarranty
    pkWarranty [PK Not Null Unique]
    fkEquipment [FK Not Null Unique]
    ...

    This ensures that you cannot put a row in one table without having a matching row in the other table. However, it's a bit of a chicken/egg problem, as you need a dummy value for the fkWarranty until you actually insert the Warranty row, and then you need to update tblEquipment with the actual ID of the Warranty. 

    As you can see, it's hard to create an actual one-to-one relationship, but the one-to-zero-or-one relationship is usually sufficient, and business logic and triggers can keep the data clean.

    The simplest option would be to use the same key value for both tables: 

    tblEquipment
    pkEquipment [PK Not Null Unique]
    ...

    tblWarranty
    pkEquipment [PK Not Null Unique FK Referencing tblEquipment]
    ...

    This still results in a one-to-zero-or-one relationship, but as they have the same key, it's easier to keep track of the two.  It prevents inserts into tblWarranty unless there's a matching row in tblEquipment, but does allow any number of pieces of equipment to not have a warranty.

    Another option would be to create a structure such as this, and then put a view on top, with an inner join between the two tables.  This way, the view will only show those rows that have an entry in both tables, and "incomplete" matches will be excluded from the view.
    • Marked as answer by Nathon Dalton Wednesday, January 6, 2010 11:31 PM
    Tuesday, January 5, 2010 10:57 PM
  • Jim,

    Since you can not have a warranty without an equipment, and you may have an equipment without a warranty, there is no need to store the warrenty PK (as FK) in the equipment table... 

    The following boils down to a personal preference, but I rather not use a compound PK in the warranty table, having a unique indexe on the equipment FKwill prevent having the same equipment listed twice, while making it easier to link the warranty table to other tables with only a single join.
    - Doug
    • Marked as answer by Nathon Dalton Wednesday, January 6, 2010 11:31 PM
    Tuesday, January 5, 2010 11:23 PM
  • What Eric said...  :-)

    You can set a unique index on multiple fields to accommodate the scenario you described.

    - Doug
    Tuesday, January 5, 2010 11:24 PM
  • You guys are awesome. Thanks for all the help! This has really helped me out on this issue.
    Nathon Dalton .NET Software Developer
    Wednesday, January 6, 2010 11:32 PM