创建主键约束
代码 在列属性中使用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定义在列类型后面,可以显式地创建约束,并能为约束指定约束名称。对于UNIQUE与PRIMARY KEY,还可以在表级别使用CONSTRAINT指定约束,比如在为一个表设置多个主键时,可以在表级别使用CONSTRAINT设置约束。在表级别与在列级别的效果是相同的,但是能提供更清晰的代码,将代码5.5的CONSTRAINT声明更改为表级别,实现如代码5.6所示。
代码 在表级别使用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)
- );
上述代码相对于列类型来说最大的好处在于可以使用多列,比如通过在括号内输入以逗号分隔的多个列名,可以同时指定多列主键,例如如果要使用invoide_id和vendor_id作为主键,可以使用如下所示的代码:
- CONSTRAINT invoiceid_vendorid_pk PRIMARY KEY (invoice_id,vendor_id),
在为表设计主键时,下面是一些常用的设置规则。
主键应该是对用户没有意义的,在一些数据表的设计中,不建议以材料编码或身份证号码及员工工号作为主键,主键应该只是一些具有唯一性标识的标识符,比如自增长的数字等。
主键应该是单列的,以便提高连接和筛选操作的性能,复合主键通常导致不良的外键,因此要尽量避免。
主键应该是不能被更新的,主键的主要作用是唯一标识一行,更新则违反了主键无意义的原则。
主键不应该包含动态更新的数据,比如时间戳、创建时间或修改时间等这些动态变化的数据。
主键最好由计算机自动生成,在Oracle中可以使用序列来为主键列生成值。
主键约束:SQL 92建议在建立一个表时定义一个主键,它其实就是:唯一约束+非空约束。
创建外键约束
外键约束又称为引用约束,这种类型的约束主要用来在多个表之间定义关系,并强制引用完整性,与主键约束一样,外键约束也可以在列级别和表级别创建,使用关键字REFERENCES语句来定义,列级别的外键约束语法如下所示。
- [CONSTRAINT constraint_name]
- REFERENCES table_name (column_name)
- [ON DELETE {CASCADE|SET NULL}]
位于[]的可选部分指定CONSTRAINT和约束名称,ON DELETE {CASCADE|SET NULL}这行代码用来指示是否级联删除,当两个表中的两个字段建立了外键关联后,如果主键所在表中的值被删除,使用ON DELETE指定是否级联删除,CASCADE表示关联表中的内容一并删除,而SET NULL表示子表中的值设置为NULL。
注意:如果没有指定ON DELETE,默认情况下将使用CASCADE进行级联删除。
假定有一个表vendor,可以将invoice表的vendor_id与vendor表的vendor_id字段进行外键约束,也就是说,invoice表中的字段取值必须是vendor表中已经存在的供应商字段,vendor表的创建如代码5.7所示。
代码5.7 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) --唯一性约束
- )
下面的代码创建invoice表,在列级别为invoice表的vendor_id字段与vendor表的vendor_id字段进行了关联,如代码5.8所示。
代码5.8 在invoice表中为vendor_id列创建外键关联
- CREATE TABLE invoice
- (
- invoice_id NUMBER , --自动编号,唯一,不为空
- vendor_id NUMBER REFERENCES vendors (vendor_id), --供应商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)
- );
通过使用REFERENCES语法,指定要关联的目标表名与字段,示例中指定vendors表的主键vendor_id列作为关联字段。
注意:在定义外键时,引用的表键必须是唯一性键值,一般建议使用关联表的主键作为关联字段。
同样可以在表级别使用CONSTRAINT关键字来创建外键约束,例如下面的代码在表级别使用CONSTRAINT定义了外键关联并指定了ON DELETE级联删除设置,如代码5.9所示。
代码5.9 在invoice表级别创建外键关联
- 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
- );