达梦数据库修改字段（列）（基表修改语句）

最新推荐文章于 2024-03-15 11:50:42 发布

weixin_33810006

最新推荐文章于 2024-03-15 11:50:42 发布

阅读量2.9w

点赞数 4

文章标签：数据库 python 开发工具

原文链接：https://my.oschina.net/u/3782432/blog/1919631

版权

2019独角兽企业重金招聘Python工程师标准>>>

达梦数据库提供基表修改语句，对基表的结构进行全面的修改，包括修改基表名、列名、增加列、删除列、修改列类型、增加表级约束、删除表级约束、设置列缺省值、设置触发器状态等一系列修改。

通用格式：ALTER TABLE [[.] .]

下面通过例子来如何进行操作：根据实际情况对进行相应的变化

1.修改一列（字段）的数据类型、精度、刻度，设置列上的 DEFAULT,NOT NULL,NULL；

a. 使用 MODIFY COLUMN 时，要更改的列不能是：
（1）聚集索引的列或者引用约束中引用和被引用的列； //(信息量比较大)
（2）用于 CHECK 约束的列。 //(信息量比较大)
b. 使用 MODIFY COLUMN 子句不能在列上增加 check 约束，能修改的约束只有列上的NULL， NOT NULL 约束；如果某列现有的值均非空，则允许添加 NOT NULL；属于聚集索引包含的列不能被修改；自增列不允许被修改。
c. 使用 MODIFY COLUMN 修改可更改列的数据类型时，若该表中无元组(表中的一行称为元组，无元组即每行都没有数据)，则可任意修改
其数据类型、长度、精度或量度；若表中有元组，则系统会尝试修改其数据类型、长度、精度或量度，如果修改不成功，则会报错返回。
特殊说明：无论表中有、无元组，多媒体数据类型和非多媒体数据类型都不能相互转换。 d．修改有默认值的列的数据类型时，原数据类型与新数据类型必须是可以转换的,否则即使数据类型修改成功，但在进行插入等其他操作时，仍会出现数据类型转换错误。

举例说明：

例1 产品的评论表中COMMENTS、 PRODUCT_REVIEWID、 PRODUCTID、 RATING几列都不允许修改，分别因为： COMMENTS为多媒体数据类型； PRODUCT_REVIEWID上定义有关键字，属于用于索引的列； PRODUCTID用于引用约束（包括引用列和被引用列）； RATING用于CHECK约束。另外，关联有缺省值的列也不能修改。而其他列都允许修改。假定当前数据库为BOOKSHOP，用户为SYSDBA，如将评论人姓名的数据类型改为varchar(8),并指定该列为NOTNULL，且缺省值为'刘青'：

ALTER TABLE PRODUCTION.PRODUCT_REVIEW MODIFY NAME varchar(8) DEFAULT '刘青' NOTNULL;
此语句只有在表中无元组的情况下才能成功。

2．增加一列（字段）及该列（字段）上的列级约束；

a．使用 ADD COLUMN 时，新增列名之间、新增列名与该基表中的其它列名之间均不能重复。若新增列跟有缺省值，则已存在的行的新增列值是其缺省值。添加新列对于任何涉及表的视图定义或约束定义没有作用。例如：如果用“*”为一个表创建一个视图，那么后加入的新列不会自动地加入视图中，只能重新创建此视图。
b．使用 ADD COLUMN 时，还有以下限制条件：
（1）列定义中如果带有列约束，只能是对该新增列的约束；列级约束可以带有约束名，系统中同一模式下的约束名不得重复，如果不带约束名，系统自动为此约束命名；
（2）如果表上没有元组，列可以指定为 NOT NULL；如果表中有元组，对于已有列可以指定同时有 DEFAULT 和 NOT NULL，新增列不能指定 NOT NULL(因为如果指定为not null那么之前存在的数据根本就没有这一列的数据，你还让它不能为空，是何居心？)；
（3）该列可指定为 CHECK；
（4）该列可指定为 FOREIGN KEY；
（5）允许向空数据的表中，添加自增列。
举例说明：

例2 具有DBA权限的用户需要对EMPLOYEE_ADDRESS表增加一列，列名为ID（序号），数据类型为INT，值小于10000。

ALTER TABLE RESOURCES.EMPLOYEE_ADDRESS ADD ID INT PRIMARY KEY CHECK (ID <10000);

如果该表上没有元组，且没有PRIMARY KEY，则可以将新增列指定为 PRIMARY KEY。表上没有元组时也可以将新增列指定为UNIQUE，但同一列上不能同时指定PRIMARY KEY和UNIQUE两种约束。

例3 具有DBA权限的用户需要对ADDRESS表增加一列， 列名为PERSONID，数据类型为INT，定义该列为NOT NULL，并引用PERSON的PERSONID。
ALTER TABLE PERSON.ADDRESS ADD PERSONID INT NOT NULL REFERENCES PERSON. PERSON(PERSONID);

如果表上没有元组，新增列可以指定为 NOT NULL；如果表上有元组且都不为空，该列可以指定同时有 DEFAULT 和 NOT NULL，不能单独指定为 NOT NULL

3．删除一列（字段）；

用 DROP COLUMN 子句删除一列有两种方式： RESTRICT 和 CASCADE。 RESTRICT 方式为缺省选项，确保只有不被其他对象引用的列才被删除。无论哪种方式，表中的唯一列不能被删除。 RESTRICT 方式下，下列类型的列不能被删除：被引用列、建有视图的列、有 check 约束的列。删除列的同时将删除该列上的约束。 CASCADE 方式下，将删除这一列上的引用信息和被引用信息、引用该列的视图、索引和约束；系统允许直接删除 PK 列（我猜pk列指的应该是主键列）。但被删除列为 CLUSTER PRIMARY KEY 类型时除外，此时不允许删除。（蓝色字没看懂）。

例 4 具有 DBA 权限的用户需要删除 PRODUCT 表的 PRODUCT_SUBCATEGORYID 一列。

ALTER TABLE PRODUCTION.PRODUCT DROP PRODUCT_SUBCATEGORYID CASCADE;

删除PRODUCT_SUBCATEGORYID这一列必须采用CASCADE方式，因为该列引用了PRODUCT_SUBCATEGORY表的PRODUCT_SUBCATEGORYID。

4 . 表名或列表的重命名

举例说明：具有DBA权限的用户需要将表MY_STUDENT的SEX字段改为GENDER

ALTER TABLE DMHR.MY_STUDENT ALTER SEX RENAME TO GENDER;

/前4个是操作表的字段的，后几个是操作关于这个表的属性的*/

5．增加表上的约束；

ADD CONSTRAINT 子句用于添加表级约束。表级约束包括：主键约束（PRIMARY KEY），唯一性约束（UNIQUE），引用约束（REFERENCES），检查约束（CHECK）。添加表级约束时可以带有约束名，系统中同一模式下的约束名不得重复，如果不带约束名，系统自动为此约束命名。用 ADD CONSTRAINT 子句添加约束时，对于该基表上现有的全部元组要进行约束违规验证：
（1）添加一个主键约束时，要求将成为关键字的字段上无重复值且值非空，并且表上没有定义主关键字；
（2）添加一个 UNIQUE 约束时，要求将成为唯一性约束的字段上不存在重复值，但允许有空值；
（3）添加一个 REFERENCES 约束时，要求将成为引用约束的字段上的值满足该引用约束。
（4）添加一个 CHECK 约束或外键时，要求该基表中全部的元组满足该约束。

例5 具有DBA权限的用户需要在PRODUCT表上增加UNIQUE约束， UNIQUE字段为NAME。
ALTER TABLE PRODUCTION.PRODUCT ADD CONSTRAINT CONS_PRODUCTNAME UNIQUE(NAME);
用ADD CONSTRAINT子句添加约束时， 对于该基表上现有的全部元组要进行约束违规验证。
在这里，分为三种情况：
（1）如果表商场登记里没有元组，则上述语句一定执行成功；
（2）如果表商场登记里有元组，并且欲成为唯一性约束的字段商场名上不存在重复值，则
上述语句执行成功；
（3）如果表商场登记里有元组，并且欲成为唯一性约束的字段商场名上存在重复值，则上
述语句执行不成功，系统报错“无法建立唯一性索引”。
如果语句执行成功，用户通过查询
SELECT TABLEDEF('BOOKSHOP', 'PRODUCTION', 'PRODUCT');
可以看到，修改后的商场登记的表结构显示为：
CREATE TABLE "PRODUCT" AT "BOOKSHOP"
(
"PRODUCTID" INTEGER IDENTITY(1,1) NOT NULL,
"NAME" VARCHAR(50) NOT NULL,
"AUTHOR" VARCHAR(25) NOT NULL,
"PUBLISHER" VARCHAR(50) NOT NULL,
"PUBLISHTIME" DATE NOT NULL,
"PRODUCT_SUBCATEGORYID" INTEGER NOT NULL,
"PRODUCTNO" VARCHAR(25) NOT NULL,
"SATETYSTOCKLEVEL" SMALLINT NOT NULL,
"ORIGINALPRICE" DEC(19,4) NOT NULL,
"NOWPRICE" DEC(19,4) NOT NULL,
"DISCOUNT" DEC(2,1) NOT NULL,
"DESCRIPTION" TEXT,
"PHOTO" IMAGE,
"TYPE" VARCHAR(5),
"PAPERTOTAL" INTEGER,
"WORDTOTAL" INTEGER,
"SELLSTARTTIME" DATE NOT NULL,
"SELLENDTIME" DATE,
PRIMARY KEY("PRODUCTID"),
UNIQUE("PRODUCTNO"),
CONSTRAINT "CONS_PRODUCTNAME" UNIQUE("NAME"),
FOREIGN KEY("PRODUCT_SUBCATEGORYID")
REFERENCES"PRODUCT_SUBCATEGORY"("PRODUCT_SUBCATEGORYID"))

可以看到CONS_PRODUCTNAME就是UNIQUE约束名。

6．删除表上的约束；

DROP CONSTRAINT 子句用于删除表级约束，表级约束包括：主键约束（PRIMARY KEY）、
唯一性约束（UNIQUE）、引用约束（REFERENCES）、检查约束（CHECK）。用 DROP CONSTRAINT
子句删除一约束时，同样有 RESTRICT 和 CASCADE 两种方式。当删除主键或唯一性约束时，
系统自动创建的索引也将一起删除。如果打算删除一个主键约束或一个唯一性约束而它有外部约
束，除非指定 CASCADE 选项，否则将不允许删除。也就是说，指定 CASCADE 时，删除的不
仅仅是用户命名的约束，还有任何引用它的外部约束。

例6 假定当前数据库为BOOKSHOP，库中具有DBA权限的用户需要删除PRODUCT表上的NAME的UNIQUE约束。当前的PRODUCT表结构请参见例5。删除表约束，首先需要得到该约束对应的约束名，用户可以查询系统表 SYSCONSTRAINTS，如下所示：
SELECT NAME FROM SYSCONSTRAINTS WHERE TABLEID =(SELECT ID FROM SYSTABLES WHERE NAME = 'PRODUCT' AND SCHID=( select SCHID from sysschemas where name='PRODUCTION'));
该系统表显示商场登记表上的所有PRIMARY KEY， UNIQUE， CHECK约束。查询得到NAME上UNIQUE约束对应的约束名，这里为CONS_PRODUCTNAME。然后，可采用以下的语句删除指定约束名的约束：
ALTER TABLE PRODUCTION.PRODUCT DROP CONSTRAINT CONS_PRODUCTNAME;
语句执行成功。

转载于:https://my.oschina.net/u/3782432/blog/1919631