sql - 你如何添加外键关系？

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

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