oracle 表的创建与管理 约束

oracle 表的创建与管理 约束

在 Oracle 之中数据表就被称为

数据库对象，而对象的操作语法一共有三种：

· 创建对象：CREATE 对象类型 对象名称 [选项]；

· 删除对象：DROP 对象类型 对象名称 [选项]；

· 修改对象：ALTER 对象类型 对象名称 [选项]。

创建表

复制代码

CREATE TABLE 表名称 (

字段 类型 [DEFAULT 默认值] ,

...

字段 类型 [DEFAULT 默认值]

)

一般而言，默认值是为了防止用户在增加数据时为 null 的问题。

复制表

实际上所谓的复制表严格来讲不是复制的含义，是针对于一个子查询（返回多行多列）的结果进行表的创建，完整

语法如下：

CREATE TABLE 表名称 AS 子查询

注意点：关于事务与 DDL 的问题

· 事务可以控制的只是数据的更新操作（增加、修改、删除），而 DDL 是不受事务控制的；

· 在 Oracle 之中，如果发生了 DDL 操作，那么所有未提交的事务将会自动进行提交。

在 Oracle 之中有三类数据字典：

· user_*：用户可以使用的数据字典；

· dba_*：管理员可以使用到的数据字典；

· all_*：包含了当前用户可以访问的全部数据字典信息（有可能某些数据字典不属于此用户）。

为表重命名

RENAME 旧的表名称 TO 新的表名称 ;

截断表

TRUNCATE TABLE 表名称 ;

表一旦被截断，其所有占用的资源都将被彻底释放。

删除数据表

DROP TABLE 表名称 ;

闪回操作

从 Oracle 10g 起为了方便用户进行数据表的恢复。为 Oracle 增加了一个类似于 windows 的回收站功能，等于是所有

删除是数据表，首先保存在回收站之中，如果用户有需要也可以进行恢复。

范例：通过回收站恢复 emp10 数据表

FLASHBACK TABLE emp10 TO BEFORE DROP ;

范例：彻底删除数据表，不让删除的数据表经过回收站，在删除语句之后增加一个 PURGE

DROP TABLE emp10 PURGE ;

范例：删除回收站之中的一个表

PURGE TABLE empnull ;

范例：清空回收站

PURGE RECYCLEBIN ;

只有 oracle 有此特性

修改表结构

1、 为表中增加数据列

ALTER TABLE 表名称 ADD(

列名称 数据类型 [DEFAULT 默认值],

列名称 数据类型 [DEFAULT 默认值], ...)

2、 修改列结构

ALTER TABLE 表名称 MODIFY(

列名称 数据类型 [DEFAULT 默认值], ...) ;

从标准的 SQL 来讲，是一定要提供有数据表的修改操作的，但是从实际的开发来讲，任何人都不允许有随意修改数

据表结构的想法，而且更不能说自己去修改。

约束是保证表中数据完整性的一种有效检测手

非空约束（NOT NULL，NK）

DROP TABLE member ;

CREATE TABLE member(

mid NUMBER ,

name VARCHAR2(20) NOT NULL

) ;

INSERT INTO member(mid,name) VALUES(1,null)

此时，数据库会提示如下错误信息：“ORA-01400: 无法将 NULL 插入 ("SCOTT"."MEMBER"."NAME")”。此时会

明确的显示出那个字段上违反了非空约束。

唯一约束（UNIQUE，UK）

name VARCHAR2(20) NOT NULL ,

email VARCHAR2(20) UNIQUE

范例：增加正确的数据

INSERT INTO member(mid,name) VALUES (1,'王小强') ;

INSERT INTO member(mid,name,email) VALUES (1,'王大强','[email protected]') ;

在使用唯一约束的时候一定要记住，null 不属于重复的统计范畴。

范例：增加错误的数据

INSERT INTO member(mid,name,email) VALUES (1,'王中强','[email protected]') ;

此时出现了错误的提示信息：“ORA-00001: 违反唯一约束条件 (SCOTT.SYS_C0011101)”。但是与之前的非空约束相

比，此时的提示信息非常不明确。

DROP TABLE member PURGE ;

name VARCHAR2(20)NOT NULL ,

email VARCHAR2(20) ,

CONSTRAINT uk_email UNIQUE(email)

此时，如果保存的数据出现了错误，则提示信息“ORA-00001: 违反唯一约束条件 (SCOTT.UK_EMAIL)”。

除了非空约束之外，所有的约束都一定要按照以上的方式进行设置，因为只有一个正常的名称才方便系统进行约束

的维护

主键约束（PRIMARY KEY，PK）

主键约束 = 非空约束 + 唯一约束。

CONSTRAINT pk_mid PRIMARY KEY(mid)

观察复合主键

name VARCHAR2(20) ,

CONSTRAINT pk_mid_name PRIMARY KEY(mid,name)

INSERT INTO member(mid,name) VALUES(1,'学冰') ;

INSERT INTO member(mid,name) VALUES(1,'学刁') ;

INSERT INTO member(mid,name) VALUES(2,'学刁') ;

正常人都不用，知道就行了，千万别用。

检查约束（CHECK，CK）

sex VARCHAR2(10) ,

age NUMBER(3) ,

CONSTRAINT pk_mid PRIMARY KEY(mid) ,

CONSTRAINT ck_sex CHECK (sex IN ('男','女')) ,

CONSTRAINT ck_age CHECK (age BETWEEN 0 AND 250)

即使设置了多个检查约束，那么也是一个一个约束进行过滤的。

但是在这里有一个提示：一般而言，你所设置的约束越多，在进行数据更新操作的时候性能就越低。所以很多时候

一些数据的检查操作都会交给程序完成

主-外键约束（FOREIGN KEY，FK）

DROP TABLE book PURGE ;

CONSTRAINT pk_mid PRIMARY KEY(mid)

CREATE TABLE book(

bid NUMBER ,

title VARCHAR2(20) ,

CONSTRAINT pk_bid PRIMARY KEY(bid) ,

CONSTRAINT fk_mid FOREIGN KEY(mid) REFERENCES member(mid)

限制一：在删除主表之前，请一定要保证先删除子表。所以在删除数据表的时候应该先删除子表后再删除父表。

范例：强制删除

DROP TABLE member CASCADE CONSTRAINT ;

但是必须注意的是，强制删除是无法彻底删除的，所删除的数据表会保存在回收站之中。但是不建议使用强制删除

表的操作，还是建议按照先删除子表再删除父表的方式进行。

限制二：作为外键的字段在主表之中必须具备主键约束或者是唯一约束。

限制三：数据的级联操作问题

· 为了方便数据的删除，提供有一个级联删除操作，在主表数据删除的时候对应的子表数据会同时删除掉，在建立

外键的时候使用 ON DELETE CASCADE 来设置。

范例：设置级联删除

CONSTRAINT fk_mid FOREIGN KEY(mid) REFERENCES member(mid) ON DELETE CASCADE

· 如果在删除主表数据的时候不希望子表数据被一起删除掉，则可以使用级联更新操作，使用 ON DELETE SET

NULL 来设置。

范例：设置级联更新

CONSTRAINT fk_mid FOREIGN KEY(mid) REFERENCES member(mid) ON DELETE SET NULL

修改约束

首先明确：所有的约束一定要在表建立的同时就已经设置完成，不可能说表先去使用之后再回头设置约束，所以以

下的这些

1、 为表中增加约束

ALTER TABLE 表名称 ADD CONSTRAINT 约束名称 约束类型(约束字段) [选项] ;

但是需要提醒的是，此类的语法适合于唯一、主键、检查、外键约束的添加，而无法添加非空约束

2、 删除约束

ALTER TABLE 表名称 DROP CONSTRAINT 约束名称 ;

原文地址

https://www.cnblogs.com/xianting/p/10595807.html