Insert Into Ignoring Constraints

Inigo Montoya 586 Reputation points
2021-04-22T14:28:19.49+00:00

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]
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,535 questions
0 comments No comments
{count} votes

Accepted answer
  1. Tom Cooper 8,461 Reputation points
    2021-04-22T14:38:05.59+00:00

    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


0 additional answers

Sort by: Most helpful