How to update primary key?

Question

I am writing a script that has to update some rows without changing the contents of another script that creates a few tables. Another condition is that you cannot alter or drop constraints.

Contents of Create table script:

CREATE TABLE TRUCK(
REGNUM VARCHAR(10) NOT NULL,
CAPACITY DECIMAL(7) NOT NULL,
WEIGHT  DECIMAL(5) NOT NULL,
STATUS  VARCHAR(10) NOT NULL,
CONSTRAINT TRUCK_PKEY PRIMARY KEY(REGNUM),
CONSTRAINT TRUCK_STATUS CHECK (STATUS IN ('AVAILABLE', 'MAINTAINED', 'USED'));

and there are some row insertion statements.

CREATE TABLE TRIP(
TNUM DECIMAL(10)  NOT NULL,
LNUM DECIMAL(8)   NOT NULL,
REGNUM VARCHAR(10) NOT NULL,
TRIP_DATE DATE NOT NULL,
CONSTRAINT TRIP_PKEY PRIMARY KEY(TNUM),
CONSTRAINT TRIP_FKEY1 FOREIGN KEY(LNUM) REFERENCES DRIVER(LNUM)
CONSTRAINT TRIP_FKEY2 FOREGIN KEY(REGNUM) REFERENCES TRUCK(REGNUM) );

and there are some row insertion statements too.

This script is given by the lecturer and has no error.

Now, I tried:

UPDATE TRIP
SET REGNUN = 'PKR856'
WHERE REGNUM = 'SST005';

UPDATE TRUCK
SET REGNUN = 'PKR856'
WHERE REGNUM = 'SST005';

and this will give me an error "cannot delete/ update parent row. foregin key constriant.". All the row insertion statements in the given script have full information and there are row with regnum = sst005. I tried to update truck first and it won't work either. HELP PLEASE!

Check the first answer here: http://stackoverflow.com/questions/2341576/updating-mysql-primary-key — Timothy Kanski, Apr 23 '16 at 05:31
have you tried the second answer from that post? `update IGNORE table set primary_field = 'value'...............` — Jhecht, Apr 23 '16 at 05:57
I tried: update ignore TRIP set regnum = 'pkr856' .... and update truck set ..... . There was no error executing it but then the matching rows are not affected.... i.e. SST005 was not modified to PKR856. One more thing is that we haven't covered IGNORE in the lecture so I am not sure if I am supposed to use this statement either. — strongbaby12, Apr 23 '16 at 06:03
MySQL allows you to turn off foreign key constraint checking. You can do that temporarily while you make all these changes. — Barmar, Apr 23 '16 at 06:05
Can you add the `ON UPDATE CASCADE` option to the foreign key constraints? This is exactly what that option is for. — Barmar, Apr 23 '16 at 06:06
@Barmar Although I don't think you can change the content of the script given by the lecturer, adding ON UPDATE CACADE is the only solution I have... — strongbaby12, Apr 23 '16 at 06:17
The old solution, when Databases had no ON UPDATE CASCADE was to copy the TRUCK wirh the new REGNUM, update the TRIPs and delete the old TRUCK — Turo, Apr 23 '16 at 06:21

spencer7593 · Answer 1 · 2016-04-23T06:44:46.853

The quick fix is to disable foreign key checks for the session, and then re-enable them. With the foreign key checks disabled, MySQL will allow DML changes (INSERT/UPDATE/DELETE) that would otherwise violated the foreign key constraints.

Make sure the changes you make put the database in a consistent state, where there are no rows that violate constraints.

SET FOREIGN_KEY_CHECKS = 0;

UPDATE TRIP
SET REGNUN = 'PKR856'
WHERE REGNUM = 'SST005';

UPDATE TRUCK
SET REGNUN = 'PKR856'
WHERE REGNUM = 'SST005';

SET FOREIGN_KEY_CHECKS = 1;

As another alternative, if the scripts don't specify ENGINE= for the tables, you could temporarily change the default storage engine for the session to MyISAM, before executing the script to create the tables.

SELECT @@session.default_storage_engine INTO @prev_default_storage_engine ;
SET default_storage_engine = MYIASM ;

-- execute table creation script 

SET default_storage_engine = @prev_default_storage_engine ;

(The "trick" here is that MyISAM storage engine doesn't enforce foreign key constraints.)

score 1 · Accepted Answer · answered Apr 23 '16 at 06:22

CONSTRAINT TRIP_FKEY1 FOREIGN KEY(LNUM) REFERENCES DRIVER(LNUM)

Check manual about MySQL foreign key:

RESTRICT: Rejects the delete or update operation for the parent table. Specifying RESTRICT (or NO ACTION) is the same as omitting the ON DELETE or ON UPDATE clause.

You don't set ON DELETE and ON UPDATE options, so they will be RESTRICT by default. And you cannot update parent table primary key while row in child table exists.

You can change your CREATE TABLE like this:

CREATE TABLE TRIP(
  TNUM DECIMAL(10)  NOT NULL,
  LNUM DECIMAL(8)   NOT NULL,
  REGNUM VARCHAR(10) NOT NULL,
  TRIP_DATE DATE NOT NULL,
  CONSTRAINT TRIP_PKEY PRIMARY KEY(TNUM),
  CONSTRAINT TRIP_FKEY1 FOREIGN KEY(LNUM) REFERENCES DRIVER(LNUM) ON UPDATE CASCADE
  CONSTRAINT TRIP_FKEY2 FOREGIN KEY(REGNUM) REFERENCES TRUCK(REGNUM) ON UPDATE CASCADE);

and query

UPDATE TRUCK SET REGNUN = 'PKR856' WHERE REGNUM = 'SST005';

will change keys in both tables, primary key in truck and foreign key in trip.

score 0 · Answer 3 · answered Apr 23 '16 at 06:23

Instead of updating the primary keys in the parent table, add new rows that copy all the data except the primary key. Then you can update the foreign key in the child table, and then delete the original row in the parent table.

INSERT INTO TRUCK (regnum, capacity, weight, status)
SELECT 'PKR856', capacity, weight, status
FROM TRUCK
WHERE regnum = 'SST005';

UPDATE TRIP
SET REGNUM = 'PKR856'
WHERE REGNUM = 'SST005';

DELETE FROM TRUCK WHERE regnum = 'SST005';

How to update primary key?

3 Answers3

Linked