1:1 Foreign Key Constraints

Question

How do you specify that a foreign key constraint should be a 1:1 relationship in transact sql? Is declaring the column UNIQUE enough? Below is my existing code.!

CREATE TABLE [dbo].MyTable(
    [MyTablekey] INT IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [OtherTableKey] INT NOT NULL UNIQUE
        CONSTRAINT [FK_MyTable_OtherTable] FOREIGN KEY REFERENCES [dbo].[OtherTable]([OtherTableKey]),
    ...
    CONSTRAINT [PK_MyTable] PRIMARY KEY CLUSTERED 
    (
        [MyTableKey] ASC
    ) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

Answer 1

A foreign key column with the UNIQUE and NOT NULL constraints that references a UNIQUE, NOT NULL column in another table creates a 1:(0|1) relationship, which is probably what you want.

If there was a true 1:1 relationship, every record in the first table would have a corresponding record in the second table and vice-versa. In that case, you would probably just want to make one table (unless you needed some strange storage optimization).

Answer 2

You could declare the column to be both the primary key and a foreign key. This is a good strategy for "extension" tables that are used to avoid putting nullable columns into the main table.

Answer 3

@bosnic:

You have a table CLIENT that has a 1:1 relationship with table SALES_OFFICE because, for example, the logic of your system says so.

What your app logic says, and what your data model say are 2 different things. There is nothing wrong with enforcing that relationship with your business logic code, but it has no place in the data model.

Would you really incorporate the data of SALES_OFFICE into CLIENT table?

If every CLIENT has a unique SALES_OFFICE, and every SALES_OFFICE has a singular, unique CLIENT - then yes, they should be in the same table. We just need a better name. ;)

And if another tables need to relate them selfs with SALES_OFFICE?

There's no reason to. Relate your other tables to CLIENT, since CLIENT has a unique SALES_OFFICE.

And what about database normalization best practices and patterns?

This is normalization.

To be fair, SALES_OFFICE and CLIENT is obviously not a 1:1 relationship - it's 1:N. Hopefully, your SALES_OFFICE exists to serve more than 1 client, and will continue to exist (for a while, at least) without any clients.

A more realistic example is SALES_OFFICE and ZIP_CODE. A SALES_OFFICE must have exactly 1 ZIP_CODE, and 2 SALES_OFFICEs - even if they have an equivalent ZIP_CODE - do not share the instance of a ZIP_CODE (so, changing the ZIP_CODE of 1 does not impact the other). Wouldn't you agree that ZIP_CODE belongs as a column in SALES_OFFICE?