Multiple update statement with foreign key constraints

ITBenelux
ITBenelux used Ask the Experts™
on
Experts,

I have an collection of update statement that I need to execute.  but for every table the field that I need to update is the PK (user_id)

use users
UPDATE tbluser SET id = 'mlammert' where id = 'mfleer'
UPDATE tbluserTitle SET user_id = 'mlammert' where user_id = 'mfleer'
UPDATE tbluserTeam SET user_id = 'mlammert' where user_id = 'mfleer'

So I assume that I need to drop the constraint every time ? or is there a different way? and if not.. what is the best way to write this in code..

thx

ITBe
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
can you scrip the constrains here
using:

UPDATE [tbluser]            SET [id]            = 'mlammert' where [id]             = 'mfleer'
UPDATE [tbluserTitle]      SET [user_id]      = 'mlammert' where [user_id] = 'mfleer'
UPDATE [tbluserTeam]      SET [user_id]      = 'mlammert' where [user_id] = 'mfleer'

which statement fails ? - please can you post error message ?

Author

Commented:
constraint example of title database:
CREATE TABLE [dbo].[tblTitle](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[isPrimary] [bit] NOT NULL CONSTRAINT [DF__tblTitle__isPrim__7C4F7684]  DEFAULT ((0)),
	[entity_id] [int] NULL,
 CONSTRAINT [tblTitle_PK] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO

Open in new window

How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

Author

Commented:
Error message:

The UPDATE statement conflicted with the FOREIGN KEY constraint "tblUserTitle_FK_1". The conflict occurred in database "Users", table "dbo.tblUser", column 'id'.
The statement has been terminated.
Is this data correct:

UPDATE [tbluser]            SET [id]            = 'mlammert' where [id]             = 'mfleer'
UPDATE [tbluserTitle]      SET [user_id]      = 'mlammert' where [user_id] = 'mfleer'
UPDATE [tbluserTeam]      SET [user_id]      = 'mlammert' where [user_id] = 'mfleer'


as we are adding string data into a int column ??? - this wont work anyway ?
Project Architect
Commented:
Hi ITBenelux,

Maintaining a PK while other tables have a FK relationship to it is ... messy.  I would not recommend doing it.

What I suggest you do is:
1. Insert a new row into the PK table with the new PK (done as INSERT ... SELECT ... perhaps);
2. Update the child tables to point to the new row;
3. If required, delete the old row from the PK table.

lwadwell

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial