Inserting data into SQL Server 2012

Question

I'm working with SQL Server 2012

I have two tables, Person and Couple, Person has a foreign key from Couple and Couple has foreign key from 'Person'.

When I try to insert data into each one, an error occurs because each table has FK from the other table, and both are empty initially.

CREATE TABLE [dbo].[Couple](
    [CoupleId] [int] IDENTITY(1,1) NOT NULL,
    [HusbandPersonId] [int] NOT NULL,
    [WifePersonId] [int] NOT NULL,
    [StartDate] [date] NOT NULL,
    [EndDate] [date] NOT NULL,
 CONSTRAINT [PK_Couple] PRIMARY KEY CLUSTERED 
(
    [CoupleId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[Person](
    [PersonId] [int] IDENTITY(1,1) NOT NULL,
    [ChildOfCoupleId] [int] NOT NULL,
    [CityOfBirth] [int] NOT NULL,
    [CityOfPermanentResidence] [int] NOT NULL,
    [CityOfCurrentResidence] [int] NOT NULL,
    [FirstName] [varchar](20) NOT NULL,
    [LastName] [varchar](20) NOT NULL,
    [BirthDate] [date] NOT NULL,
    [DeathDate] [date] NOT NULL,
    [IsMale] [bit] NOT NULL,
 CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED 
(
    [PersonId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY

ALTER TABLE [dbo].[Couple]  WITH CHECK ADD  CONSTRAINT [FK_Couple_Person] FOREIGN KEY([HusbandPersonId])
REFERENCES [dbo].[Person] ([PersonId])
GO
ALTER TABLE [dbo].[Couple] CHECK CONSTRAINT [FK_Couple_Person]
GO
ALTER TABLE [dbo].[Couple]  WITH CHECK ADD  CONSTRAINT [FK_Couple_Person1] FOREIGN KEY([WifePersonId])
REFERENCES [dbo].[Person] ([PersonId])
GO
ALTER TABLE [dbo].[Couple] CHECK CONSTRAINT [FK_Couple_Person1]
GO
ALTER TABLE [dbo].[Person]  WITH CHECK ADD  CONSTRAINT [FK_Person_Couple] FOREIGN KEY([ChildOfCoupleId])
REFERENCES [dbo].[Couple] ([CoupleId])
GO
ALTER TABLE [dbo].[Person] CHECK CONSTRAINT [FK_Person_Couple]
GO

How to solve this ?

why exctly do yu need to give both tables each others foreign keys? — Azzy, Dec 24 '12 at 08:10
Some DBMS allow you to define constraints as *deferred* so that they are evaluated when you commit the transaction, not when you run the `insert`. Not sure if SQL Server 2012 has such a feature though. — , Dec 24 '12 at 08:24
Make the `ChildOfCoupleId` on `Person` nullable - after all, you don't always have all those pieces of information at hand. That way, you can insert `Person` into the table without having to specify the `CoupleId` just yet (can be added later on, if information becomes available) — marc_s, Dec 24 '12 at 08:27
I solve it by Temporarily Disabling Constraint FK_Couple_Person and FK_Couple_Person1 ,, but is there any way better? — Mohamad Ghanem, Dec 24 '12 at 08:49
Check my answer on a similar question for MySQL: **[In SQL, is it OK for two tables to refer to each other?](http://stackoverflow.com/questions/10446641/in-sql-is-it-ok-for-two-tables-to-refer-to-each-other/10458105#10458105)**. There are links for other DBMS there but I'm afraid that SQL-Server does not allow deferrable constraints. So, your best option may be to redesign the schema with an additional table, so you have no circular paths in the FK relationships. — ypercubeᵀᴹ, Dec 25 '12 at 22:57

ErikE · Accepted Answer · 2012-12-27T21:11:15.077

You can't require that all people have parents, because you would hit an infinite loop of parent-recursion. That is, you must have a parentless "Adam and Eve" couple to be the parents of all children.

Now, you could make your ChildOfCoupleId column NULLable as one solution. But frankly I think using CoupleId is not optimal. Instead, use MotherId and FatherId. This is ultimately better as it:

Doesn't require you to know both elements--if you know only one parent it can be entered.
Simplifies the FK relationships by making the FKs in Person point to Person rather than another table which must be queried to determine the person.
Doesn't force you to have a defined start and stop date for the parent Couple's involvement--I notice you aren't calling it Marriage but you are using Husband and Wife. So if you want to model the real, broken, world it's kind of a must to not necessarily know that information.
Disentangles the child's parentage from exactly when the couple was together. If a couple marries, divorces, and remarries, then you are specifying during which marriage the child was conceived. For simple lineage this is unnecessary--you just need to know the parents. What if the marriage is nullified, but they managed to conceive a child? How will you store the Parent-Child information then? In our world parentage is a distinct fact from couple status.

Finally, it is often best to avoid NULLable columns. You can accomplish this by creating a new table such as Parentage:

CREATE TABLE dbo.Parentage (
   PersonID int NOT NULL
      CONSTRAINT FK_Parentage_Person
      FOREIGN KEY REFERENCES dbo.Person (PersonID),
   ParentID int NOT NULL,
   ParentIsMale bit NOT NULL,
   CONSTRAINT PK_Parentage PRIMARY KEY CLUSTERED (PersonID, ParentIsMale),
   CONSTRAINT FK_Parentage_Parent FOREIGN KEY (ParentID, ParentIsMale)
      REFERENCES dbo.Person (ParentID, IsMale)
);

You'll have to add a unique constraint or unique index on (ParentId, IsMale) in the Person table in order to create an FK to it. Using IsMale as part of the PK/FK makes sure you get exactly one mother and exactly one father.

While this design may seem clunky, in the long run it will provide true benefits. The knowledge of a person's father or mother is encoded via insertion of a row rather than updating a NULLable column. If there is no row, then the parent is unknown.

This design also is easily adapted for recording different kinds of parents: genetic, birth, adoptive. A person could have 2 genetic parents and a birth mother. Then through a series of disastrous events have all parents killed and be adopted (even more than once). So you would need to change the PK then add ParentTypeID, FromDate, and ToDate columns to the table. Further adjustments would be necessary to restrict improper relationships from being denoted (such as "birth father").

score 0 · Answer 2 · answered Dec 24 '12 at 09:04

I think you must seriously consider changing this schema. Besides the problems on inserting data, you will have the exact same problem when you try to delete data. The solution marc_c gives, using null helps some, but is not the best thing to do. The best you could do is bring another table into you deign and make there your back references to couple.

Inserting data into SQL Server 2012

2 Answers2