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. Thanks in advance for the help!

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

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 2

Based on your code above, the unique constraint would be enough given that the for every primary key you have in the table, the unique constrained column is also unique. Also, this assumes that in [OtherTable], the [OtherTableKey] column is the primary key of that table.

Answer 3

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 4

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

This is very incorrect. Let me give you an example. 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. Would you really incorporate the data of SALES_OFFICE into CLIENT table? And if another tables need to relate them selfs with SALES_OFFICE? And what about database normalization best practices and patterns?

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.

The first part of your answer is the right answer, without the second part, unless the data in second table is really a kind of information that belongs to first table and never will be used by other tables.

Answer 5

@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?