How to represent an (optional) one to (optional) one relationship

Question

I'm creating a database from an UML diagram. I trying to code a case I never had which is an (optional) one to (optional) one relationship.

I have two tables, let's call them A and B. A can have a reference to B through a 0..1 relationship. The problem is that B is supposed to be referenced by one A object at most.

I'm not sure how I should approach this. Should I put a reference to A in B and a reference to B in a? Should I only put only a reference to B in A (and force the other check in a trigger or in the code)?

If it can help, these links won't created often, and only one by one so there won't be performance issues if checks are done in the code or in triggers. But I'd rather forbid bad data through the database design than only through the code.

It seems like B's reference should be in A. That way you can restrict rows having more than one reference to a B's id. — Vamsi Prabhala, Jun 26 '15 at 17:25
@PM77-1 Nice and simple solution. I can't believe I hadn't thought of this one. You should put it as an answer. — Loïc Lopes, Jun 26 '15 at 18:48
I think a UNIQUE constraint will only allow one row to have a NULL value. Does that suit your requirements for a 0..1 relationship? — Tab Alleman, Jun 26 '15 at 19:58
It seems, that [unlike MySQL](http://dev.mysql.com/doc/refman/5.7/en/create-table.html), [SQL Server](https://msdn.microsoft.com/en-us/library/ms187550.aspx) does **not** allow multiple NULL values in a UNIQUE column. — PM 77-1, Jun 26 '15 at 23:54
See [this article](http://improvingsoftware.com/2010/03/26/creating-a-unique-constraint-that-ignores-nulls-in-sql-server/) for possible solutions. — PM 77-1, Jun 26 '15 at 23:57
Related: [SQL Server 2005 unique key with null value](http://stackoverflow.com/questions/5982933/sql-server-2005-unique-key-with-null-value). — PM 77-1, Jun 27 '15 at 00:00
Just to make sure I understand you correctly - each row in A can have one or zero reference to a row in B, and each row in B can have one or zero reference to a row in A, right? — Zohar Peled, Jun 28 '15 at 13:46
@Zohar Peled From the database point of view, yes. A can reference 0..1 B but a row in B can be referenced by only one row in A. This is why I thought about adding a foreign key in each table. For example, if A(id:5) references B(id:7) then B(id:7) would need to reference A(id:5). This solution sees weird to me but maybe it is the only one. I'll try the solution http://stackoverflow.com/a/767702/689336 and give you feedback. — Loïc Lopes, Jun 29 '15 at 08:05
One to One relationships in physical models don't exist. When you try to represent that you should have a Foreign key in each table from the other, like: Table A Primary Key is columnxx and Primary Key of table B is YY so you should add ColumnXX to table B and a foreign key and ColumnYY in table A and it's foreign Key. You should create a middle table that joins the two tables with Foreign Keys just like if it was a Many to many rlationship and the primary key of the new table should be the two foreign key columns together so you hace only unique combinations — Juan Gaete, Jul 03 '15 at 18:38
Well, the solution at http://stackoverflow.com/a/767702/689336 worked. Should I delete this question? — Loïc Lopes, Jul 06 '15 at 11:45

score 0 · Answer 1 · answered Jul 06 '15 at 10:51

please try this example,

    CREATE TABLE dbo.TableA
    (
        col tinyint NOT NULL ,
        Primary KEY(Col)
    );

    CREATE TABLE dbo.TableB
    (
        colRef    tinyint NULL
    );

    Alter Table TableB
    ADD FOREIGN KEY (ColRef)
    REFERENCES TableA(Col)

    INSERT dbo.TableA VALUES (1);
    INSERT dbo.TableA VALUES (2);

    GO
    CREATE VIEW dbo.TableBUnique
    WITH SCHEMABINDING AS
    SELECT b.colRef
    FROM dbo.TableB AS b
    WHERE b.colRef IS NOT NULL;

    GO
    CREATE UNIQUE CLUSTERED INDEX uci
    ON dbo.TableBUnique (colRef);
    GO
    INSERT dbo.TableB VALUES (1);
    INSERT dbo.TableB VALUES (2);
    INSERT dbo.TableB VALUES (NULL);
    INSERT dbo.TableB VALUES (NULL);

    -- Error
    INSERT dbo.TableB VALUES (1); -- Duplicate Data
    INSERT dbo.TableB VALUES (3); -- Reference Not Exist

    GO

How to represent an (optional) one to (optional) one relationship

1 Answers1