MySQL Primary and Foreign keys.

Hello Friends,

This is one of my tutorials regarding the MySql primary and foreign keys. In this tutor I will be focusing on one of the main aspect of any database i.e. keys. Database keys are mostly used while accessing the database itself of its tables from any other source like web-application or simple code. This helps the developer to retrieve or manipulate the data from or in the database from the application itself using simple queries.

Keys are used as the Constraints in MySql. Keys are mainly used as the reference for the data or column of a particular table so that multiple tables can be easily combined for manipulation when there is need of accessing the data from multiple tables. The most commonly used keys in database are Primary key and
Foreign Key.

Primary Key

Primary key is a single field or combination of fields that defines the uniqueness of the record or a table. Once the field is defined as primary key then it does not allows the NULL value for that field, also it does not allows two fields to have same value i.e. it only accepts the unique values and not the duplicate values.

If the Primary key contains more than one field then the primary key is said to be Composite Primary Key.

Primary key can be created or defined in two ways-

a) While Creating Tables using CREATE TABLE statement:

Example:

Or

Now the above sql statements will create new table with name niktb1 where “id” will be the primary key.

b) Using the ALTER TABLE statement:-

The Primary key can also be defined after the table being created using Alter statement.

This query once executed will change the field id into the primary key if not defined previously. You can define Composite Primary key as

Similarly we can remove the primary key constraint from the table, for this we can use DROP statement. This will remove the field constraint of being unique but will not affect the existing values.
Example:

Now, take this into notice that when the primary key is defined its NOT NULL constraint is enabled i.e. its does not accept the null values and when the Primary key is DROPped then it does not disable the NOT NULL constraint it still exists and does not allow the Null values. To remove the NOT NULL constraint use the below statements for dropping the Primary Key.

We can also name the primary key using Constraints so that it can be used directly with the name. It can be achieved as-

Using ALTER TABLE Statement:

Now lets discuss-

Foreign Keys

It is the value of one table that must be the primary key of another table. As its name suggest it is dependent on foreign (other) table. For example, if the table1 has its field defined as foreign key then that field must be the primary key of another table, say table2. The table in which the foreign key is defined is called as Child Table and the referenced table is called as Parent Table. So, the foreign key in the child table is the primary key of the parent table.

The foreign key is used to prevent the invalid actions that may destroy the links between multiple tables. For example, it prevents the invalid data from being inserted into foreign key column of the table as that column is direct reference of primary key column of another table. This type of design for linking information together is known as “Normalization”. Foreign key can be defined with multiple fields similar to that of primary key, in this case it is called as Composite Foreign key

Foreign key can be created similar to that of Primary key with little difference:-

a) While Table Creation:

b) Using ALTER TABLE:-

Here References is used to refer the table name and its column name from TB2 which is the primary key and used as Foreign key in TB1. The DROP statement used for foreign key is same as that of primary Key.

Hope this will help you. Please write your comments on this.

Thanks,

admin@code2java.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.