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

Question

I have two tables Country and Capital, I set Capital's primary key as foreign key which references Country'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?

Answer 1

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

Answer 2

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

Answer 3

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.

Answer 4

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

Answer 5

How does one create a one-to-one relationship in SQL Server?

Short answer: You can't.

Long answer: You can, if you dare to read on...

---

I understand there are two main approaches for "implementing" 1:1 relationships when deferrable constraints are not supported by a DBMS (*cough* MS SQL Server *cough*). This post discusses those 2 main approaches.

Both of these approaches have some degree of compatibility with EF by tricking EF into treating a VIEW as a TABLE. If you aren't using EF then you probably don't need the VIEW objects, but they're still handy for convenience queries and for quickly querying a product type view of your 1:1 entities in separate tables.

Both of these approaches are built around using another table (ValidCountries) which contains only PK values, and exists for 2 reasons:

1. To have FK constraints to both of the 1:1 member tables (don't forget you can also have three or more 1:1 tables too!): so a row in ValidCountries cannot exist unless all required related data exists in their respective tables.
2. To provide a target for any incoming FOREIGN KEY constraints from other entities. This is explained in more detail and demonstrated below.

The two approaches differ in their constraints on the 1:1 member tables, their use of TRIGGER objects, and their compatibility with EF. I'm sure more variations on these 2 approaches are possible - it really depends on how you modelled your data and your business requirements.

Neither of these approaches use CHECK CONSTRAINT rules with UDFs to validate data in other tables, which is currently the predominant way to implement 1:1 constraints, but that approach has a poor reputation for performance.

---

Approach 1: Use two more TABLE objects (one for forward-declarations, the other as proof-of-validity), and a read/write VIEW to expose only valid 1:1 data from a JOIN:

• This approach uses a third table to "forward-declare" only the (shared) PK values, while other tables that want a 1:1 relationship with each other reference only the forward-declaration table.

• Another "final" TABLE is used to prove (via FK constraints) that for any given PK, that valid definitely exists.

• This complexity is then hidden behind an (technically optional) VIEW object which exposes only valid data and performs an INNER JOIN of the 3 (or more) backing tables, while also supporting INSERT/UPDATE/DELETE/MERGE DML operations.

  • This works great with Entity Framework as EF is perfectly happy to pretend that a VIEW is a TABLE. A caveat is that all these approaches are strictly database-first because all these approaches outsmart EF to bend it to our will (so be sure to disable migrations!)
  • While the "final" table might seem superfluous as the VIEW won't ever expose invalid data, it's actually quite necessary to serve as a target for incoming foreign-key references from other separate entity tables (which must never reference the forward-declarations table).
    • This is because, unfortunately, VIEW objects (even Indexed Views) cannot participate in RDBMS foreign-key constraints, which is annoying.

• The three tables are:

  1. Table 1: The "forward-declaration table" with only the PK value.
  • In the OP's example (of Countries and Capitals), this would be a table named like CountryDeclarations (or CountryDecl for short) and stores only CountryName values, which is the shared PK for both the Countries and Capitals tables).
  2. Table 2: One (or more!) dependent tables with FKs to the forward-declaration table.
     • In the OP's example this would be 2 tables:
       • TABLE Countries with CountryName as the table's PK and its FK to only the forward-declaration table.
       • TABLE Capitals with CountryName as the table's PK and its FK to only the forward-declaration table.
  3. Table 3: The publicly-visible principal table, which has FKs to the forward-declaration table and all dependent tables.
  • In the OP's example this would be TABLE ValidCountries with a PK + FK to CountryDecl and separate FK columns to Countries and Capitals.

Here's a database-diagram of this approach:

• When querying data from the Countries and/or Capitals tables, provided provided you always INNER JOIN with ValidCountries then you get hard guarantees that you're always querying valid data.

  • Or just use the VIEW to get the JOIN already-done for you.

• Remember that the 1:1 relationship is not enforced between the constituent Countries and Capitals tables: this is necessary otherwise there would be a chicken vs. egg problem on INSERT.

  • Though if you're sure you'll always INSERT into Countries before Capitals (and DELETE in the reverse order) you could add an FK constraint from Capitals directly to Countries, but this doesn't really add any benefits because the Countries table cannot provide guarantees that a corresponding Capitals row will exist.

• This design is compatible with IDENTITY PKs too, just remember that only the forward-declaration table will have the IDENTITY column, all other tables will have normal int PK+FK columns.

Here's the SQL for this approach:

CREATE SCHEMA app1; /* The `app1` schema contains the individual objects to avoid namespace pollution in `dbo`. */
GO

CREATE TABLE app1.CountryDecl (
    CountryName nvarchar(100) NOT NULL,
    CONSTRAINT PK_CountryDecl PRIMARY KEY ( CountryName )
);
GO

CREATE TABLE app1.Countries (
    CountryName nvarchar(100) NOT NULL,
    CapitalName nvarchar(255) NOT NULL,
    Inhabitants bigint        NOT NULL,
    AreaKM2     bigint        NOT NULL,

    CONSTRAINT PK_Countries PRIMARY KEY ( CountryName ),

    CONSTRAINT FK_CountriesDecl FOREIGN KEY ( CountryName ) REFERENCES app1.CountryDecl ( CountryName ),
--  CONSTRAINT FK_Countries_Capitals FOREIGN KEY ( CountryName ) REFERENCES app1.Capitals ( CountryName ) -- This FK is entirely optional and adds no value, imo.
);
GO

CREATE TABLE app1.Capitals (
    CountryName nvarchar(100) NOT NULL,
    CapitalName nvarchar(255) NOT NULL,
    Inhabitants bigint        NOT NULL,
    AreaKM2     int           NOT NULL,

    CONSTRAINT PK_Capitals PRIMARY KEY ( CountryName ),

    CONSTRAINT FK_CountriesDecl FOREIGN KEY ( CountryName ) REFERENCES app1.CountryDecl ( CountryName )
);
GO

CREATE TABLE app1.ValidCountries (

    CountryName nvarchar(100) NOT NULL,

    CONSTRAINT PK_ValidCountries PRIMARY KEY ( CountryName ),

    CONSTRAINT FK_ValidCountries_to_Capitals FOREIGN KEY ( CountryName ) REFERENCES app1.Capitals ( CountryName ),
    CONSTRAINT FK_ValidCountries_to_Countries FOREIGN KEY ( CountryName ) REFERENCES app1.Countries ( CountryName ).
    CONSTRAINT FK_ValidCountries_to_Decl FOREIGN KEY( CountryName ) REFERENCES app1.CountriesDecl ( CountryName )
);
GO

CREATE VIEW dbo.Countries AS
SELECT
    -- ValidCountries:
    v.CountryName,

    -- Countries
    cun.Inhabitants     AS CountryInhabitants,
    cun.Area            AS CountryArea,

    -- Capitals
    cap.Capital         AS CapitalCityName,
    cap.CityArea        AS CapitalCityArea,
    cap.CityInhabitants AS CapitalCityInhabitants

FROM
    app1.ValidCountries AS v
    INNER JOIN app1.Countries AS cun ON v.CountryName = cun.CountryName
    INNER JOIN app1.Capitals  AS cap ON v.CountryName = cap.CountryName;

GO

CREATE TRIGGER Countries_Insert ON dbo.Countries
INSTEAD OF INSERT 
AS
BEGIN

    SET NOCOUNT ON;
    
    INSERT INTO app1.CountriesDecl (
        CountryName
    )
    SELECT
        CountryName
    FROM
        inserted;

    -------

    INSERT INTO app1.Capitals (
        CountryName,
        Capital,
        CityInhabitants,
        CityArea
    )
    SELECT
        CountryName,
        CapitalCityName,
        CapitalCityInhabitants,
        CapitalCityArea
    FROM
        inserted;

    -------

    INSERT INTO app1.Countries (
        CountryName,
        Capital,
        Inhabitants,
        Area
    )
    SELECT
        CountryName,
        CapitalCityName,
        CountryInhabitants,
        CountryArea
    FROM
        inserted;

    ----

    INSERT INTO app1.ValidCountries (
        CountryName
    )
    SELECT
        CountryName
    FROM
        inserted;

    -------

END;

/* NOTE: Defining UPDATE and DELETE triggers for the VIEW is an exercise for the reader. */

• When using Entity Framework and Entity Framework Core, remember that approaches like these are ultimately about outsmarting Entity Framework (if not outright hacks), so it's important that you don't ever let EF perform any migrations or generate and run any DDL (CREATE TABLE...) statements based on your Code-First entity model classes.

  • While EF no-longer supports "Database-first" models, you can still use "Code-first from Database" with code-first code-gen like https://github.com/sjh37/EntityFramework-Reverse-POCO-Code-First-Generator (disclaimer: this is my personal favourite code-gen and I'm a contributor to that project).

  • If you run default scaffolding or code-first-codegen on a database using this approach dthen you'll end up with a model containing separate entities for app1.Countries, app1.Capitals, app1.CountriesDecl and app1.ValidCountries - so you should configure your code-gen to filter-out those objects you don't want in your EF model.

    • In this case, I'd exclude all app1.* tables from EF, and instead instruct EF to treat VIEW dbo.Countries as a single entity (which makes sense, as mathematically every 1:1 relationship between 2 entities is the same thing as a single entity defined as a Product Type of those 2 other entities).
    • Because a VIEW does not have a PRIMARY KEY nor any FOREIGN KEY constraints, EF (by default) cannot correctly codegen an entity class from a VIEW, but the aforementioned code-gen tool makes it easy to nudge EF in the right ways (look for the ViewProcessing method, and AddForeignKeys method below it).

  • If you do retain the app1.Countries and app1.Capitals tables as entity types in EF, be aware that having EF perform an INSERT into those two tables will fail unless your code first does an INSERT into app1.CountriesDecl.

  • Or you could add a CREATE TRIGGER Countries/Capitals_Insert ON app1.Countries/app1.Capitals INSTEAD OF INSERT which will perform the IF NOT EXIST ... INSERT INTO app1.CountriesDecl.

  • However EF won't have any problems with UPDATE and DELETE on those 2 tables, at least.

---

Approach 2: Only a single extra TABLE object, but the FK columns are NULL-able - and a VIEW is used as a curtain to hide invalid/incomplete rows.

• If Approach 1 can be summarized as borrowing ideas from the "objects must always be immutable" school-of-thought, then Approach 2 is inspired by languages that allow you to mutate an existing object in-place such that the compiler can verify that each mutation step alters the effective type of the object such that it satisfies some type-constraint.

  • For example, consider this pseudo-TypeScript (because as of 2022, TypeScript still doesn't seem to support/detect when adding properties to a POJsO (thus extending its structural type) is valid and provably extends a variable's static type):

    interface MyResult { readonly name: string; readonly year: number; };
    
    function doSomething() : MyResult {
        let result = {};
    //  return result;                 // Error: Cannot return `result` yet: it doesn't conform to `MyResult` (there's no `name` nor `year` value)
        result.name = "NameGoesHere";  // So let's define `name`.
    //  return result;                 // ERROR: Still cannot return `result` yet: it still doesn't yet have a `year` property.
        result.year = 2022;            // So let's add `year`.
        return result;                 // No error, `result` can now be returned OK because it conforms to `interface MyResult`.
    }
    

• With that concept in-mind, we can have TABLE objects that holds partial/incomplete Country and Capital data which we can freely insert/update/delete because their mutual FOREIGN KEY constraints are NULL-able, see below.

  • The tables are named dbo.CountriesData and dbo.CapitalsData instead of dbo.Countries and dbo.Capitals respectively to indicate that the tables only contain arbitrary "data" rather than valid and correct entities. This is a personal naming-convention of mine. YMMV.
  • As with Approach 1, the VIEW dbo.Countries exists which exposes only valid entities as a single product type.
    • Optionally you could also define additional VIEW objects for Countries and Capitals separately and do the work to make EF treat those as Entities too (though you'll need to do loads more legwork to make INSERT work for each view individually).

• But unlike with Approach 1, the dbo.CapitalsData table now has a composite primary-key, which is a consequence of the OP's specific database design objectives - this might not apply to your database.

  • The composite-PK is necessary to allow dbo.Countries to have a non-NULL CountryName value while not having the FK_CountriesData_to_Capitals constraint enforced. This is necessary because CountryName is also the PK of dbo.CountriesData, so it cannot be NULL. This works because SQL Server only enforces FK constraints when all columns in an FK are non-NULL. If you have a different PK design then this will be different for you.

CREATE TABLE dbo.CountriesData (
   CountryName nvarchar(100) NOT NULL,
   CapitalName nvarchar(255)     NULL,
   Inhabitants bigint        NOT NULL,
   Area        geography     NOT NULL,

   CONSTRAINT PK_CountriesData PRIMARY KEY ( CountryName ),

   CONSTRAINT FK_CountriesData_to_Capitals FOREIGN KEY ( CountryName, CapitalName ) REFERENCES dbo.CapitalsData ( CapitalName )
);

CREATE TABLE dbo.CapitalsData (
    CountryName nvarchar(100) NOT NULL,
    CapitalName nvarchar(255) NOT NULL,
    Inhabitants bigint        NOT NULL,
    Area        geography     NOT NULL,

    CONSTRAINT PK_CapitalsData PRIMARY KEY ( CountryName, CountryName ),

    CONSTRAINT FK_CapitalssData_to_Countries FOREIGN KEY ( CapitalName ) REFERENCES dbo.CountriesData ( CountryName )
);

CREATE VIEW dbo.Countries AS
SELECT
    -- Countries
    cun.Inhabitants     AS CountryInhabitants,
    cun.Area            AS CountryArea,

    -- Capitals
    cap.Capital         AS CapitalCityName,
    cap.CityArea        AS CapitalCityArea,
    cap.CityInhabitants AS CapitalCityInhabitants

FROM
    dbo.CountriesData AS cd
    INNER JOIN dbo.CapitalsData AS cad ON cd.CountryName = cad.CountryName;


CREATE TABLE dbo.ValidCountries (

   -- This TABLE is largely the as in Approach 1. Ensure that all incoming FKs only reference this table and not dbo.CountriesData or dbo.CapitalsData.
   -- NOTE: When using EF, provided to trick EF into treating `VIEW dbo.Countries` as a TABLE then you don't need to include this table in your EF model at all (just be sure to massage all of EF's FK relationships from other entities that initially point to `ValidCountries` to point to the `VIEW dbo.Countries` entity instead.

    CountryName nvarchar(100) NOT NULL,
    CapitalName nvarchar(255) NOT NULL,

    CONSTRAINT PK_ValidCountries PRIMARY KEY ( CountryName ),

    CONSTRAINT FK_ValidCountries_to_Capitals FOREIGN KEY ( CountryName ) REFERENCES dbo.CapitalsData ( CountryName, CapitalName ),
    CONSTRAINT FK_ValidCountries_to_Countries FOREIGN KEY ( CountryName ) REFERENCES dbo.CountriesData ( CountryName )
);

CREATE TRIGGER After_UPDATE_in_CountriesData_then_INSERT_into_ValidCountries_if_valid ON dbo.CountriesData
AFTER UPDATE 
AS
BEGIN
    INSERT INTO dbo.ValidCountries ( CountryName, CapitalName )
    SELECT
        i.CountryName,
        i.CapitalName
    FROM
        inserted.CountryName AS i
        INNER JOIN dbo.CapitalsData AS capd ON -- The JOINs prevents inserting CountryNames for countries that are either invalid or already exist in dbo.ValidCountries.
            capd.CountryName = i.CountryName
            AND
            capd.CapitalName = i.CapitalName
        LEFT OUTER JOIN dbo.ValidCountries AS v ON -- This is a "LEFT ANTI JOIN" due to the WHERE condition below.
            v.CountryName = i.CountryName
    WHERE 
        v.CountryName IS NULL
        AND
        i.CapitalName IS NOT NULL;
END;

CREATE TRIGGER After_INSERT_in_CapitalsData_then_SET_C ON dbo.CapitalsData
AFTER INSERT 
AS
BEGIN
    
    -- Due to the specific design of dbo.CapitalsData, any INSERT will necessarily complete a valid product-type entity, so we can UPDATE dbo.CountriesData to set CapitalName to the correct value.
    UPDATE
        cd
    SET
        cd.CapitalName = inserted.CapitalName
    FROM
        dbo.CountriesData AS cd
        INNER JOIN inserted AS i ON
            cd.CountryName = i.CountryName
            AND
            cd.CapitalName IS NULL
    WHERE
        i.CountryName IS NOT NULL;
 
END;

• For manual DML:
  • To INSERT a new Country...
    1. First INSERT INTO dbo.CountriesData with an initially NULL CapitalName value.
    • This is okay because SQL Server ignores FK constraints when its value (or when at least 1 value in a composite FK) is NULL.
    2. Then INSERT INTO dbo.CapitalsData (or vice-versa, provided CountryName is conversely NULL).
    3. Only after both rows are inserted do you then run UPDATE dbo.CountriesData SET CapitalName = inserted.CapitalName WHERE CountryName = inserted.CountryName.
    4. Whereupon your VIEW dbo.Countries will now expose the now-valid 1:1-related data.
  • DELETE operations must be performed in reverse-order (i.e. first UPDATE to clear the FKs, then DELETE from each table, in any order).
  • UPDATE operations require no special handling.
• I note that you could actually move all the above INSERT logic into an AFTER INSERT trigger on both CountriesData and CapitalsData tables, as this means:
  • That UPDATE into an AFTER INSERT trigger on dbo.CapitalsData! (and vice-versa) - but be sure to also add the check that WHERE inserted.CountryName IS NOT NULL - but if you do that then your client's SQL code only needs to do two INSERT statements and one of the two AFTER INSERT triggers will handle the rest automatically, but only if the data is finally valid - whereupon it will be visible in VIEW dbo.Countries.
  • This approach plays nicer with EF, as you don't need to faff around with the CountriesDecl table, so doing individual INSERT ops into dbo.CountriesData and dbo.CapitalsData won't fail - but remember that there's no 1:1 relationship between those two tables/entities.

Answer 6

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.

Answer 7

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