Create a Single-to-Option-One Constraint in SQL Server

Question

I have a "main table", name 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 to restrict the foreign key from the Illegal table back to the Customers main table:

In other words:

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

My instinct was to drag and drop on a SQL Server database diagram

• FROM table Illegal TO table Customers

An indication to SQL Server that Customers_IllegallyObtainedInformation is a "child" in relation. Instead, what happens in this SQL Server makes it one-to-one:

This means that if you try to insert Customer , it will not work because there is no existing Illegal information.

How to create a parent-child or one-to-one relationship in SQL Server?

---

Note: Do not confuse the example with the question. I could create a sacrificial primary surrogate key in the Illegal table:

But that was not my question.

Answer 1

Others have already indicated how you can achieve what you want by setting up relationships using SQL scripts. I thought I would just add a few cents about what the designer does ...

Basically you drag the wrong direction.

A foreign key in itself is always one-to-many. This is a way to tell the DBMS that you have a table (child table) where you want the column (or combination of columns) to always match the key from another table. Thanks to this information, the DBMS can take responsibility for ensuring that each row in the child table actually fulfills this requirement.

By making the key a column in the child table as well, the connection can be made de facto one-to-one, but from the DBMS point of view this is not the property of the relationship. Most likely, this is another limitation on the data that can be inserted into the child table.

When creating relationships in the designer, it seems like someone decided that the primary key should be dragged into the child table. Therefore, when you drag from Customers_IllegallyObtainedInformation to Clients , the constructor shows that Customers_IllegallyObtainedInformation is a table containing the primary key.

But wait, why did he work with the second sample, where did you enter the surrogate key? Probably because the people creating the designer decided to make him smart. In this case, you are dragging a column that is not a key in the table. This cannot form the primary key in the relation; therefore, the designer checks whether the relation can be formed in the opposite direction. And since it’s possible, that’s what he offers ...

Answer 2

The designer seems to be creating a foreign key in the opposite direction.

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

Answer 3

PRIMARY KEY can participate in the outgoing FOREIGN KEY relationship.

 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)