How to Create a real one-to-one relationship in SQL Server
SqlSql ServerEntity FrameworkOne to-OneSql Problem Overview
Sql Solutions
Solution 1 - Sql
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.
Solution 2 - Sql
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
Solution 3 - Sql
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).
Solution 4 - Sql
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.
Solution 5 - Sql
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
Solution 6 - Sql
> 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:
- To have FK constraints to both of the
1:1
member tables (don't forget you can also have three or more1:1
tables too!): so a row inValidCountries
cannot exist unless all required related data exists in their respective tables. - 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.
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
:
Approach 1: Use two more -
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 anINNER JOIN
of the 3 (or more) backing tables, while also supportingINSERT/UPDATE/DELETE/MERGE
DML operations.- This works great with Entity Framework as EF is perfectly happy to pretend that a
VIEW
is aTABLE
. 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.
- This is because, unfortunately,
- This works great with Entity Framework as EF is perfectly happy to pretend that a
-
The three tables are:
- Table 1: The "forward-declaration table" with only the PK value.
- In the OP's example (of
Countries
andCapitals
), this would be a table named likeCountryDeclarations
(orCountryDecl
for short) and stores onlyCountryName
values, which is the shared PK for both theCountries
andCapitals
tables).
- 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
withCountryName
as the table's PK and its FK to only the forward-declaration table.TABLE Capitals
withCountryName
as the table's PK and its FK to only the forward-declaration table.
- In the OP's example this would be 2 tables:
- 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 toCountryDecl
and separateFK
columns toCountries
andCapitals
.
Here's a database-diagram of this approach:
-
When querying data from the
Countries
and/orCapitals
tables, provided provided you alwaysINNER JOIN
withValidCountries
then you get hard guarantees that you're always querying valid data.- Or just use the
VIEW
to get theJOIN
already-done for you.
- Or just use the
-
Remember that the
1:1
relationship is not enforced between the constituentCountries
andCapitals
tables: this is necessary otherwise there would be a chicken vs. egg problem onINSERT
.- Though if you're sure you'll always
INSERT
intoCountries
beforeCapitals
(andDELETE
in the reverse order) you could add anFK
constraint fromCapitals
directly toCountries
, but this doesn't really add any benefits because theCountries
table cannot provide guarantees that a correspondingCapitals
row will exist.
- Though if you're sure you'll always
-
This design is compatible with
IDENTITY
PKs too, just remember that only the forward-declaration table will have theIDENTITY
column, all other tables will have normalint
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
andapp1.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 treatVIEW dbo.Countries
as a single entity (which makes sense, as mathematically every1: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 aPRIMARY KEY
nor anyFOREIGN KEY
constraints, EF (by default) cannot correctly codegen an entity class from aVIEW
, but the aforementioned code-gen tool makes it easy to nudge EF in the right ways (look for theViewProcessing
method, andAddForeignKeys
method below it).
- In this case, I'd exclude all
-
If you do retain the
app1.Countries
andapp1.Capitals
tables as entity types in EF, be aware that having EF perform anINSERT
into those two tables will fail unless your code first does anINSERT
intoapp1.CountriesDecl
. -
Or you could add a
CREATE TRIGGER Countries/Capitals_Insert ON app1.Countries/app1.Capitals INSTEAD OF INSERT
which will perform theIF NOT EXIST ... INSERT INTO app1.CountriesDecl
. -
However EF won't have any problems with
UPDATE
andDELETE
on those 2 tables, at least.
-
TABLE
object, but the FK
columns are NULL
-able - and a VIEW
is used as a curtain to hide invalid/incomplete rows.
Approach 2: Only a single extra -
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/incompleteCountry
andCapital
data which we can freely insert/update/delete because their mutualFOREIGN KEY
constraints areNULL
-able, see below.- The tables are named
dbo.CountriesData
anddbo.CapitalsData
instead ofdbo.Countries
anddbo.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 makeINSERT
work for each view individually).
- Optionally you could also define additional
- The tables are named
-
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 theFK_CountriesData_to_Capitals
constraint enforced. This is necessary becauseCountryName
is also the PK ofdbo.CountriesData
, so it cannot beNULL
. 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.
- The composite-PK is necessary to allow
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...- First
INSERT INTO dbo.CountriesData
with an initiallyNULL
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
.
- Then
INSERT INTO dbo.CapitalsData
(or vice-versa, providedCountryName
is converselyNULL
). - Only after both rows are inserted do you then run
UPDATE dbo.CountriesData SET CapitalName = inserted.CapitalName WHERE CountryName = inserted.CountryName
. - Whereupon your
VIEW dbo.Countries
will now expose the now-valid1:1
-related data.
- First
DELETE
operations must be performed in reverse-order (i.e. firstUPDATE
to clear the FKs, thenDELETE
from each table, in any order).UPDATE
operations require no special handling.
- To
-
I note that you could actually move all the above
INSERT
logic into anAFTER INSERT
trigger on bothCountriesData
andCapitalsData
tables, as this means:- That
UPDATE
into anAFTER INSERT
trigger ondbo.CapitalsData
! (and vice-versa) - but be sure to also add the check thatWHERE inserted.CountryName IS NOT NULL
- but if you do that then your client's SQL code only needs to do twoINSERT
statements and one of the twoAFTER INSERT
triggers will handle the rest automatically, but only if the data is finally valid - whereupon it will be visible inVIEW dbo.Countries
. - This approach plays nicer with EF, as you don't need to faff around with the
CountriesDecl
table, so doing individualINSERT
ops intodbo.CountriesData
anddbo.CapitalsData
won't fail - but remember that there's no1:1
relationship between those two tables/entities.
- That
Solution 7 - Sql
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.
Solution 8 - Sql
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)
)