一 什么是外键
(1)在oracle数据库中,外键是指在表(父表)中定义外键的字段的列的值必须在另一张表(子表)中出现。外键是用来实现参照完整性的方法之一。
(2)被参照的表称为父表,创建外键的表称为子表。子表中的外键关联的是父表中的主键
(3)外键可以在建表的时候定义,也可以通过alter table语句来创建。
二 创建表定义外键
a. 建表的时候定义
- CREATE TABLE table_name
- (
- column1 datatype null/not null,
- column2 datatype null/not null,
- ...
- CONSTRAINT fk_column FOREIGN KEY (column1,column2,... column_n) REFERENCES parent_table (column1,column2,...column_n)
- );
(1)创建单列的外键- create table tb_supplier
- (
- supplier_id number not null,
- supplier_name varchar2(50) not null,
- contact_name varchar2(50),
- CONSTRAINT pk_supplier PRIMARY KEY (supplier_id)
- );
-
- create table tb_products
- (
- product_id number not null,
- product_name varchar2(100),
- supplier_id number not null,
- constraint fk_products_supplier foreign key (supplier_id) references tb_supplier(supplier_id)
- );
(2)创建多列的外键- create table tb_supplier
- (
- supplier_id number not null,
- supplier_name varchar2(50) not null,
- contact_name varchar2(50),
- CONSTRAINT pk_supplier PRIMARY KEY (supplier_id,supplier_name)
- );
-
- create table tb_products
- (
- product_id number not null,
- product_name varchar2(100),
- supplier_name varchar2(50),
- supplier_id number not null,
- constraint fk_products_supplier foreign key (supplier_id,supplier_name) references tb_supplier(supplier_id,supplier_name)
- );
b. 使用alter table命令定义
- ALTER TABLE table_name
- ADD CONSTRAINT constraint_name
- FOREIGN KEY (column1, column2,...column_n)
- REFERENCES parent_table (column1,column2,...column_n);
(1)创建多列的外键- create table tb_supplier
- (
- supplier_id number not null,
- supplier_name varchar2(50) not null,
- contact_name varchar2(50),
- CONSTRAINT pk_supplier PRIMARY KEY (supplier_id,supplier_name)
- );
-
- create table tb_products
- (
- product_id number not null,
- product_name varchar2(100),
- supplier_name varchar2(50),
- supplier_id number not null
- );
-
- --使用alter table创建外键
- alter table tb_products
- add constraint fk_products_supplier
- foreign key (supplier_id,supplier_name)
- references tb_supplier(supplier_id,supplier_name);
单列不赘述了。
|