Create a One-to-Optional-One constraint in SQL Server

Question

i have a "main table", call it Customers:

 CREATE TABLE Customers (
     CustomerID int PRIMARY KEY NOT NULL,
     FirstName nvarchar(50),
     LastName nvarchar(50)
 )

And i have a "satellite table", call it Customer_IllegallyObtainedInformation:

CREATE TABLE Customer_IllegallyObtainedInformation (
    CustomerID int PRIMARY KEY NOT NULL,
    CellPhonePin int,
    SexualOrientation varchar(20),
    EmailPassword varchar(50)
)

Now, what i want is a foreign key constraint from the Illegal table back to the main Customers table:

In other words:

• there can be a Customer without an Illegal entry
• but there can never be an Illegal entry without a Customer

My instinct was to, in the SQL Server database diagram, drag

• FROM the Illegal table TO the Customers table

Indicating to SQL Server that Customers_IllegallyObtainedInformation is the "child" in the relationship. Instead what happens in that SQL Server makes it a one-to-one relationship:

Meaning that if you try to insert a Customer, it will fail because there is no existing Illegal information.

How can i create a "Parent-Child", or "One-to-Optional-One" relationship in SQL Server?

---

Note: Don't confuse the example with the question. i could create an sacrificial primary surrogate key in the Illegal table:

But that wasn't my question.

Sweatshop

Illegally obtained information? Must be planning something definitely and completely benign. – Sheilahshekel

@Sheilahshekel Not to worry; definitely nothing that would violate the Financial Services Modernization Act of 1999. – Sweatshop

Answer 1

Others have already pointed out how you can acheive what you want by setting up the relationship using SQL scripts. I thought I would just add my few cents about what the designer is doing...

Basically you are dragging in the wrong direction.

A foreign key is by itself always One-To-Many. It is a way of informing the DBMS that you have a table (the child table) where you want a column (or a combination of columns) to always correspond to a key from another table. With this information, the DBMS can then take over the responsibility of making sure that each row in the child table actually fulfills this requirement.

By making the column a key also in the child table the relationship can be made de facto One-To-One, but from the DBMS perspective this is not really a property of the relationship. Rather it is just one more restriction on the data that can be inserted into the child table.

When creating a relationship in the designer, it seems someone decided that the primary key should be dragged into the child table. So when you drag from Customers_IllegallyObtainedInformation to Customers, the designer figures that the Customers_IllegallyObtainedInformation is the table containing the primary key.

But wait, why did it work with the second sample where you had introduced a surrogate key? Probably because the people making the designer decided to make it smart. In that case you are dragging a column that is not a key in the table. That cannot form the primary key in the relation so the designer checks if the relation can be formed in the opposite direction. And since it can, thats what it offers...

Towrey

The other guys answered it too; but this answer is best-er. i wish i had it sooner; would have saved me a lot of typing. – Sweatshop

Answer 2

It seems the designer is creating the foreign key in the opposite direction.

Just code it yourself:

 CREATE TABLE Customers (
         CustomerID int PRIMARY KEY NOT NULL,
         FirstName nvarchar(50),
         LastName nvarchar(50)
     )

    CREATE TABLE Customer_IllegallyObtainedInformation (
        CustomerID int PRIMARY KEY NOT NULL,
        CellPhonePin int,
        SexualOrientation varchar(20),
        EmailPassword varchar(50),

        constraint fk_Customers foreign key (CustomerId) references dbo.Customers
    )

    -- succeeds:
    insert into dbo.Customers
        values(1, 'One', 'One'), (2, 'Two', 'Two')

    --fails:
    insert into dbo.Customer_IllegallyObtainedInformation
        values(3, 1, '', '');

    --succeeds:
    insert into dbo.Customer_IllegallyObtainedInformation
        values(1, 1, '', '');

Yenta

+1, and same note about putting both constraints after the column definition as to @Joshua. – Tungstite

Answer 3

The PRIMARY KEY can participate in the outbound FOREIGN KEY relation.

CREATE TABLE Customer_IllegallyObtainedInformation (
    CustomerID int PRIMARY KEY NOT NULL,
    CellPhonePin int,
    SexualOrientation varchar(20),
    EmailPassword varchar(50)
)
ALTER TABLE Customer_IllegallyObtainedInformation ADD FOREIGN KEY (CustomerId)
        REFERENCES Customer(CustomerId)

Glynda

+1. Actually, both PRIMARY KEY and FOREIGN KEY can be specified after the column they pertain to, including the optional constraint names: ... CustomerID int CONSTRAINT PK_CustomerIOI PRIMARY KEY CONSTRAINT FK_CustomerIOI_Customer FOREIGN KEY REFERENCES Customer (CustomerID), ... – Tungstite

I'm sure they can. My scripts always have some incredible distance between CREATE TABLE and ALTER TABLE ... FOREIGN KEY so I never do it that way. – Glynda