How do you add Foreign Key Relationships?

Question

I'm working with an existing SQL 2005 database that was not implemented with FK relationships between tables. I tried to add the relationships with a database diagram and my application immediately blew up trying to edit or insert any data that is tied to the new 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]

The person_campaigns table is where a person, campaign, and disposition are tied together. Can you please provide the appropriate SQL syntax for adding the proper FK relationships between these entities?

EDIT

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

Since I don't have SQL Server on this PC and I don't memorize the syntax, the easiest thing to do is to create two new test tables, create TableA with an ID field, TableB with a field that is a FK of TableA.ID, and then script out TableB to see the ADD CONSTRAINT syntax. Do this using SQL Server Management Studio via a database diagram.

However, if you were able to successfully create the FKs in a data diagram, and you only can't add or update records, I believe something else is wrong. Script out the person_campaigns table and post the code.

Answer 2

If you have to add them after the table is created the syntax is

 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 3

the easiest way to do it is through the database diagram editor; do them one at a time and save the diagram to affect the tables after each connection is made. If it "blows up" it is most likely because the tables contain foreign-key values that do not exist; you'll have to clean these up first.

Answer 4

Suppose I had two tables that should have had a foreign key but did not. The first thing to do is check to see if there will be a data problem if I set a foreign key.

something like the below code would get you the records in the child table that do not have a match in the parent table.

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

Once you can see what is wrong with the existing data, then you need to create a way to fix it. The fix will vary depending on what data you have that has no relationship to the Parent table and what the tables represent. Suppose your parent table contained a VIN number for automobiles as the PK. If your child table contains the cars that were worked on by the shop, you would want to fix the issue by adding the nonexisting VINS to the primary table becasue you wouldn't want to lose the history of what was worked on. There are other structures where you might want to simply delete the records that don't match in child table because they are meaningless. In other circumstances you might want to update those records to some default value (perhaps a customer in the customer table called unknown). In still other circumstances, you might need to go to audit tables or backups to find the value of the PK that was deleted without the associated child records being deleted. The actual way to fix this problem is highly dependent on what the data is used for and how important it is to retain all historical records. Since you should never delete any record that might be related to a financial transaction for legal (and accounting) reasons, you need to be most careful with those.

After fixing all the data, then you run the code to create the FK constraint.