Designing 1:1 and 1:M Relationships in SQL Server

How to implement one-to-one, one-to-many and many-to-many relationships while designing tables?

One-to-one: Use a foreign key to the referenced table:

student: student_id, first_name, last_name, address_id
address: address_id, address, city, zipcode, student_id # you can have a
                                                        # "link back" if you need

You must also put a unique constraint on the foreign key column (addess.student_id) to prevent multiple rows in the child table (address) from relating to the same row in the referenced table (student).

One-to-many: Use a foreign key on the many side of the relationship linking back to the "one" side:

teachers: teacher_id, first_name, last_name # the "one" side
classes:  class_id, class_name, teacher_id  # the "many" side

Many-to-many: Use a junction table (example):

student: student_id, first_name, last_name
classes: class_id, name, teacher_id
student_classes: class_id, student_id     # the junction table

Example queries:

 -- Getting all students for a class:

    SELECT s.student_id, last_name
      FROM student_classes sc 
INNER JOIN students s ON s.student_id = sc.student_id
     WHERE sc.class_id = X

 -- Getting all classes for a student: 

    SELECT c.class_id, name
      FROM student_classes sc 
INNER JOIN classes c ON c.class_id = sc.class_id
     WHERE sc.student_id = Y

How to Create a real one-to-one relationship in SQL Server

I'm pretty sure it is technically impossible in SQL Server to have a True 1 to 1 relationship, as that would mean you would have to insert both records at the same time (otherwise you'd get a constraint error on insert), in both tables, with both tables having a foreign key relationship to each other.

That being said, your database design described with a foreign key is a 1 to 0..1 relationship. There is no constraint possible that would require a record in tableB. You can have a pseudo-relationship with a trigger that creates the record in tableB.

So there are a few pseudo-solutions

First, store all the data in a single table. Then you'll have no issues in EF.

Or Secondly, your entity must be smart enough to not allow an insert unless it has an associated record.

Or thirdly, and most likely, you have a problem you are trying to solve, and you are asking us why your solution doesn't work instead of the actual problem you are trying to solve (an XY Problem).

UPDATE

To explain in REALITY how 1 to 1 relationships don't work, I'll use the analogy of the Chicken or the egg dilemma. I don't intend to solve this dilemma, but if you were to have a constraint that says in order to add a an Egg to the Egg table, the relationship of the Chicken must exist, and the chicken must exist in the table, then you couldn't add an Egg to the Egg table. The opposite is also true. You cannot add a Chicken to the Chicken table without both the relationship to the Egg and the Egg existing in the Egg table. Thus no records can be every made, in a database without breaking one of the rules/constraints.

Database nomenclature of a one-to-one relationship is misleading. All relationships I've seen (there-fore my experience) would be more descriptive as one-to-(zero or one) relationships.

UPDATE EF 5.0 - one-to-one Support

While SQL Server will still allow the dependent row to be null. Entity Framework Core 5.0 now allows you to configure dependent properties as required. EF 5 What's new

Excerpt:

In EF Core 5.0, a navigation to an owned entity can be configured as a required dependent. For example:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Person>(b =>
    {
        b.OwnsOne(e => e.HomeAddress,
            b =>
            {
                b.Property(e => e.City).IsRequired();
                b.Property(e => e.Postcode).IsRequired();
            });
        b.Navigation(e => e.HomeAddress).IsRequired();
    });
}

Relationship between tables (1:1, 1:M) SQL Server

When you have a table that has many references, you cannot add the references inside that table because you cannot know how many field must be added. So, just add the Activity id in that referenced table. In your example when an Activity has many Requests you cannot add MANY columns inside Activity table. So, for each request just add id of the Activity to refer to it. So in your example add a foreign key in Request table to refer to the Activity id:

Activity:

• Id (PK)
• description

Request:

• ID (PK)
• accepted
• activityID (FK)

Implementing one-to-zero-or-one relation in SQL Server

The 1-0..1 relation in your database is directly visible. It is built between Course and OnlineCourse tables where Course is principal in relation (1) and OnlineCourse is dependent with FK configured on CourseID. FK is also PK of the OnlineCourse = it must be unique and because of that it is 0..1.

Database "always" uses 1 - 0..1 because real 1 - 1 cannot be effectively used for data insertion. 1 - 1 means that left must be inserted after right but right must be inserted after left = impossible. Because of that 1 - 0..1 is used where left is principal and must be inserted before right and right is dependent and must be inserted after left.

Design 1 or 2 tables for a 1 to (0..1) relationship with SQL Server

Since it is possible for a user not to own a company, this is not a true "1 to 1" relationship.

In fact, this is "1 to 0..1", and you can model it in one of the two ways:

1. Have everything in one table:

   

   Note how COMPANY_ID is both UNIQUE (preventing multiple users from owning the same company) and NULL-able (allowing for the users that don't own a company). The separation of USER_ID and COMPANY_ID is what allows company-level foreign keys (i.e. allows child tables to reference company, while preventing them from referencing company-less users).

   If there are no company-level FKs, you can omit the COMPANY_ID altogether.

   You'll also need a CHECK to ensure no other company field can be set unless COMPANY_ID is set (or at the very least that the correct subset of company fields is non-NULL).

2. Have two tables:

   

   We can't just have PKs of these two tables also be FKs (in both directions) because MS SQL Server does't support deferred constraints that would be needed to resolve the chicken-and-egg problem when inserting new data, nor it would correctly model the "1 to 0..1" relationship (it would model "1 to 1" and not allow company-less users).

Which one of these two strategies should you choose depends largely on the number of companies compared to users:

• If most users own a company, choose (1).
• If there are many more users than companies, choose (2).

SQL table: create 1-to-1 relationship with itself?

You should add a ParentID column to your table MenuItem with a foreign key.
This is an example on how to do that.

alter table MenuItem
add ParentID int null;

alter table MenuItem
add constraint FK_MenuItemParent foreign key (ParentID) references MenuItem (ID);

Now you have an hierarchical table, which means that a menuitem can have only one parent, but many other menuitems can have the same menuitem as parent

A Link Table is only needed when you need a many to many relationship, which is not the case for this

Also you can create an unique index on both columns, as suggested, but beware that the ParentID can be null often so add a clause to fix that

create unique nonclustered index idx_MenuParentID
on MenuItem(ID, ParentID)
where ParentID is not null;

Is there ever a time where using a database 1:1 relationship makes sense?

A 1:1 relationship typically indicates that you have partitioned a larger entity for some reason. Often it is because of performance reasons in the physical schema, but it can happen in the logic side as well if a large chunk of the data is expected to be "unknown" at the same time (in which case you have a 1:0 or 1:1, but no more).

As an example of a logical partition: you have data about an employee, but there is a larger set of data that needs to be collected, if and only if they select to have health coverage. I would keep the demographic data regarding health coverage in a different table to both give easier security partitioning and to avoid hauling that data around in queries unrelated to insurance.

An example of a physical partition would be the same data being hosted on multiple servers. I may keep the health coverage demographic data in another state (where the HR office is, for example) and the primary database may only link to it via a linked server... avoiding replicating sensitive data to other locations, yet making it available for (assuming here rare) queries that need it.

Physical partitioning can be useful whenever you have queries that need consistent subsets of a larger entity.

---

Related Topics