Why primary & foreign key is required for database table

we know that when we create pk on any table then a cluster index is created and pk do not allow null and duplicate value. so instead of pk we can create unique key on a fields then cluster index will be created and duplicate data will not be inserted. it would be helpful if some one discuss briefly why pk is required and its advantage.

the same way when we have table called employee and employee_details and both have same field called empid and when we create relation from sql server diagram then automatically a fk will be created on employee_details table but i like to know if we do not create fk on employee_details table then we also can join two table for fetching data. so my question is if we can join 2 table to fetch data without fk then what is importance of fk and what will be the advantage of fk.

it will be helpful if some one discuss my points in details example for better understanding. thanks

Hii tri_inn Your First Question is About the primary key and Unique Key. The basic diff b/w Primary key and unique key is:
Primary Key:

Can be only one in a table
It never allows null values
Primary Key is unique key identifier and can not be null and must be unique.

Unique Key:

Can be more than one unique key in one table.
Unique key can have null values
It can be a candidate key
Unique key can be null and may not be unique.

Now i Question Rise Why should we use a PK instead of UK.It is not necessray it depend upon your Requirement. Before discuss upon that when should we use a PK and when UK.
We should know the work of PK and UK.

Work of PK and UK in a table:
1.Primary Key is used to identify a row (record) in a table whereas Unique-key is to prevent duplicate values in a column.
2.You can't change or delete primary values but Unique-key values can.

You cannot create a unique index on a single column if that column contains NULL in more than one row. Similarly, you cannot create a unique index on multiple columns if the combination of columns contains NULL in more than one row. These are treated as duplicate values for indexing purposes.

So if you want to identify a row identity(Unique) then you should use PK and if you want to prevent duplicate data then use UK.

Now second question is that Why Should we use FK.The main reason are
1. foreign key is used to make the relation of one table to another table ,while updating the table both will affect at the same.

2.Let take an example we have two table User_Table(contain PK) and User_Table2(FK)
then

By making it a foreign key you are enforcing the relationship between the tables If you have a foreign key you can't put a user_id in User_Table2 that is not in the user_table1.

3.In Foreign Key table You can set Update and Delete Rule which play a important role:

NO ACTION: Configuring "NO ACTION" means just that: when a parent key is modified or deleted from the database, no special action is taken.

SET NULL: If the configured action is "SET NULL", then when a parent key is deleted (for ON DELETE SET NULL) or modified (for ON UPDATE SET NULL), the child key columns of all rows in the child table that mapped to the parent key are set to contain SQL NULL values.
SET DEFAULT: The "SET DEFAULT" actions are similar to "SET NULL", except that each of the child key columns is set to contain the columns default value instead of NULL. Refer to the CREATE TABLE documentation for details on how default values are assigned to table columns.
CASCADE: A "CASCADE" action propagates the delete or update operation on the parent key to each dependent child key. For an "ON DELETE CASCADE" action, this means that each row in the child table that was associated with the deleted parent row is also deleted. For an "ON UPDATE CASCADE" action, it means that the values stored in each dependent child key are modified to match the new parent key values.
RESTRICT: The "RESTRICT" action means that the application is prohibited from deleting (for ON DELETE RESTRICT) or modifying (for ON UPDATE RESTRICT) a parent key when there exists one or more child keys mapped to it. The difference between the effect of a RESTRICT action and normal foreign key constraint enforcement is that the RESTRICT action processing happens as soon as the field is updated - not at the end of the current statement as it would with an immediate constraint, or at the end of the current transaction as it would with a deferred constraint. Even if the foreign key constraint it is attached to is deferred, configuring a RESTRICT action causes SQLite to return an error immediately if a parent key with dependent child keys is deleted or modified.

So use a FK on a table is Very Important .Hope it will help you

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

11y ago
thanks for reply but u were two brief and small explanation.

how PK allow us to identify row uniquely? is the reason = PK do not allow duplicate data and null data.........is it the reason?

we can use unique key because it also prevent duplicate data.

looking for your discussion. thanks
0
Vithal Wadje11y ago
Primary key is used and uniquely identify the records where as Unique key is used to maintain the relation ship between two table to avoid duplicate records
0
Pankaj Kumar Choudhary11y ago
Hii tri_inn Your First Question is About the primary key and Unique Key. The basic diff b/w Primary key and unique key is:
Primary Key:
- Can be only one in a table
- It never allows null values
- Primary Key is unique key identifier and can not be null and must be unique.
Unique Key:
- Can be more than one unique key in one table.
- Unique key can have null values
- It can be a candidate key
- Unique key can be null and may not be unique.
Now i Question Rise Why should we use a PK instead of UK.It is not necessray it depend upon your Requirement. Before discuss upon that when should we use a PK and when UK.
We should know the work of PK and UK.

Work of PK and UK in a table:
1.Primary Key is used to identify a row (record) in a table whereas Unique-key is to prevent duplicate values in a column.
2.You can't change or delete primary values but Unique-key values can.

You cannot create a unique index on a single column if that column contains NULL in more than one row. Similarly, you cannot create a unique index on multiple columns if the combination of columns contains NULL in more than one row. These are treated as duplicate values for indexing purposes.

So if you want to identify a row identity(Unique) then you should use PK and if you want to prevent duplicate data then use UK.

Now second question is that Why Should we use FK.The main reason are
1. foreign key is used to make the relation of one table to another table ,while updating the table both will affect at the same.

2.Let take an example we have two table User_Table(contain PK) and User_Table2(FK)
then

By making it a foreign key you are enforcing the relationship between the tables If you have a foreign key you can't put a user_id in User_Table2 that is not in the user_table1.

3.In Foreign Key table You can set Update and Delete Rule which play a important role:

NO ACTION: Configuring "NO ACTION" means just that: when a parent key is modified or deleted from the database, no special action is taken.
- SET NULL: If the configured action is "SET NULL", then when a parent key is deleted (for ON DELETE SET NULL) or modified (for ON UPDATE SET NULL), the child key columns of all rows in the child table that mapped to the parent key are set to contain SQL NULL values.
- SET DEFAULT: The "SET DEFAULT" actions are similar to "SET NULL", except that each of the child key columns is set to contain the columns default value instead of NULL. Refer to the CREATE TABLE documentation for details on how default values are assigned to table columns.
- CASCADE: A "CASCADE" action propagates the delete or update operation on the parent key to each dependent child key. For an "ON DELETE CASCADE" action, this means that each row in the child table that was associated with the deleted parent row is also deleted. For an "ON UPDATE CASCADE" action, it means that the values stored in each dependent child key are modified to match the new parent key values.
- RESTRICT: The "RESTRICT" action means that the application is prohibited from deleting (for ON DELETE RESTRICT) or modifying (for ON UPDATE RESTRICT) a parent key when there exists one or more child keys mapped to it. The difference between the effect of a RESTRICT action and normal foreign key constraint enforcement is that the RESTRICT action processing happens as soon as the field is updated - not at the end of the current statement as it would with an immediate constraint, or at the end of the current transaction as it would with a deferred constraint. Even if the foreign key constraint it is attached to is deferred, configuring a RESTRICT action causes SQLite to return an error immediately if a parent key with dependent child keys is deleted or modified.
So use a FK on a table is Very Important .Hope it will help you
0