Designing 1:1 and 1:m relationships in SQL Server

Question

In SQL Server 2008, how does one design a 1:1 and 1:m relationship?

Answer 1

One-to-One Relationship

Create Table ParentTable
    (
    PrimaryKeyCol ... not null Primary Key
    , ...
    )

Create Table ChildTable
    (
    , ForeignKeyCol ... [not] null [Primary Key, Unique]
    , ...
    , Constraint FK_ChildTable_ParentTable
        Foreign Key ( ForeignKeyCol )
        References ParentTable( PrimaryKeyCol )
    )

In this case, I can never have more than one row in the ChildTable for a given ParentTable primary key value. Note that even in a One-to-One relationship, one of the tables is the "parent" table. What differentiates a One-to-One relationship from a One-to-Many relationship purely in terms of implementation is whether the ChildTable's foreign key value has a Unique or Primary Key constraint.

One-to-Many Relationship

Create Table ParentTable
    (
    PrimaryKeyCol ... not null Primary Key
    , ...
    )

Create Table ChildTable
    (
    , ForeignKeyCol ... [not] null 
    , ...
    , Constraint FK_ChildTable_ParentTable
        Foreign Key ( PrimaryKeyCol )
        References ParentTable( PrimaryKeyCol )
    )

In this scenario, I can have multiple rows in the ChildTable for a given ParentTable primary key value.

Answer 2

A 1:1 relationship exists where table A and table B only exist once in regards to each other. Example: A student has 1 master student record. The student would be table A and the record in table B. Table B would contain a foreign key to the student record in table A (and possibly vice-versa)

A 1:m relationship exists where table A can be referenced or linked to by many entries in table B. Example: A student can take several books out from the library. The student again would be table A and the book could be the entry in table B. The entry in table B would contain a foreign key to who checked the book out, and many books could reference the same student.