将数据插入SQL Server 2012

Question

我正在使用SQL Server 2012。

我有两个表，Person和Couple，Person有一个从Couple来的外键，而Couple则有一个从Person来的外键。

当我尝试向每个表中插入数据时，会出现错误，因为每个表都具有对另一个表的外键引用，且初始情况下两个表均为空。

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

如何解决这个问题？

- Mohamad Ghanem

---

为什么你需要给两个表彼此的外键？ - Azzy

最好您发布表格定义。 - ypercubeᵀᴹ

一些数据库管理系统允许您将约束定义为“延迟”，以便在提交事务时进行评估，而不是在运行“插入”时进行评估。不确定 SQL Server 2012 是否具有此功能。 - user330315

@ypercube代码已插入问题中。 - Mohamad Ghanem

将 Person 上的 ChildOfCoupleId 变为可空 - 毕竟，您并不总是拥有所有这些信息。这样，您可以将 Person 插入表中，而无需立即指定 CoupleId（如果信息变得可用，则可以稍后添加）。 - marc_s

Answer 1

你不能要求所有人都有父母，因为你会陷入一个无限循环的父母递归中。也就是说，必须有一个没有父母的“亚当和夏娃”夫妇作为所有孩子的父母。

现在，你可以将你的`ChildOfCoupleId`列设置为可空值作为一种解决方案。但老实说，我认为使用`CoupleId`并不是最佳选择。相反，应该使用`MotherId`和`FatherId`。这样做的好处在于：

1. 不需要知道两个元素--如果你只知道一个父母，那么它可以被输入。

2. 通过使`Person`中的FK指向`Person`而不是另一个必须查询以确定人员的表，简化了FK关系。

3. 不强制你为父母`Couple`的参与指定开始和结束日期--我注意到你没有称之为`Marriage`但你使用了`Husband`和`Wife`。因此，如果你想模拟真实且破碎的世界，这是一种不一定要知道信息的必须方式。

4. 将孩子的亲子关系与夫妻何时在一起分离开来。如果一对夫妻结婚，离婚，再次结婚，那么你就要确定孩子是在哪个婚姻中受孕的。对于简单的血统来说，这是不必要的--你只需要知道父母。如果婚姻被废除了，但他们设法怀孕了一个孩子，你将如何存储父母-子女信息呢？在我们的世界里，亲子关系是一个独立的事实，与夫妇的状态无关。

最后，避免使用可空列通常是最好的。你可以通过创建一个新表，例如`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)
);

为了创建一个FK，您需要在Person表中的(ParentId, IsMale)上添加唯一约束或唯一索引。将IsMale作为PK/FK的一部分可以确保您获得一个母亲和一个父亲。

虽然这个设计看起来有些笨重，但从长远来看，它会带来真正的好处。通过插入行来编码一个人的父亲或母亲的信息，而不是更新可空列。如果没有行，则父母未知。

这个设计也很容易适应记录不同类型的父母：基因、出生、收养等。一个人可能有两个基因父母和一个生母。然后经过一系列灾难性事件，所有的父母都被杀害并被收养（甚至不止一次）。因此，您需要改变PK，然后向表中添加ParentTypeID、FromDate和ToDate列。还需要进行进一步的调整，以防止不当的关系被表示（比如“生父”）。

- ErikE

Answer 2

我认为你必须认真考虑更改这个schema。除了插入数据时的问题之外，当你尝试删除数据时也会遇到完全相同的问题。Marc_c提供的使用null的解决方案可以帮助一些，但并不是最好的选择。你能做的最好的事情是在你的设计中引入另一个表，并在那里构建你的反向引用以进行耦合。

- George Mavritsakis