Primary Key and Foreign Key Integrity Constraint in DBMS with Example
Primary key constraint
A primary key constraint is a field in a table which uniquely identifies each rows/records in a database table.
Characteristics of Primary Key :
- Primary key constraint must contain unique values.
- Primary key constraint column cannot have null values.
- Table can have only one primary key.
- If multiple fields are used as a primary key, then they are called as composite key.
CREATE TABLE employee ( EmpID int NOT NULL, Name varchar(30) NOT NULL, Address varchar(50), Salary int, Sex char(1) DEFAULT='M', PRIMARY KEY (EmpID) );
Primary key applied on EmpID column, therefore now the column cannot be left blank nor duplicate values can exist.
A table can contain only one primary key constraint, but can have several many constraints.
ADD or DROP a PRIMARY KEY using ALTER TABLE Statement :
Alter table statement is used, if we want to add or drop constraint later.
Add a primary key
ALTER TABLE table_name ADD CONSTRAINT constraint_name PRIMARY KEY (col1, col2,....,coln);
ALTER TABLE employee ADD CONSTRAINT employee_pk PRIMARY KEY (EmpID);
By using alter table statement, you can add PRIMARY KEY constraint on the existing table employee named employee_pk.
DROP a Primary Key :
ALTER TABLE table_name DROP CONSTRAINT constraint_name;
ALTER TABLE employee DROP CONSTRAINT employee_pk;
DISABLE a Primary Key
ALTER TABLE table_name DISABLE CONSTRAINT constraint_name;
ALTER TABLE employee DISABLE CONSTRAINT employee_pk;
ENABLE a Primary Key :
ALTER TABLE table_name ENABLE CONSTRAINT constraint_name;
ALTER TABLE employee ENABLE CONSTRAINT employee_pk;
Foreign key constraint
- If foreign key is a field which points to the primary key of another table. Means that, the same value in one table must also appear in another table.
- The reference table, means the table which contains primary key is called the parent table and the table with the foreign key is called the child table.
- If foreign key field cannot contain values, which are not present in the parent tables. In other words, it is used to enforce referential integrity between tables in a relational database.
- Primary key field cannot contain null values but foreign key field can contain null values.
- Foreign key constraint can be defined by a CREATE TABLE statement or an ALTER TABLE statement.
A foreign key constraint can refer to the columns of tables in the same database for within the same table.
CREATE TABLE table_name ( column1 datatype, column2 datatype, ..... CONSTRAINT fk_column FOREIGN KEY (column1,column2,....) REFERENCES parent_table (column1,column2,.....) );
PRIMARY KEY table : "employee" as defined above. FOREIGN KEY table : CREATE TABLE Department ( Eno int, Dno int NOT NULL, Dname varchar(30), PRIMARY KEY (Eno,Dno), FOREIGN KEY (Eno) REFERENCES employee(EmpID) );
- The PRIMARY KEY table contains 5 columns- (EmpID, Name, Address, Salary, Sex) , in which EmpID is the Primary Key.
- The other table, means the FOREIGN KEY table contains 3 columns – (Eno, Dno, Dname), in which Eno and Dno combined is the primary key. Eno has been considered as foreign key as it will take the references from employee table (EmpID).
- Foreign Key accept only those values which are present in primary key table or NULL values. So, while inserting any value in Department table, the column Eno would accept only those values which are already in table Employee, EmpID column or it will accept NULL values.
ADD a FOREIGN KEY using ALTER TABLE Statement
ADD a FOREIGN KEY
ALTER TABLE table_name ADD CONSTRAINT constraint_name FOREIGN KEY (col1, col2,....) REFERENCES parent_table (col1, col2,...)
ALTER TABLE Department ADD CONSTRAINT FK_employee FOREIGN KEY (Eno) REFERENCES employee(EmpID);
DELETION of a Row :
We cannot delete a row that contains a primary key that is used as a foreign key in another table. For example :
Query 1 : DELETE FROM employee where EmpID = 1000; × Query 2 : DELETE FROM employee; × Query 3 : DELETE FROM Department where Eno = 1002; √
- The Query 1 and Query 2 would return an error, because the column EmpID is a Primary Key and it has been referenced by a Foreign Key table (Department).
- But Query 3 would successfully executed.
- But it is possible with the use of ON DELETE CASCADE and ON DELETE SET NULL commands.
ON DELETE CASCADE and ON DELETE SET NULL
- ON DELETE CASCADE – delete the dependent rows in the child table, only when a row in the parent table is deleted.
- ON DELETE SET NULL- is used when you want to convert dependent foreign key values to null.
Without using ON DELETE CASCADE or ON DELETE SET NULL command, you cannot delete the row of parent table, if it is referenced in the child table.
Guidelines for primary keys and foreign keys
- Primary key does not use duplicate values.
- Foreign key is based on the values from the primary key and purely logical and physical pointers.
- The values of foreign key must match the existing primary key value.
- Foreign key must refer either a primary key or unique key column.
Comparison of primary key and foreign key
- Primary key is unique but foreign key need not be unique.
- Primary key is not null and foreign key can be null, foreign key references a primary key in another table.
- Primary key is used to identify a row, where as foreign key refers to a column or combination of columns.
- Primary key is the parent table and foreign key is a child table.
|Integrity Constraints||Complete Format of SELECT Command|
Incoming search terms:
- foreign key constraint in dbms
- one dbms program with reference key and foreign key
- characteristics of primary key
- create table with primary key and foreign key with all constraint program in dbms
- Key integrity dbms
- foriegnkey in dbms
- foreign key in dbms with example
- difference between on delete set null and on delete cascade
- difference between on delete cascade and on delete setnull
- what is the difference between ON DELETE SET NULL and ON DELETE CASCADE in dbms