Oracle PL/SQL开发基础（第一弹：创建表和创建约束）

张小龙net馆藏 2017-10-16

展开全文

创建表

创建表使用CREATE TABLE语句
如：

--创建表workcenter
CREATE TABLE workcenter    --指定表名称
(  
   id int,                 --添加编号字段
   name varchar2(200)      --添加名称字段
)1
2
3
4
5
6

除了指定列的类型之外，还可以使用列类型属性来指定列的基本约束，常用的列特性有：
- NOT NULL：指定列不接受NULL值。
- UNIQUE：指定存储在列中的每一个值都必须唯一。
- DEFAULT default_value：指定列的默认值。
如：

CREATE TABLE invoice
(
   invoice_id NUMBER NOT NULL UNIQUE,                     --自动编号，唯一，不为空
   vendor_id NUMBER NOT NULL,                             --供应商ID
   invoice_number VARCHAR2(50)  NOT NULL,                 --发票编号
   invoice_date DATE DEFAULT SYSDATE,                     --发票日期
   invoice_total  NUMBER(9,2) NOT NULL,                   --发票总数
   payment_total NUMBER(9,2)   DEFAULT 0                  --付款总数
)1
2
3
4
5
6
7
8
9

创建约束

Oracle中最常勇士的约束有：
- 非空约束：验证字段的值不能为空，一般在字段级别使用NOT NULL列属性进行约束。
- 唯一约束：指定列的值在整个表的相同咧中是唯一的，既可以在表级别也可以在字段级别定义，在字段级别使用UNIQUE进声明。
- 检查约束：在定义数据表时，在字段级别或表级别加入检查约束，使其满足特定的要求，允许指定字段的检查条件，比如值大于0或小于0等。
- 主键约束：建议在建立一个表时定义一个主键，主键其实就是唯一约束+非空约束。
- 外键约束：用于定义表间关联的约束，实现数据完整性，这是关系型数据库的精髓。

创建主键约束

可以使用PRIMARY KEY关键字，如：

CREATE TABLE invoice
(
   invoice_id NUMBER PRIMARY KEY,                         --自动编号，唯一，不为空
   vendor_id NUMBER NOT NULL,                             --供应商ID
   invoice_number VARCHAR2(50)  NOT NULL,                 --发票编号
   invoice_date DATE DEFAULT SYSDATE,                     --发票日期
   invoice_total  NUMBER(9,2) NOT NULL,                   --发票总数
   payment_total NUMBER(9,2)   DEFAULT 0                  --付款总数
)1
2
3
4
5
6
7
8
9

这是最简单的指定表主键的方式，但不是最好的编程习惯。

建议的方法是在列或表级别使用CONSTRAINT关键字，为约束指定一个约束名。
在列级别使用CONSTRAINT，如：

CREATE TABLE invoice
(
   invoice_id NUMBER CONSTRAINT invoice_pk PRIMARY KEY,                --自动编号，唯一，不为空                                                    
   vendor_id NUMBER CONSTRAINT vendor_id_nn NOT NULL,                  --供应商ID
   invoice_number VARCHAR2(50) CONSTRAINT vendor_number_nn NOT NULL,   --发票编号                                                                   
   invoice_date DATE DEFAULT SYSDATE,                                  --发票日期
   invoice_total  NUMBER(9,2)  CONSTRAINT invoice_total_nn NOT NULL,   --发票总数
   payment_total NUMBER(9,2)   DEFAULT 0                               --付款总数
)1
2
3
4
5
6
7
8
9

更好的方法是在表级别使用CONSTRAINT，如：

CREATE TABLE invoice
(
   invoice_id NUMBER ,                                --自动编号，唯一，不为空
   vendor_id NUMBER,                                  --供应商ID
   invoice_number VARCHAR2(50),                       --发票编号
   invoice_date DATE DEFAULT SYSDATE,                 --发票日期
   invoice_total  NUMBER(9,2) ,                       --发票总数
   payment_total NUMBER(9,2)   DEFAULT 0,             --付款总数
   CONSTRAINT invoice_pk PRIMARY KEY (invoice_id),
   CONSTRAINT vendor_id_un UNIQUE (vendor_id)
);1
2
3
4
5
6
7
8
9
10
11

创建外键约束

可以使用FOREIGN KEY关键字创建外键约束。
假定有一个表vendor，可以将invoice表的vendor_id与vendor表的vendor_id字段进行外键约束，也就是说，invoice表中的字段取值必须是vendor表中已经存在的供应商字段，如：

CREATE TABLE vendors
(
   vendor_id NUMBER,                              --供应商id
   vendor_name VARCHAR2(50) NOT NULL,             --供应商名称
   CONSTRAINT vendors_pk PRIMARY KEY (vendor_id), --主键
   CONSTRAINT vendor_name_uq UNIQUE (vendor_name) --唯一性约束
);

CREATE TABLE invoice
(
   invoice_id NUMBER ,                            --自动编号，唯一，不为空
   vendor_id NUMBER,                              --供应商ID
   invoice_number VARCHAR2(50),                   --发票编号
   invoice_date DATE DEFAULT SYSDATE,             --发票日期
   invoice_total  NUMBER(9,2) ,                   --发票总数
   payment_total NUMBER(9,2)   DEFAULT 0,         --付款总数
   CONSTRAINT invoiceid_vendorid_pk PRIMARY KEY (invoice_id,vendor_id),
   CONSTRAINT vendor_id_un UNIQUE (vendor_id),
   CONSTRAINT invoice_fk_vendors FOREIGN KEY (vendor_id) REFERENCES vendors (vendor_id) 
   ON DELETE CASCADE
);1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

ON DELETE CASCADE用来指示级联删除。
在定义外键时，引用的表键必须是唯一性键值，一般建议使用关联表的主键作为关联字段。

创建检查约束

可以在列级别创建检查约束，如：

CREATE TABLE invoice_check
(
   invoice_id NUMBER ,
   invoice_total  NUMBER(9,2)  CHECK (invoice_total>0 AND invoice_total<=5000) ,                                                                   
   payment_total NUMBER(9,2)  DEFAULT 0 CHECK(payment_total>0 AND payment_total<=10000)
);1
2
3
4
5
6

更建议在表级别创建检查约束，如：

CREATE TABLE invoice_check
(
   invoice_id NUMBER ,
   invoice_total  NUMBER(9,2) DEFAULT 0 ,
   payment_total NUMBER(9,2)  DEFAULT 0,
   CONSTRAINT invoice_ck CHECK(invoice_total<=5000 AND payment_total<=10000)      
);1
2
3
4
5
6
7

如果插入的值不满足约束条件，将触发异常：ORA-02290: 违反检查约束条件(SCOTT.INVOICE_CK)。
在约束中，还可以使用各种逻辑运算符及标准的SQL函数来计算布尔值结果，如：

CREATE TABLE invoice_check_others
(
   invoice_id NUMBER ,
   invoice_name VARCHAR2(20),
   invoice_type INT,
   invoice_clerk VARCHAR2(20),
   invoice_total  NUMBER(9,2) DEFAULT 0 ,
   payment_total NUMBER(9,2)  DEFAULT 0,
   --发票总数必须在1-1000之间
   CONSTRAINT invoice_ck_01 CHECK(invoice_total BETWEEN 1 AND 1000) ,
   --发票名称必须为大写字母
   CONSTRAINT check_invoice_name CHECK (invoice_name = UPPER(invoice_name)),
   --发票类别必须在1,2,3,4,5,6,7之间
   CONSTRAINT check_invoice_type CHECK (invoice_type IN (1,2,3,4,5,6,7)),
   --发票处理员工编号不能为NULL值
   CONSTRAINT check_invoice_clerk CHECK (invoice_clerk IS NOT NULL)
);1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

使用检查约束有如下限制：
- 不能为视图指定检查约束，但是可以在视图上使用WITH CHECK OPTION子句，该子句与使用检查约束等同。
- 检查约束不能包含子查询和标量子查询表达式，不能包含CURRENT_DATE、CURRENT_TIMESTAMP、DBTIMEZONE、LOCALTIMESTAMP、SESSIONTIMEZONE、SYSDATE、SYSTIMESTAMP、UID、USER、USERENV等函数。
- 检查约束中不能包含自定义的函数。
- 不能包含伪列，比如CURRVAL、NEXTVAL、LEVEL、ROWNUM。