question

InigoMontoya-1790 avatar image
0 Votes"
InigoMontoya-1790 asked TomCooper-6989 commented

Insert Into Ignoring Constraints

For an excercise using MS SQL Server 2016, I need to insert data into a table ignoring constraints.
I have tried these commands, but still receive error when attempting to insert:
EXEC sp_MSforeachtable "ALTER TABLE Sale_Items_Parts NOCHECK CONSTRAINT all"
ALTER TABLE Sale_Items_Parts NOCHECK CONSTRAINT ALL

The error I receive is:
Violation of UNIQUE KEY constraint 'AK_Item_Parts_Sku'. Cannot insert duplicate key in object 'dbo.Sale_Items_Parts'

How can I insert these duplicate values ignoring the constraints?


The table has these constraints on it
CONSTRAINT [PK_Sale_Items_Parts] 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],
CONSTRAINT [AK_Item_Parts_Sku] UNIQUE NONCLUSTERED
(
[Item_Sku] 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].[Sale_Items_Parts]  WITH NOCHECK ADD  CONSTRAINT [FK_Parts_UOM_UOMId] FOREIGN KEY([UOMId])
 REFERENCES [dbo].[UOM] ([Id])
 GO
    
 ALTER TABLE [dbo].[Sale_Items_Parts] NOCHECK CONSTRAINT [FK_Parts_UOM_UOMId]
sql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

TomCooper-6989 avatar image
0 Votes"
TomCooper-6989 answered TomCooper-6989 commented

You cannot disable UNIQUE constraints. Your inserts are violating the unique constraint AK_Item_Parts_Sku. If you need to insert duplicate keys for some reason, you would have to drop the constraint, not disable it.
Tom

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@TomCooper-6989 -- how can I drop before the insert, then add back after the insert? (again this is a test excercise, not working with production or live data at all)

0 Votes 0 ·

If the unique constraint exists, then you cannot have duplicate keys in the table. You can create a unique index with the IGNORE_DUP_KEY = ON option. But that would not allow you to actually succeed in inserting a duplicate key. If you have that option on and you try to insert a new row which is a duplicate you will get a warning message and that row will not be inserted.

0 Votes 0 ·