Can't add Foreign Key constraint

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Can't add Foreign Key constraint

Author

Topic

lagyossarian
Starting Member

2 Posts

Posted - 2009-09-18 : 14:06:34

I have two tables -- Expenses and CostCodes. I want to have a foreign key in the Expense table, CostCodeID for the primary key of the CostCodes table, ID. When I attempt to add the relationship, I get the following error:

The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_Expenses_CostCodes". The conflict occurred in database "DWOR", table "dbo.CostCodes", column 'ID'.

Here are the SQL scripts for my tables and the Alter statemtn adding the Constraint:

[CODE]

USE [DWOR]
GO

/****** Object: Table [dbo].[Expenses] Script Date: 09/18/2009 11:56:05 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Expenses](
[ID] [bigint] NOT NULL,
[UserID] [nvarchar](15) NULL,
[ExpenseDate] [datetime] NOT NULL,
[EnteredDate] [datetime] NULL,
[CostCodeID] [nvarchar](6) NULL,
[ExpenseAmount] [money] NOT NULL,
[InvoiceNumber] [nvarchar](12) NULL,
[VendorID] [bigint] NULL,
[VendorName] [nvarchar](50) NULL,
[PropertyID] [bigint] NULL,
[PropertyName] [nvarchar](50) NULL,
[ForemanID] [int] NULL,
[ForemanName] [nvarchar](50) NULL,
[Notes] [text] NULL,
[ReportPeriod] [nvarchar](6) NULL,
CONSTRAINT [PK_Expenses] PRIMARY KEY CLUSTERED
(
[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] TEXTIMAGE_ON [PRIMARY]

GO

ALTER TABLE [dbo].[Expenses] WITH CHECK ADD CONSTRAINT [FK_Expenses_Foremen] FOREIGN KEY([ForemanID])
REFERENCES [dbo].[Foremen] ([ID])
GO

ALTER TABLE [dbo].[Expenses] CHECK CONSTRAINT [FK_Expenses_Foremen]
GO

ALTER TABLE [dbo].[Expenses] WITH CHECK ADD CONSTRAINT [FK_Expenses_Properties] FOREIGN KEY([PropertyID])
REFERENCES [dbo].[Properties] ([ID])
GO

ALTER TABLE [dbo].[Expenses] CHECK CONSTRAINT [FK_Expenses_Properties]
GO

ALTER TABLE [dbo].[Expenses] WITH CHECK ADD CONSTRAINT [FK_Expenses_Users] FOREIGN KEY([UserID])
REFERENCES [dbo].[Users] ([UserID])
GO

ALTER TABLE [dbo].[Expenses] CHECK CONSTRAINT [FK_Expenses_Users]
GO

ALTER TABLE [dbo].[Expenses] WITH CHECK ADD CONSTRAINT [FK_Expenses_Vendors] FOREIGN KEY([VendorID])
REFERENCES [dbo].[Vendors] ([ID])
GO

ALTER TABLE [dbo].[Expenses] CHECK CONSTRAINT [FK_Expenses_Vendors]
GO

USE [DWOR]
GO

/****** Object: Table [dbo].[CostCodes] Script Date: 09/18/2009 11:53:10 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[CostCodes](
[ID] [nvarchar](6) NOT NULL,
[Gen] [nvarchar](3) NOT NULL,
[SubCode] [nvarchar](3) NOT NULL,
[Description] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_CostCodes] PRIMARY KEY CLUSTERED
(
[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

ALTER TABLE [dbo].[Expenses] WITH CHECK ADD CONSTRAINT [FK_Expenses_CostCodes] FOREIGN KEY([CostCodeID])
REFERENCES [dbo].[CostCodes] ([ID])
GO

ALTER TABLE [dbo].[Expenses] CHECK CONSTRAINT [FK_Expenses_CostCodes]
GO

[/CODE]

Thanks in advance for any assistance rendered!

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-09-20 : 09:31:11

ALTER TABLE EXPENSES ADD CONSTRAINT FK_EXPENSES_COSTCODES FOREIGN KEY(COSTCODEID) REFERENCES COSTCODES(ID)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources