Link to home
Start Free TrialLog in
Avatar of Tom Knowlton
Tom KnowltonFlag for United States of America

asked on

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

ASKER CERTIFIED SOLUTION
Avatar of Paul Jackson
Paul Jackson
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Tom Knowlton

ASKER

It sure does.

Thanks for your eagle eyes.  I missed it.