如何更新主键? [英] How to update primary key?

问题描述

我正在编写一个脚本，该脚本必须更新一些行，而无需更改创建几个表的另一个脚本的内容.另一个条件是您不能更改或删除约束.

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.

创建表脚本的内容:

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) );

还有一些行插入语句.

此脚本由讲师提供，没有错误.

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

现在，我尝试了:

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

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

，这将给我一个错误无法删除/更新父行.foregin键约束.".给定脚本中的所有行插入语句均具有完整信息，并且其中行的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)

查看有关MySQL 外键的手册:

Check manual about MySQL foreign key:

RESTRICT:拒绝父表的删除或更新操作.指定RESTRICT(或NO ACTION)与省略ON DELETE或ON UPDATE子句相同.

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.

您没有设置ON DELETE和ON UPDATE选项，因此默认情况下它们将为RESTRICT.当子表中的行存在时，您将无法更新父表的主键.

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.

您可以这样更改CREATE TABLE:

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);

并查询

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

将更改两个表中的键，分别是truck中的主键和trip中的外键.

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

这篇关于如何更新主键?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！