SQL Foreign Key Constraint With Example

Constraints Definition:

SQL Constraints are rules used to limit the type of data that can go into a table, to maintain the accuracy and integrity of the data inside table.

Constraints provide a standard mechanism to maintain the accuracy and integrity of the data inside a database table.

5. FOREIGHN KEY

A Foreign key is a field which can uniquely identify each row in a another table. And this constraint is used to specify a field as Foreign key.

A Foreign Key Constraint is used to establish a relationship between two tables where one column is a Primary Key of the table and the other column from other table is referenced to the Primary Key column.

A Foreign Key column can also have reference to Unique Key column of another table.

Syntax:

CREATE TABLE table_name(
    column_name datatype[(size)] [ NULL | NOT NULL ] REFERENCES another_table_name(column_name) 
        [ ON UPDATE | ON DELETE 
                [ NO ACTION | SET NULL | SET DEFAULT | CASCADE ] 
        ],
    ....
);

Example:

SQL> CREATE TABLE emp_info(
    no NUMBER(3,0) PRIMARY KEY,
    name VARCHAR(30),
    address VARCHAR(70),
    contact_no NUMBER(12,0)
);
------------------------
Table created.

 
SQL> CREATE TABLE emp_salary(
    no NUMBER(3,0) PRIMARY KEY,
    users_no NUMBER(3,0) REFERENCES emp_info(no),
    salary NUMBER(12)
);
----------------------------
Table created

Read Also: