CODEWITHSUNDEEP
sql-serversql-server-2008-r2relationshipstable-relationships
Ian Boyd
Ian Boyd

Reputation: 256951

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

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

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

enter image description here

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)
)

enter image description here

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

enter image description here

In other words:

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

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:

enter image description here

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:

enter image description here

But that wasn't my question.

Upvotes: 6

Views: 6869

Answers (3)

user1429080
user1429080

Reputation: 9166

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...

Upvotes: 5

Joshua
Joshua

Reputation: 43317

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)

Upvotes: 5

nathan_jr
nathan_jr

Reputation: 9292

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, '', '');

Upvotes: 9

Related Questions