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

Question

I have two tables tableA and tableB  I set tableB s primary key as foreign key which references tableA s primary  But when I use Entity Framework database-first  the model is 1 to 0  1    How does one create a one-to-one relationship in SQL Server

User · Answer

A 1 to 1 relationship is very much possible. Even if the relationship diagram doesn't show the 1 to 1 relationship explicitly. If you implement it as below, it will function as a one to one relationship.

I will use a basic example to explain the concept where a single person can only have a single passport. This example works perfectly in MS Access. For the SQL Server version follow this link.

Remember that in MS Access, SQL scripts can only be run one at a time and not as displayed here in sequence.

CREATE TABLE Person
(
Pk_Person_Id INT PRIMARY KEY,
Name VARCHAR(255),
EmailId VARCHAR(255),
);

CREATE TABLE PassportDetails
(
Pk_Passport_Id INT PRIMARY KEY,
Passport_Number VARCHAR(255),
Fk_Person_Id INT NOT NULL UNIQUE, 
FOREIGN KEY(Fk_Person_Id) REFERENCES Person(Pk_Person_Id)
);

User · Answer

This can be done by creating a simple primary foreign key relationship and setting the foreign key column to unique in the following manner   CREATE TABLE  Employee         ID     INT PRIMARY KEY      Name   VARCHAR 50      CREATE TABLE  Salary         EmployeeID     INT UNIQUE NOT NULL      SalaryAmount   INT      ALTER TABLE  Salary  ADD CONSTRAINT FK Salary Employee FOREIGN KEY  EmployeeID        REFERENCES  Employee   ID        INSERT INTO  Employee         ID       Name    VALUES      1   Ram        2   Rahim        3   Pankaj        4   Mohan     INSERT INTO  Salary         EmployeeID       SalaryAmount    VALUES      1  2000       2  3000       3  2500       4  3000     Check to see if everything is fine  SELECT   FROM  Employee   SELECT   FROM  Salary     Now Generally in Primary Foreign Relationship  One to many   you could enter multiple times EmployeeID  but here an error will be thrown  INSERT INTO  Salary         EmployeeID       SalaryAmount    VALUES      1  3000     The above statement will show error as     Violation of UNIQUE KEY constraint  UQ  Salary  7AD04FF0C044141D      Cannot insert duplicate key in object  dbo Salary   The duplicate key value is  1

User · Answer

The easiest way to achieve this is to create only 1 table with both Table A and B fields NOT NULL  This way it is impossible to have one without the other

User · Answer

Set the foreign key as a primary key  and then set the relationship on both primary key fields  That s it  You should see a key sign on both ends of the relationship line  This represents a one to one      Check this   SQL Server Database Design with a One To One Relationship

User · Answer

1 To 1 Relationships in SQL are made by merging the field of both table in one    I know you can split a Table in two entity with a 1 to 1 relation  Most of time you use this because you want to use lazy loading on  heavy field of binary data in a table     Exemple  You have a table containing pictures with a name column  string   maybe some metadata column  a thumbnail column and the picture itself varbinary max   In your application  you will certainly display first only the name and the thumbnail in a collection control and then load the  full picture data  only if needed   If it is what your are looking for  It is something called  table splitting  or  horizontal splitting    https   visualstudiomagazine com articles 2014 09 01 splitting-tables aspx

User · Answer

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 constrain 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

User · Answer

There is one way I know how to achieve a strictly  one-to-one relationship without using triggers  computed columns  additional tables  or other  exotic  tricks  only foreign keys and unique constraints   with one small caveat   I will borrow the chicken-and-the-egg concept from the accepted answer to help me explain the caveat   It is a fact that either a chicken or an egg must come first  in current DBs anyway   Luckily this solution does not get political and does not prescribe which has to come first - it leaves it up to the implementer   The caveat is that the table which allows a record to  come first  technically can have a record created without the corresponding record in the other table  however  in this solution  only one such record is allowed  When only one record is created  only chicken or egg   no more records can be added to any of the two tables until either the  lonely  record is deleted or a matching record is created in the other table   Solution   Add foreign keys to each table  referencing the other  add unique constraints to each foreign key  and make one foreign key nullable  the other not nullable and also a primary key  For this to work  the unique constrain on the nullable column must only allow one null  this is the case in SQL Server  not sure about other databases    CREATE TABLE dbo Egg       ID int identity 1 1  not null      Chicken int null      CONSTRAINT  PK Egg  PRIMARY KEY CLUSTERED   ID  ASC  ON  PRIMARY    ON  PRIMARY  GO CREATE TABLE dbo Chicken       Egg int not null      CONSTRAINT  PK Chicken  PRIMARY KEY CLUSTERED   Egg  ASC  ON  PRIMARY    ON  PRIMARY  GO ALTER TABLE dbo Egg  WITH NOCHECK ADD  CONSTRAINT  FK Egg Chicken  FOREIGN KEY  Chicken   REFERENCES  dbo   Chicken    Egg   GO ALTER TABLE dbo Chicken  WITH NOCHECK ADD  CONSTRAINT  FK Chicken Egg  FOREIGN KEY  Egg   REFERENCES  dbo   Egg    ID   GO ALTER TABLE dbo Egg WITH NOCHECK ADD CONSTRAINT  UQ Egg Chicken  UNIQUE  Chicken   GO ALTER TABLE dbo Chicken WITH NOCHECK ADD CONSTRAINT  UQ Chicken Egg  UNIQUE  Egg   GO   To insert  first an egg must be inserted  with null for Chicken   Now  only a chicken can be inserted and it must reference the  unclaimed  egg  Finally  the added egg can be updated and it must reference the  unclaimed  chicken  At no point can two chickens be made to reference the same egg or vice-versa   To delete  the same logic can be followed  update egg s Chicken to null  delete the newly  unclaimed  chicken  delete the egg   This solution also allows swapping easily  Interestingly  swapping might be the strongest argument for using such a solution  because it has a potential practical use  Normally  in most cases  a one-to-one relationship of two tables is better implemented by simply refactoring the two tables into one  however  in a potential scenario  the two tables may represent truly distinct entities  which require a strict one-to-one relationship  but need to frequently swap  partners  or be re-arranged in general  while still maintaining the one-to-one relationship after re-arrangement  If the more common solution were used  all data columns of one of the entities would have to be updated overwritten for all pairs being re-arranged  as opposed to this solution  where only one column of foreign keys need to be re-arranged  the nullable foreign key column    Well  this is the best I could do using standard constraints  don t judge    Maybe someone will find it useful

User · Answer

What about this    create table dbo  Address    Id int identity not null  City nvarchar 255  not null  Street nvarchar 255  not null  CONSTRAINT PK Address PRIMARY KEY  Id     create table dbo  Person    Id int identity not null  AddressId int not null  FirstName nvarchar 255  not null  LastName nvarchar 255  not null  CONSTRAINT PK Person PRIMARY KEY  Id   CONSTRAINT FK Person Address FOREIGN KEY  AddressId  REFERENCES dbo  Address   Id

[sql] How to Create a real one-to-one relationship in SQL Server

Examples related to sql

Examples related to sql-server

Examples related to entity-framework

Examples related to one-to-one