Oracle/PLSQL: Foreign Keys

转载 2006年06月06日 09:02:00

Oracle/PLSQL: Foreign Keys


What is a foreign key?

A foreign key means that values in one table must also appear in another table.

The referenced table is called the parent table while the table with the foreign key is called the child table. The foreign key in the child table will generally reference a primary key in the parent table.

A foreign key can be defined in either a CREATE TABLE statement or an ALTER TABLE statement.

Using a CREATE TABLE statement

The syntax for creating a foreign key using a CREATE TABLE statement is:

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)
);

For example:

CREATE TABLE supplier

(

supplier_id

numeric(10)

not null,

 

supplier_name

varchar2(50)

not null,

 

contact_name

varchar2(50),

 
 

CONSTRAINT supplier_pk PRIMARY KEY (supplier_id)

);

 

CREATE TABLE products

(

product_id

numeric(10)

not null,

 

supplier_id

numeric(10)

not null,

 

CONSTRAINT fk_supplier

 

  FOREIGN KEY (supplier_id)

 

  REFERENCES supplier(supplier_id)

);

In this example, we've created a primary key on the supplier table called supplier_pk. It consists of only one field - the supplier_id field. Then we've created a foreign key called fk_supplier on the products table that references the supplier table based on the supplier_id field.

We could also create a foreign key with more than one field as in the example below:

CREATE TABLE supplier

(

supplier_id

numeric(10)

not null,

 

supplier_name

varchar2(50)

not null,

 

contact_name

varchar2(50),

 
 

CONSTRAINT supplier_pk PRIMARY KEY (supplier_id, supplier_name)

);

 

CREATE TABLE products

(

product_id

numeric(10)

not null,

 

supplier_id

numeric(10)

not null,

 

supplier_name

varchar2(50)

not null,

 

CONSTRAINT fk_supplier_comp

 

  FOREIGN KEY (supplier_id, supplier_name)

 

  REFERENCES supplier(supplier_id, supplier_name)

);

In this example, our foreign key called fk_foreign_comp references the supplier table based on two fields - the supplier_id and supplier_name fields.

Using an ALTER TABLE statement

The syntax for creating a foreign key in an ALTER TABLE statement is:

ALTER TABLE table_name
add CONSTRAINT constraint_name
  FOREIGN KEY (column1, column2, ... column_n)
  REFERENCES parent_table (column1, column2, ... column_n);

For example:

ALTER TABLE products
add CONSTRAINT fk_supplier
  FOREIGN KEY (supplier_id)
  REFERENCES supplier(supplier_id);

In this example, we've created a foreign key called fk_supplier that references the supplier table based on the supplier_id field.

We could also create a foreign key with more than one field as in the example below:

ALTER TABLE products
add CONSTRAINT fk_supplier
  FOREIGN KEY (supplier_id, supplier_name)
  REFERENCES supplier(supplier_id, supplier_name);

相关文章推荐

Index Monitoring and Foreign Keys – Caution

Index Monitoring and Foreign Keys – Caution (Kid A) September 12, 2008 Posted by Richard Foote in ...
  • loryliu
  • loryliu
  • 2016年07月27日 21:32
  • 118

truncate table 错误 ORA-02266: unique/primary keys in table referenced by enabled foreign keys

这两天在做一个feature

Oracle之外键(Foreign Key)用法详解(一)

如何在Oracle数据库中使用外键FOREIGN KEY

聊聊Oracle外键约束(Foreign Key)的几个操作选项

原文链接: 聊聊Oracle外键约束(Foreign Key)的几个操作选项 关系型数据库是以数据表和关系作为两大对象基础。数据表是以二维关系将数据组织在DBMS中,而关系建立数据表之间...

Oracle get the Primary and foreign Key Column

获取单个表主键 SELECT cols.table_name, cols.column_name, cols.position, cons.status, cons.owner FROM all_co...

oracle的完整性约束:check/not null/unique/primary/foreign key

oracle的完整性约束   完整性约束用于增强数据的完整性,Oracle提供了5种完整性约束:     Check     NOT NULL     Unique     Primar...

Oracle之外键(Foreign Key)用法详解(二)- 级联删除(DELETE CASCADE)

Oracle外键中使用级联删除,ON CASCADE DELETE

ORACLE foreign key

很少用到foreign key ,网上找的资料研究下: foreign key就是表与表之间的某种约定的关系,由于这种关系的存在,能够让表与表之间的数据,更加的完整,关连性更强。 二,外键维护数据完整...
  • zftang
  • zftang
  • 2011年03月01日 15:17
  • 4785

oracle约束+disable与enable约束+为表添加约束+修改表约束的状态+primary key+not null+unique+foreign key+外键删除数据,可以使用的级联操作

oracle约束 分类 表级约束:应用于表,对表的多个起作用 列级约束:应用于表的一列,只对该列起作用disable与enable约束 控制约束的约束 控制表的约束是禁用还是激活状态 di...

PLSQL连接Oracle报错ORA-12541:TNS:无监听程序

用PLSQL连接Oracle时报错ORA-12541:TNS:无监听程序。网上有很多方法建议打开“Oracle Net Configuration Assistance”来重新配置监听程序。我建议在重...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:Oracle/PLSQL: Foreign Keys
举报原因:
原因补充:

(最多只允许输入30个字)