CODEWITHSUNDEEP
sql-serverforeign-keyssql-update
Daggy
Daggy

Reputation: 3

sql - update two key columns without conflict

I want to to update a column from table patient, and add a prefix before all its fields, like

update patient
set contactdetailsID = '99999'+ contactdetailsID
where patient.id = 5294

So, row with id = 5294, will have the same value in column contactdetailsID with prefix 99999.

Before  |    After
012345      99999012345

the issue i am facing is that patient.contactdetailsID is the foreign key for another table "contactdetails".

So i get the following error.

Msg 547, Level 16, State 0, Line 1 UPDATE statement conflicted with COLUMN FOREIGN KEY constraint 'ContactDetails_Patient_FK1'. The conflict occurred in database 'A', table 'ContactDetails', column 'id'. The statement has been terminated.

(0 row(s) affected)

How can i make that change? I want to change both patient.contactdetailsID and its key Contactdetails.ID, with the same prefix in order not to lose the connection between them.

Upvotes: 0

Views: 272

Answers (2)

Quoting Eddie
Quoting Eddie

Reputation: 1791

You could implement ON UPDATE CASCADE in your table. see MySQL documentation

Upvotes: 0

OrangUtanKlaus
OrangUtanKlaus

Reputation: 44

You can temporary disable the Foreign-Key Constraint using:

ALTER TABLE *Tablename*
NOCHECK CONSTRAINT *ForeinKey_Name*;

and update both the ContactDetails Table and Patient Table.

But make sure that you enable the ForeignKey afterwards!

To enable a Foreign Key use 

ALTER TABLE *Tablename*
CHECK CONSTRAINT *ForeinKey_Name*;

Upvotes: 1

Related Questions