Solved

find origin of key constraint

Posted on 2013-05-20
2
346 Views
Last Modified: 2013-05-20
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
Comment
Question by:knowlton
2 Comments
 
LVL 29

Accepted Solution

by:
Paul Jackson earned 500 total points
ID: 39182978
lines 60 - 65 in the code posted above shows the creation of the constraint.
0
 
LVL 5

Author Comment

by:knowlton
ID: 39182985
It sure does.

Thanks for your eagle eyes.  I missed it.
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question