Multiple update statement with foreign key constraints
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
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
Jarrod
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 ?
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 ?
ASKER
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
ASKER
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.
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 ?
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 ?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.