Defining a one-to-one relationship in SQL Server

by

One-to-one is actually frequently used in super-type/subtype relationship. In the child table, the primary key also serves as the foreign key to the parent table. Here is an example:

org_model_00

CREATE TABLE Organization
( 
     ID       int PRIMARY KEY,
     Name     varchar(200),
     Address  varchar(200),
     Phone    varchar(12)
)
GO

CREATE TABLE Customer
( 
     ID              int PRIMARY KEY,
     AccountManager  varchar(100)
)
GO

ALTER TABLE Customer
    ADD  FOREIGN KEY (ID) REFERENCES Organization(ID)
        ON DELETE CASCADE
        ON UPDATE CASCADE
GO

Leave a Comment

tech