Foreign key in detail .

Foreign Key.

Oracle Database Foreign Key Explained in Detail

In the last tutorial we learnt about Primary key constraint and today Another topic in oracle database that I would like to elaborate is referential integrity constraint or Foreign Key.
Foreign key is an Input/output data constraint which is also known as referential integrity constraint.
Foreign key represent a link or say a relationship between columns of tables.

Similar to primary key constraint Foreign Key constraint is also of two types.

1. Simple Foreign key constraint and
2. Composite Foreign key constraint.

Constraint which involves only one column in foreign key in child table and one column in reference key in parent table is called Simple Foreign Key. While the constraint which involves more than one column in foreign key in child table and more than one column in reference key in the parent table is called Composite Foreign Key.

There are few things which you should know about foreign key ( Features of Foreign Key)

1. You cannot define a foreign key constraint in a CREATE TABLE statement that contains an AS sub query clause. Instead, you must create the table without the constraint and then add it later with an ALTER TABLE statement
2. None of the columns in the foreign key can be of LOB, LONG, LONG RAW, VARRAY, NESTED TABLE, BFILE, REF, TIMESTAMP WITH TIME ZONE, or user-defined type. However, the primary key can contain a column of TIMESTAMP WITH LOCAL TIME ZONE.
3. A composite foreign key cannot have more than 32 columns.
4. Referenced key in parent table must either be Primary Key or Unique Key.
5. Records in parent table cannot be updated if child record exist.
6. Foreign Key Constraint can be specified on child table not on parent table

Foreign key involves two different tables. First one is PARENT TABLE or referenced Table and second one is CHILD TABLE or foreign table.

1. The column of parent table which will get referenced by foreign key must be either Primary Key or Unique Key.
2. Column(s) in child table can contain NULL or Duplicate values while vice versa is not true and.
3. Column(s) of parent table column(s) of child table which are participating in foreign key should be of same data-type and size (column width).

How to create/ Define Foreign key

You can define foreign key constraint either by using CREATE TABLE statement or by using ALTER TABLE statement.

1. Defining Foreign Key Using Create table at Column Level

This way of defining constraint is called column level because we define constraint with column definition while creating table.

Syntax  Column_name   Datatype(size)   REFERENCES   parent_table_name (parent_column_name)

For example
To demonstrate this we will use two tables parent table with the name of Authors and child table with the name of Books Parent table authors is a simple table with 2 columns Author_id and Author_name. Where Author_id is a Primary key column.You can add as many column as you want.
Read how to create table and how to define primary key on a table.

CREATE TABLE author
 (
  author_id   NUMBER(3)   CONSTRAINT   athr_aid_pk   PRIMARY KEY,
  author_name   VARCHAR2(30)
 );

Now let’s create our child table BOOKS. The structure of this table contain columns book_id which will be the primary key for this table, book_title and Book_price and the 4th column will be book_author_id this column will be the foreign key which will reference the author_id column of author table you can give whatever name to this column but data-type and the size (column width) of this column must be the same as of author_id column in author table.

CREATE TABLE books
  (
  book_id   NUMBER(3),
  book_title   VARCHAR2(30),
  book_price   NUMBER(3),
  book_author_id    NUMBER(3)   CONSTRAINT   bok_ai_fk   REFERENCES   author(author_id)
  );

2.How to define foreign key using CREATE TABLE at table Level.

To define foreign key Using create table at table level. You have to define all the column of your child table first then you have to define foreign key at the end of the table.
Let’s see the syntax first

CONSTRAINT   constraint_name   FOREIGN KEY(child_table_column)   REFERENCES   Parent_table_name(parent_table_column)

To demonstrate how to define foreign key using create table at table level I’ll recreate our child table BOOKS.

CREATE TABLE books
  (
  book_id  NUMBER(3)  CONSTRAINT  bok_bi_pk  PRIMARY KEY,
  book_title   VARCHAR2(30),
  book_price  NUMBER(3),
  book_author_id   NUMBER(3),
  CONSTRAINT  bok_ai_fk  FOREIGN KEY  (book_author_id) REFERENCES  author(author_id)
  );

As you can see I defined all the column first and then I define foreign Key constraint in the last statement of Create table.

Define Foreign Key Using ALTER TABLE statement.

We define Foreign Key through ALTER TABLE statement when we already have a table and then we want to emphasize the constraint over that
Suppose we have a simple table with primary key by the name of BOOKS [Please watch my How to create table tutorial] and now you have emphasize the FOREIGN KEY constraint on this table. In this scenario we will use ALTER TABLE statement.
Let’s how
First see the syntax

ALTER TABLE  child_table_name  ADD  FOREIGN KEY  (child_column)  REFERENCES  parent_table_name(parent_column)

For Example

ALTER TABLE  books  ADD  CONSTRAINT  bok_ai_fk  FOREIGN KEY  (book_author_id) REFERENCES  author(author_id);

That’s all you have to do.

If you try to delete parent table which is having primary or unique key which was referenced by child table then oracle will give you a SQL Error: ORA-02449.But you can still drop child table without any error.

So there you are! This is the foreign key concept in brief. Please remember that there are many small topics that branch out from this main foreign key concept and are beyond the scope of this article. Hope you like it. Please share with your friends and colleagues.

Also you can visit my YouTube channel for the video tutorial explaining foreign key concept. 

On Delete Set Null.

On Delete Set Null clause of Foreign Key Explained in Detail

As we discussed in our last tutorial that Foreign key constraint establishes a link / relation between PARENT and CHILD table. Because of this link we cannot update or delete the rows of parent table. Foreign key is defined in the child table and parent table contains the reference column.

By default Oracle engine is set to ON DELETE NO ACTION clause when you define a simple foreign key. This means that you are allowed to update the rows in parent table however you cannot delete rows from parent table.

Activity

---

Go To my previous tutorial on foreign key and create parent (authors) and child (books) table with foreign key and after creating them try to drop the parent (authors) table and see what will happen.
(To drop parent table AUTHORS execute DROP TABLE AUTHORS; ddl) 

 

 

This default behavior is called Restrict rule. This Restrict rule doesn’t allow user to delete or update reference data in parent table.

But we can change this default behavior of oracle, either to SET NULL or to DELTE CASCADE by

1. ON DELETE SET NULL or
2. ON DELETE CASCADE

Referential action. Let’s see how
First we will see ON DELETE SET NULL.

Remember parent table holds REFERENCE COLUMN and Child table holds FOREIGN KEY COLUMN.

When you change default restrict rule (ON DELETE NO ACTION) to ON DELETE SET NULL then the corresponding value of foreign key in child table will set to NULL on deleting the rows from parent table.

How to set ON DELETE SET NULL clause with foreign key.

Parent table: Authors
Note: Parent table is simply a table with primary key constraint on Author id column which in turn is the reference column.

CREATE TABLE author
 (
  author_id  NUMBER(3)  CONSTRAINT  athr_aid_pk  PRIMARY KEY,
  author_name  VARCHAR2(30)
 );

Child table: Books

CREATE TABLE books
 (
  book_id  NUMBER(3),
  book_title  VARCHAR2(30),
  book_price  NUMBER(3),
  book_author_id  NUMBER(3)  CONSTRAINT  bok_ai_fk  REFERENCES  author(author_id)  ON DELETE SET NULL
 );

This is how you define foreign key with ON DELETE SET NULL with create table in column level.

You can check this constraint by executing the query - USER_CONSTRAINTS data dictionary

SELECT
   constraint_name, delete_rule
FROM user_constraints
WHERE table_name = 'BOOKS' ;

So guys, this is the process by which you can change Oracle’s default behavior to ON DELETE SET NULL clause using foreign key. Hope you find it useful.
Do read my next article explaining ON DELETE CASCADE clause. You can also watch my YouTube tutorial on the same. Please do like, share and subscribe!