创建表
创建表使用CREATE TABLE语句
如:
--创建表workcenter
CREATE TABLE workcenter --指定表名称
(
id int, --添加编号字段
name varchar2(200) --添加名称字段
)
除了指定列的类型之外,还可以使用列类型属性来指定列的基本约束,常用的列特性有:
- 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 --付款总数
)
创建约束
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 --付款总数
)
这是最简单的指定表主键的方式,但不是最好的编程习惯。
建议的方法是在列或表级别使用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 --付款总数
)
更好的方法是在表级别使用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)
);
创建外键约束
可以使用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
);
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)
);
更建议在表级别创建检查约束,如:
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)
);
如果插入的值不满足约束条件,将触发异常: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)
);
使用检查约束有如下限制:
- 不能为视图指定检查约束,但是可以在视图上使用WITH CHECK OPTION子句,该子句与使用检查约束等同。
- 检查约束不能包含子查询和标量子查询表达式,不能包含CURRENT_DATE
、CURRENT_TIMESTAMP
、DBTIMEZONE
、LOCALTIMESTAMP
、SESSIONTIMEZONE
、SYSDATE
、SYSTIMESTAMP
、UID
、USER
、USERENV
等函数。
- 检查约束中不能包含自定义的函数。
- 不能包含伪列,比如CURRVAL、NEXTVAL、LEVEL、ROWNUM。