Oracle Foreign Key usage (1), oracleforeign

Oracle Foreign Key usage (1), oracleforeign

Oracle Foreign Key usage (1)

1. Objectives

Demonstrate how to use a foreign key in an Oracle database

2. What is a foreign key?

1) in Oracle databases, foreign keys are one of the methods used to achieve the integrity of the reference. In an image, a foreign key is the column value of the table that defines the foreign key must appear in another table.

2) The referenced table is called the parent table, and the table that creates the foreign key is called the child table ). The foreign key in the child table is associated with the primary key in the parent table.

3) Foreign keys can be defined during TABLE creation or created using the alter table statement.

3. Define Foreign keys when creating a table

Syntax:

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

Example 1: Based on the foreign key of a Single Column

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

Example 2: Foreign keys based on multiple columns

drop table TB_PRODUCTS;drop table TB_SUPPLIER;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));

4. Use the alter table command to create a foreign key

Syntax:

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

Example:

Drop table TB_PRODUCTS; drop table values; create table TB_SUPPLIER (supplier_id number not null, supplier_name varchar2 (50) not null, contact_name varchar2 (50), CONSTRAINT pk_supplier primary key (values, values, supplier_name); create table tb_products (product_id number not null, product_name varchar2 (100), supplier_name varchar2 (50), supplier_id number not null ); -- use alter table to create the foreign key alter table tb_products add constraint fk_products_supplier foreign key (supplier_id, supplier_name) references tb_supplier (supplier_id, supplier_name );

Bytes -------------------------------------------------------------------------------------------------------------------

If you encounter any problems during your attempt or my code is incorrect, please correct me. Thank you very much!

Contact: david.louis.tian@outlook.com

Copyright @: reprinted, please indicate the source!
Bytes --------------------------------------------------------------------------------------------------------------------

Is the foreign key in oracle different from that in w3school?

The foreign key does not need to be written in Oracle.

You can also create a table and then use alter table to add a primary key.
Alter table Orders add constraint fk_1 foreign key (Id_P) REFERENCES Persons (Id_P );

The foreign key constraint in oracle can be added at the row level.

The foreign key constraint can be Row-level or table-level.
Row level: the Foreign keys of a single column are defined on the row level.
Table-level: Foreign keys of compound columns are defined at the table level.
Example:
Create Table T_xsml (-- Student Table
Xsbh char (8) Primary Key,
Xsxm varchar2 (8) Not null,
Xsxb char (1 ));
Create Table t_kcrf (-- course schedule
Zybm char (3), -- professional Encoding
Zymc varchar (20 ),
Kcbm char (4 ),
Primary key (zybm, kcbm ));
Create Table T_xscjb (-- External Table
Xsbh char (8) References T_sxml, -- foreign key of the student table
Zybm char (3 ),
Kcbm char (4 ),
Pscj varchar2 (6 ),
Kscj varchar2 (6 ),
Foreign Key (zybm, kcbm) References t_kcmc (zybm, kcbm); -- Foreign Key of the curriculum