Oracle/PLSQL: Foreign Keys
来源:互联网 发布:人工智能技术介绍 编辑:程序博客网 时间:2024/05/16 09:30
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);
- Oracle/PLSQL: Foreign Keys
- Oracle/PLSQL: Foreign Keys
- Locks and Unindexed Foreign Keys
- Adding Primary Keys and Foreign Keys to a Table
- Index Monitoring and Foreign Keys – Caution
- Oracle Foreign key
- ORACLE foreign key
- 未加索引的外键(unindexed foreign keys)
- ORA-02449: unique/primary keys in table referenced by foreign keys
- ORA-02266: unique/primary keys in table referenced by enabled foreign keys
- truncate table 错误 ORA-02266: unique/primary keys in table referenced by enabled foreign keys
- ORA-02266: unique/primary keys in table referenced by enabled foreign keys
- oracle plsql
- oracle plsql
- oracle plsql
- oracle--PLSQL
- oracle plsql
- EF中的外键以及关联(Foreign Keys and Relationships (Associations))
- Thinking in JAVA websites
- ADO.net 中数据库连接方式
- 最好的Java程序员免费学习材料(English)
- 高效程序员应该养成的七个习惯
- 真他妈的贱!
- Oracle/PLSQL: Foreign Keys
- 介绍 IOC
- .net错误总结
- 《80前的前辈,你们都做了些什么?!》一文的正确解读及是是非非
- JS生成N级下拉菜单
- Windows 2000 Server架设SSH服务器
- Oracle Dates and Times
- 用VC写Assembly代码(5) --字符串
- 我喜欢的歌