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 ?

Answer 1

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:

1. Doesn't require you to know both elements--if you know only one parent it can be entered.

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

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

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