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!

 

SQL Constraints #-- Samples

SQL Constraints

SQL constraints are used to specify rules for the data in a table.

 If there is any violation between the constraint and the data action, the action is aborted by the constraint.

Constraints can be specified when the table is created (inside the CREATE TABLE statement) or after the table is created (inside the ALTER TABLE statement).

SQL CREATE TABLE + CONSTRAINT Syntax

CREATE TABLE table_name
(
column_name1 data_type(size) constraint_name,
column_name2 data_type(size) constraint_name,
column_name3 data_type(size) constraint_name,
....
);

 In SQL, we have the following constraints:

· NOT NULL - Indicates that a column cannot store NULL value

· UNIQUE - Ensures that each row for a column must have a unique value

· PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Ensures that a column (or combination of two or more columns) have an unique identity which helps to find a particular record in a table more easily and quickly

· FOREIGN KEY - Ensure the referential integrity of the data in one table to match values in another table

· CHECK - Ensures that the value in a column meets a specific condition

· DEFAULT - Specifies a default value when specified none for this column

SQL NOT NULL Constraint

The NOT NULL constraint enforces a column to NOT accept NULL values.

The NOT NULL constraint enforces a field to always contain a value. This means that you cannot insert a new record, or update a record without adding a value to this field.

The following SQL enforces the "P_Id" column and the "LastName" column to not accept NULL values:

CREATE TABLE PersonsNotNull
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

SQL UNIQUE Constraint

The UNIQUE constraint uniquely identifies each record in a database table.

The UNIQUE and PRIMARY KEY constraints both provide a guarantee for uniqueness for a column or set of columns.

A PRIMARY KEY constraint automatically has a UNIQUE constraint defined on it.

Note that you can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table.

SQL UNIQUE Constraint on CREATE TABLE

The following SQL creates a UNIQUE constraint on the "P_Id" column when the "Persons" table is created:

CREATE TABLE Persons
(
P_Id int NOT NULL UNIQUE,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

To DROP a UNIQUE Constraint

To drop a UNIQUE constraint, use the following SQL:

ALTER TABLE Persons
DROP CONSTRAINT uc_PersonID

SQL CHECK Constraint

The CHECK constraint is used to limit the value range that can be placed in a column.

If you define a CHECK constraint on a single column it allows only certain values for this column.

If you define a CHECK constraint on a table it can limit the values in certain columns based on values in other columns in the row.

SQL CHECK Constraint on CREATE TABLE

The following SQL creates a CHECK constraint on the "P_Id" column when the "Persons" table is created. The CHECK constraint specifies that the column "P_Id" must only include integers greater than 0.

CREATE TABLE Persons
(
P_Id int NOT NULL CHECK (P_Id>0),
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

SQL CHECK Constraint on ALTER TABLE

To create a CHECK constraint on the "P_Id" column when the table is already created, use the following SQL:

ALTER TABLE Persons
ADD CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes')

SQL DEFAULT Constraint on ALTER TABLE

To create a DEFAULT constraint on the "City" column when the table is already created, use the following SQL:

MySQL:

ALTER TABLE Persons
ALTER City SET DEFAULT 'SANDNES'

SQL Server / MS Access:

ALTER TABLE Persons
ALTER COLUMN City SET DEFAULT 'SANDNES'

Oracle:

ALTER TABLE Persons
MODIFY City DEFAULT 'SANDNES'

To DROP a DEFAULT Constraint

To drop a DEFAULT constraint, use the following SQL:

MySQL:

ALTER TABLE Persons
ALTER City DROP DEFAULT

SQL Server / Oracle / MS Access:

ALTER TABLE Persons
ALTER COLUMN City DROP DEFAULT

PRIMARY KEY --#

Primary key is an Input/output Data constraint. It serves the purpose of uniquely identifying the rows in a table
Technically Primary key constraint is the combination of NOT NULL and UNIQUE constraints.
There are Two types of Primary key.

1 Simple Primary key and

2 Composite Primary Key.

Primary key which involves only one column of a table is called Simple Primary key whereas the primary key which involvesmore than one columns of a table is called Composite Primary key.
More than one column doesn't mean that you can give any number of columns. This still has some restrictions. A composite key in oracle can have maximum of 32 columns only.

Primary key has a single function of identifying a unique row in the table. In case the simple primary key fails to identify that unique row, the user must then define a composite primary key.
You cannot Delete a Primary key if it is referenced by a foreign key in some other table. There is a unique index created automatically by Oracle so that the requirement of the uniqueness of the PRIMARY KEY constraint is fulfilled.

There are few things which you should know about primary key. (Features of Primary key)

1. A table can have only One Primary key no matter whether its Simple Primary Key Or Composite Primary Key.
2. Columns which are participating in Primary Key cannot have NULL values. This means you cannot leave them unattended or you cannot put NULL value into them.

3 A table can have only One Primary key no matter whether its Simple Primary Key Or Composite Primary Key.

4 Columns which are participating in Primary Key cannot have NULL values. This means you cannot leave them unattended or you cannot put NULL value into them.

5 As primary key is all about Row or Record’s uniqueness thus it will not allow duplicate values.

6 When a Primary Key constraint has been defined on multiple columns then its values can be duplicated provided the duplication is happening within one single column. However the combination of values of all the columns defining each primary key constraint should be unique.

7 Data-types such as LOB, LONG, LONG RAW, VARRAY, NESTED TABLE, BFILE, REF, TIMESTAMP WITH TIME ZONE, or user-defined type are not allowed with the columns which are part of Primary key. Any attempt of creating a primary key with the column of these data-types will raise SQL Error: ORA-02269.

8 The size of the primary key cannot exceed approximately one database block.

9 As I have already mentioned above that a composite primary key can have 32 columns maximum.

10 The Primary key and Unique key should never be designated as the same column or combination of columns.

11 You cannot specify a primary key when creating a sub view in an inheritance hierarchy. The primary key can be specified only for the top-level (root) view.

12 Unique cluster Index gets created automatically on the time creating Primary key.

13 Although it is not necessary for you to define a primary key yet it is always recommended to do so.

How to create a primary key or How to Define a Primary key.

You can define a Primary key either while CREATING a table or After Creating a table using ALTER TABLE statement.

Let’s start with Defining a primary key Using CREATE TABLE statement. There are two ways of defining a Primary Key using CREATE TABLE

14 Defining Primary Key at Column Level and

15 Defining Primary Key at Table Level

efining Primary Key using Create table

1. Defining primary Key at Column Level.

This way of defining constraint is called column level because we define constraints with the column definition while creating table, Here we Define a primary key while defining the column during Create table.
For example 
Let’s Create a table by the name of “Product_master” 

CREATE TABLE product_master
 (
  Product_id    NUMBER(3)  PRIMARY KEY,
  Product_name  VARCHAR2(30),
  Product_price  NUMBER(5)
  );

To make a column - a primary key, you just have to put keyword primary key after datatype and size in column definition as we did above with our first column.

But its always a good practice to provide a meaningful name to your constraint while creating it because oracle database saves all the constraints in Data dictionary and constraints with meaningful name are easy to reference.

So Lets modify the above query and provide a meaningful name to our Primary key constraint.

CREATE TABLE product_master
 (
  Product_id    NUMBER(3)  CONSTRAINT  pmst_pid_pk  PRIMARY KEY,
  Product_name  VARCHAR2(30),
  Product_price  NUMBER(5)
  );

2. Defining Primary Key at Table Level

This way of defining constraint is called table level because we define constraints explicitly in create table statement. Here in this type of primary key creation we first define all our columns and after defining all the columns we define our constraint
for example 
Lets again Create a table this time by the name of ORDER_MASTER

CREATE TABLE order_master
 (
  order_id   NUMBER(3),
  order_data   DATE,
  order_qnty   NUMBER(5),
  CONSTRAINT   ordmstr_oid_pk   PRIMARY KEY ( order_id )
 );

Defining Primary Key using ALTER TABLE statements.

When we have already created table and then want to add primary key constraint then we use ALTER TABLE statement for adding Primary Key constraint.

Let’s say we have a table “Customer” with 3 columns Cust_id, cust_name, phone_no and we don’t have any Primary Key c onstraint on any column and now we want to add Primary Key constraint on cust_id column.
To do this task we will use ALTER TABLE statement as

ALTER TABLE customer ADD CONSTRAINT cust_cid_pk PRIMARY KEY (cust_id);

Similarly if you want to define a composite primary key on customer table then

ALTER TABLE customer ADD CONSTRAINT cust_cid_pk PRIMARY KEY (cust_id,phone_number);

COMPOSITE PRIMARY KEY

As we already know that Primary Key which involves more than one column of a table is known as Composite primary Key.

We can only define composite primary key at table level. As composite key requires more than one column thus it becomes mandatory to define column of a table first and then primary key.
Let see how

CREATE TABLE customer
 (
  cust_id   NUMBER(3),
  cust_name  VARCHAR2(3),
  phone_no  NUMBER(10),
  CONSTRAINT  cust_cid_pk  PRIMARY KEY ( cust_id, phone_no)
);

How To Drop Primary Key Constraint

To drop primary key constraint we again use ALTER TABLE statement 
Syntax for dropping a constraint is

ALTER TABLE  table_name  DROP CONSTRAINT  constraint_name;

For Example.

ALTER TABLE  customer  DROP CONSTRAINT  cust_cid_pk;

ENABLE & DISABLE PRIMARY KEY

You can enable or Disable primary key constraint by using constraint name with ALTER TABLE statement.
For example Let’s say you want to disable cust_cid_pk constraint which we earlier defined on Customer table.

Disable Primary key constraint

ALTER TABLE  customer  DISABLE CONSTRAINT  cust_cid_pk;

or if you want to enable this constraint then simply write

Enable Primary key constraint

ALTER TABLE  customer  ENABLE CONSTRAINT  cust_cid_pk;

HOW TO CHECK CONSTRAINT ON A TABLE

As we know that DESC statement will show nothing about constraints on a table. But Oracle Database provides us several DATA DICTIONARIES for checking or Describing all the constraint which we have defined on our table.
These Data Dictionaries are

16 USER_CONSTRAINTS

17 USER_CONS_COLUMNS

18 .

Where USER_CONSTRAINTS will give a brief about constraint on table. USER_CONS_COLUMNS is a Data Dictionary which holds information about columns of a table by using this Data dictionary you can have detail information about the constraint on a table. 
For example
Let’s say you want to see constraint detail on customer table

USER_CONSTRAINTS 

SELECT
  CONSTRAINT_NAME,
  CONSTRAINT_TYPE,
  TABLE_NAME,
  STATUS,
  INDEX_NAME
FROM  user_CONSTRAINTS  WHERE  table_name = 'CUSTOMER';

USER_CONS_COLUMNS

SELECT 
  CONSTRAINT_NAME,
  TABLE_NAME,
  COLUMN_NAME,
  POSITION
FROM  user_cons_columns  WHERE  table_name = 'CUSTOMER';

So that’s all about PRIMARY KEY. Hope it gives you a detailed insight into the concept. You can visit my YouTube channel for tutorials on Primary Key. Do like and Subscribe my videos.