binglingshui的专栏

我在生活的小路上捡拾阳光,虽然沾染了尘土,我还是把她们收入行囊,因为我知道,快与不快,都是必须经历的…… ...

Oracle/PLSQL: Foreign Keys

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

阅读更多
文章标签: table null reference
个人分类: 数据库知识
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

不良信息举报

Oracle/PLSQL: Foreign Keys

最多只允许输入30个字

加入CSDN,享受更精准的内容推荐,与500万程序员共同成长!
关闭
关闭