mysql 如何更新主键,如何更新主键？

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!

解决方案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.