Is there an easier way to do a "Cascading Insert" in SQL Server 2016?-CodePudding

I inherited SQL code that I need to work on that was set up similar to the following:

CREATE TABLE [dbo].[Ni](
    [FooID] [int] IDENTITY(1,1) NOT NULL,
    [Bar] [nvarchar](60) NULL,
    [LocationID] [int] NULL,
    [Thing1] [float] NULL
CONSTRAINT [PK_Ni] PRIMARY KEY CLUSTERED 
(
    [FooID] ASC
);

CREATE UNIQUE NONCLUSTERED INDEX [UQ_LocationBar] ON [dbo].[Ni]
(
    [LocationID] ASC,
    [Bar] ASC
);

CREATE TABLE [dbo].[Ni_Two](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [FooID] [int] NOT NULL,
    [Thing2] [int] NOT NULL,
    [Thing3] [int] NOT NULL
CONSTRAINT [PK_Ni_Two] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
);

ALTER TABLE [dbo].[Ni_Two]  WITH CHECK ADD  CONSTRAINT [FK_NiTwo_FooID] FOREIGN KEY([FooID])
REFERENCES [dbo].[Ni] ([FooID]);

CREATE TABLE [dbo].[KillMe](
    [ID] [int] NOT NULL,
    [FooID] [int] NULL,
    [Thing4] [int] NOT NULL,
    [Thing5] [int] NOT NULL
PRIMARY KEY CLUSTERED 
(
    [ID] ASC
);

ALTER TABLE [dbo].[KillMe]  WITH NOCHECK ADD  CONSTRAINT [FK_KillMe_FooID] FOREIGN KEY([FooID])
REFERENCES [dbo].[Ni] ([FooID]);


CREATE TABLE [dbo].[PleaseStop](
    [ID] [int] NOT NULL,
    [KillMeID] [int] NOT NULL,
    [Thing7] [int] NOT NULL,
    [Thing8] [int] NOT NULL
PRIMARY KEY CLUSTERED 
(
    [Id] ASC
);

ALTER TABLE [dbo].[PleaseStop]  WITH CHECK ADD  CONSTRAINT [FK_PleaseStop_KillMe] FOREIGN KEY([KillMeID])
REFERENCES [dbo].[KillMe] ([ID]);

At issue is that with this design is with [Ni].dbo.[Bar]. That unique constraint is put in there as a requirement. Every FooID is unique, and every Bar assigned to LocationID must be unique.

Now the requirements have changed. With each quarterly import there will be a few entries where the Bar field must be updated.

I have tried:

   UPDATE dbo.[Ni]
   SET   Bar                 = Imp.Bar
       , LocationID          = Imp.LocationID
       , Thing1              = Imp.Thing1
   FROM dbo.[Ni]
       INNER JOIN ImportData Imp ON [Ni].FooID = Imp.FooID

This will give me a Violation of UNIQUE KEY constraint error.

I don't want to change the schema because I don't know what other effects it will have on the code. The author of the program has since left the company . . . and here I am.

The program runs quarterly (I.E. four times a year) as part of a maintenance routine.

Can I do this without using WHILE statements? Because that's going to be a pain.

Thanks!

CodePudding user response：

So either update them all in a single query, eg

CREATE TABLE [dbo].[Ni](
    [FooID] [int] IDENTITY(1,1) NOT NULL,
    [Bar] [nvarchar](60) NULL,
    [LocationID] [int] NULL,
    [Thing1] [float] NULL
CONSTRAINT [PK_Ni] PRIMARY KEY CLUSTERED 
(
    [FooID] ASC
)
);

CREATE UNIQUE NONCLUSTERED INDEX [UQ_LocationBar] ON [dbo].[Ni]
(
    [LocationID] ASC,
    [Bar] ASC
);

insert into Ni(bar) values ('a'),('b'),('c');

with newValues as
(
  select * from (values (1,'c'),(3,'x')) newValues (FooId, Bar)
),
toUpdate as
(
  select ni.FooId, ni.Bar, NewValues.Bar NewBar
  from Ni
  join NewValues 
    on ni.FooID = newValues.FooId
)  
update toUpdate set Bar = NewBar

or disable and rebuild the unique index

begin transaction
alter index [UQ_LocationBar] on ni disable
update ni set bar = 'b' where fooid = 1
update ni set bar = 'a' where fooid = 2
alter index [UQ_LocationBar] on ni rebuild
commit transaction

Am I allowed to disable and re-enable the constraint in a stored procedure?

It requires additional permissions, of course, but there's no restriction on running DDL inside a stored procedure, and in SQL Server DDL is fully transactional, so you can commit/rollback to prevent partial updates and to prevent other sessions from seeing partial results.