Today, we’re going to discuss how to implement One-to-One relationship between two tables in SQL Server. So, what does it mean? That’s pretty simple. We can simply go to Database Diagram option and drag Primary Key from master table and drop that on Foreign Key on child table…and that’s it. What’s the big deal???
Yes. It will definitely implement Foreign Key constraint but the cardinality will be One-to-Many by default. Here we get the problem. I wish to implement One-to-One cardinality rather One-to-Many. I tried a lot with available options but in vain. The SQL Server doesn’t provide any option where you can simply go and change the cardinality between tables.
Let’s discuss how to achieve this in SQL Server.
I’ve got two tables, named 1.) Customer, and 2.) CustomerContact as you can see in below image.
Usually, a customer will be having one or more than one Contact Person. However, at present, a Customer will be having only on Contact Person. In that case, we should ideally merge both the tables and we’re done with the requirement. Yes. But, I know, for sure, the requirement is going to change in future where a Customer will have one or more than one Contact Person. Therefore, I wish to keep my database design intact, even if requirement gets changed.
When I go and create a relationship between both tables using Database Diagram option in SQL Server, I get below relationship. You can see it’s One-to-Many relationship as shown in below image. I wish to have it One-to-One. So, how to achieve it?
Delete the existing relationship. It should look like as shown in below image.
Create a Unique constraint on CustomerId column in CustomerContact table.
Drag CustomerId column from Customer table onto CustomerId column in CustomerContact table. Then, a window will pop up to map the columns, etc. Just click OK…OK and, hurray! We’ve done it. It will create a Foreign Key constraint with One-to-One relationship. Take a look at below image.
However, there is a negative point with this approach is that it creates a Non-Clustered index on CustomerId column in CustomerContact table when you create Unique constraint on it. You can check that from Indexes folder once you expand CustomerContact table.
I feel Microsoft SQL Server should provide an option where we can just change the cardinality from One-to-Many to One-to-One, instead of going through such unnecessary steps.
DhananjaySpeaks 