• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 359
  • Last Modified:

find origin of key constraint

I have a constraint on a table

UK_Lien_AssetTaskID

but I cannot find where the constraint is being applied.


Msg 2627, Level 14, State 1, Line 1
Violation of UNIQUE KEY constraint 'UK_Lien_AssetTaskID'. Cannot insert duplicate key in object 'dbo.Lien'. The duplicate key value is (2841370, 1).
The statement has been terminated.


Is there a way to hunt down what is creating this constraint?


For example, I did a "DROP and CREATE" script on the "Lien" table, but I did not see any constraints with:

UK_Lien_AssetTaskID

being referenced:


USE [PP]
GO

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Lien_AssetID]') AND parent_object_id = OBJECT_ID(N'[dbo].[Lien]'))
ALTER TABLE [dbo].[Lien] DROP CONSTRAINT [FK_Lien_AssetID]
GO

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Lien_AssetTaskID]') AND parent_object_id = OBJECT_ID(N'[dbo].[Lien]'))
ALTER TABLE [dbo].[Lien] DROP CONSTRAINT [FK_Lien_AssetTaskID]
GO

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Lien_RoleID]') AND parent_object_id = OBJECT_ID(N'[dbo].[Lien]'))
ALTER TABLE [dbo].[Lien] DROP CONSTRAINT [FK_Lien_RoleID]
GO

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_Lien_LienSID]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[Lien] DROP CONSTRAINT [DF_Lien_LienSID]
END

GO

USE [PP]
GO

/****** Object:  Table [dbo].[Lien]    Script Date: 05/20/2013 17:32:39 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Lien]') AND type in (N'U'))
DROP TABLE [dbo].[Lien]
GO

USE [PP]
GO

/****** Object:  Table [dbo].[Lien]    Script Date: 05/20/2013 17:32:39 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Lien](
	[LienID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
	[LienSID] [int] NOT NULL,
	[AssetID] [int] NOT NULL,
	[AssetSID] [int] NOT NULL,
	[AssetTaskID] [int] NOT NULL,
	[AssetTaskSID] [int] NOT NULL,
	[LienType] [int] NOT NULL,
	[LienHolder] [varchar](50) NOT NULL,
	[PayOffAmt] [decimal](18, 2) NOT NULL,
	[LienDetails] [varchar](max) NOT NULL,
 CONSTRAINT [PK_Lien] PRIMARY KEY CLUSTERED 
(
	[LienID] ASC,
	[LienSID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 85) ON [PRIMARY],
 CONSTRAINT [UK_Lien_AssetTaskID] UNIQUE NONCLUSTERED 
(
	[AssetTaskID] ASC,
	[AssetTaskSID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 85) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[Lien]  WITH NOCHECK ADD  CONSTRAINT [FK_Lien_AssetID] FOREIGN KEY([AssetID], [AssetSID])
REFERENCES [dbo].[Asset] ([AssetID], [AssetSID])
NOT FOR REPLICATION 
GO

ALTER TABLE [dbo].[Lien] CHECK CONSTRAINT [FK_Lien_AssetID]
GO

ALTER TABLE [dbo].[Lien]  WITH NOCHECK ADD  CONSTRAINT [FK_Lien_AssetTaskID] FOREIGN KEY([AssetTaskID], [AssetTaskSID])
REFERENCES [dbo].[AssetTask] ([AssetTaskID], [AssetTaskSID])
NOT FOR REPLICATION 
GO

ALTER TABLE [dbo].[Lien] CHECK CONSTRAINT [FK_Lien_AssetTaskID]
GO

ALTER TABLE [dbo].[Lien]  WITH NOCHECK ADD  CONSTRAINT [FK_Lien_RoleID] FOREIGN KEY([LienType])
REFERENCES [dbo].[Lookup] ([LookupID])
NOT FOR REPLICATION 
GO

ALTER TABLE [dbo].[Lien] CHECK CONSTRAINT [FK_Lien_RoleID]
GO

ALTER TABLE [dbo].[Lien] ADD  CONSTRAINT [DF_Lien_LienSID]  DEFAULT (right(@@servername,(1))) FOR [LienSID]
GO

Open in new window

0
Tom Knowlton
Asked:
Tom Knowlton
1 Solution
 
Paul JacksonSoftware EngineerCommented:
lines 60 - 65 in the code posted above shows the creation of the constraint.
0
 
Tom KnowltonWeb developerAuthor Commented:
It sure does.

Thanks for your eagle eyes.  I missed it.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now