如何添加外键关系？

Question

我正在使用现有的 SQL 2005 数据库，该数据库未使用表之间的 FK 关系来实现。 我尝试添加与数据库图表的关系，但我的应用程序在尝试编辑或插入与新 FK 相关的任何数据时立即崩溃。

dbo.person [person_id | firstname | lastname | dateofbirth]
dbo.campaign [campaign_id | campaign_description]
dbo.disposition [disposition_id | disposition_description]
dbo.person_campaigns [person_campaign_id | person_id | campaign_id | disposition_id]

person_campaigns 表是将人员、营销活动和处置联系在一起的地方。 您能否提供适当的 SQL 语法来在这些实体之间添加正确的 FK 关系？

编辑

CREATE TABLE [dbo].[person_campaigns](
    [person_campaigns_id] [int] IDENTITY(1,1) NOT NULL,
    [person_id] [int] NOT NULL,
    [d_campaign_id] [int] NOT NULL,
    [d_physician_disposition_id] [int] NULL,
 CONSTRAINT [PK_person_campaigns] PRIMARY KEY CLUSTERED 
(
    [person_campaigns_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[d_campaign](
    [d_campaign_id] [int] IDENTITY(1,1) NOT NULL,
    [name] [varchar](50) NULL,
    [year] [int] NULL,
    [isactive] [bit] NOT NULL,
 CONSTRAINT [PK_d_campaign] PRIMARY KEY CLUSTERED 
(
    [d_campaign_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[d_campaign] ADD  CONSTRAINT [DF_d_campaign_isactive]  DEFAULT ((1)) FOR [isactive]
GO

CREATE TABLE [dbo].[d_disposition](
    [d_disposition_id] [int] IDENTITY(1,1) NOT NULL,
    [name] [varchar](50) NULL,
    [isactive] [bit] NOT NULL,
 CONSTRAINT [PK_d_disposition] PRIMARY KEY CLUSTERED 
(
    [d_disposition_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[d_disposition] ADD  CONSTRAINT [DF_d_disposition_isactive]  DEFAULT ((1)) FOR [isactive]
GO

CREATE TABLE [dbo].[person](
    [person_id] [int] IDENTITY(1,1) NOT NULL,
    [firstname] [varchar](30) NULL,
    [lastname] [varchar](30) NULL,
    [dateofbirth] [datetime] NULL
 CONSTRAINT [PK__person__0BC6C43E] PRIMARY KEY CLUSTERED 
(
    [person_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

Answer 1

由于我这台电脑上没有 SQL Server，而且我不记得语法，所以最简单的事情就是创建两个新的测试表，创建带有 ID 字段的 TableA，以及带有 TableA 的 FK 字段的 TableB .ID，然后编写 TableB 脚本以查看 ADD CONSTRAINT 语法。 使用 SQL Server Management Studio 通过数据库图表执行此操作。

但是，如果您能够在数据图中成功创建 FK，并且只能添加或更新记录，我相信还有其他问题。 编写 person_campaigns 表的脚本并发布代码。

Answer 2

最简单的方法是通过数据库图表编辑器； 一次执行一个操作，并在每次连接完成后保存图表以影响表。 如果它“爆炸”，很可能是因为表包含不存在的外键值； 你必须先清理这些。

Answer 3

如果您必须在创建表后添加它们，语法是

 create table  person (person_id int primary key 
,firstname varchar(10)
, lastname varchar(10)
, dateofbirth varchar(10))

create table campaign (campaign_id int primary key
, campaign_description varchar(10))
create table  disposition (disposition_id int primary key  
,disposition_description varchar(10))

create table person_campaigns(person_campaign_id int
,person_id int, campaign_id int ,disposition_id int)
go
alter table person_campaigns  add Constraint 
fk_person_campaigns_person_id
Foreign Key (person_id) References person(person_id)
GO

alter table person_campaigns add Constraint 
fk_person_campaigns_campaign_id
Foreign Key (campaign_id) References campaign(campaign_id)
GO

alter table person_campaigns add Constraint
fk_person_campaigns_disposition_id
Foreign Key (disposition_id) References disposition(disposition_id) 

GO

Answer 4

假设我有两个应该有外键但没有的表。 首先要做的就是检查一下如果我设置了外键，是否会出现数据问题。

类似下面的代码将获取子表中与父表中没有匹配项的记录。

select t2.FKField, t2.PKfield from table2 t2
left join Table1 t1 on t2.fkfield = t1.pkfield
where t1.pkfield is null

一旦您发现现有数据存在问题，您就需要创建一种方法来修复它。 修复方法将根据您拥有的与父表无关的数据以及表所代表的内容而有所不同。 假设您的父表包含汽车的 VIN 号作为 PK。 如果您的子表包含商店加工过的汽车，您可能希望通过将不存在的 VINS 添加到主表来解决问题，因为您不想丢失加工过的历史记录。 还有其他结构，您可能只想删除子表中不匹配的记录，因为它们没有意义。 在其他情况下，您可能希望将这些记录更新为某个默认值（可能是客户表中名为未知的客户）。 在其他情况下，您可能需要转到审核表或备份来查找已删除的 PK 的值，但未删除关联的子记录。 解决此问题的实际方法在很大程度上取决于数据的用途以及保留所有历史记录的重要性。 由于出于法律（和会计）原因，您永远不应该删除可能与金融交易相关的任何记录，因此您需要对这些记录非常小心。

修复所有数据后，运行代码来创建 FK 约束。