Primary Key Constraint

What is a Primary Key Constraint in MySQL?

A Primary Key Constraint in MySQL is used to define a column or a combination of columns in a table whose values uniquely identify each record in that table.

Syntax:

CREATE TABLE table_name (
    column_name data_type PRIMARY KEY
);

Example:

Suppose the Student table contains three columns: Roll_ID, Name, and Age, with three rows. The Roll_ID field is the Primary Key because it uniquely identifies each student or record, as every Roll_ID is unique. Only one field can become the Primary Key constraint, such as Roll_ID. A Primary Key constraint cannot contain NULL values. It is also important to note that each table can have only one Primary Key.

Output:

+---------+--------+-----+
| Roll_ID | Name   | Age |
+---------+--------+-----+
| 101     | Rahul  | 20  |
| 102     | Amit   | 21  |
| 103     | Neha   | 19  |
+---------+--------+-----+

Explanation:

The column Roll_ID is a Primary Key, and the value stored in this field is always unique.

Because a Primary Key constraint is set on this column, it does not allow NULL values.

How Do You Set a Primary Key Constraint on a Table in MySQL?

You can set a Primary Key constraint on a table by using the following two methods:

1. While creating a new table using the CREATE TABLE statement.

   
   

   Example:

   CREATE TABLE Student (
       Roll_ID INT PRIMARY KEY,
       Name VARCHAR(50),
       Age INT
   );
   

   
   

   Steps:

   1. First, you write the CREATE TABLE statement, and then you write the table name.
   2. Open the parentheses (.
   3. Inside the parentheses, write the column names and their data types.
   4. For the column that you want to make the Primary Key, write the PRIMARY KEY keyword after the data type of that column.
   5. Write the remaining column names with their data types, separating each field with a comma.
   6. Close the parentheses ) and end the query with a semicolon ;.
   7. Finally, execute the query to create the table with the Primary Key.

2. By adding a Primary Key to an existing table using the ALTER TABLE statement.

   
   

   Example:

   ALTER TABLE Student
   ADD PRIMARY KEY (Roll_ID);
   

   
   

   Steps:

   1. First, write the ALTER TABLE statement.
   2. Next, write the name of the existing table.
   3. Write the ADD PRIMARY KEY clause.
   4. Inside the parentheses (), write the column name that you want to make the Primary Key.
   5. Close the parentheses ) and end the query with a semicolon ;.
   6. Finally, execute the query to add the Primary Key to the existing table.

Advantages of a Primary Key

• Provides Unique Identification of Records

  A Primary Key constraint gives every record its own unique identity. This helps MySQL easily find and identify a specific row in a table.

• Prevents Duplicate Values

  A Primary Key constraint does not allow the same value to be entered again in its column. This keeps every record unique.

• Does Not Allow NULL Values

  A Primary Key constraint does not allow NULL values. Every record in the table must have a valid value in the Primary Key column.

• Improves Data Searching Performance

  When you create a Primary Key constraint, MySQL automatically creates an index for that column. It helps MySQL quickly find and retrieve records from the table.

Common Mistakes When Setting a Primary Key constraint in MySQL

1. To Insert Duplicate Values

   You should not make a mistake by entering a duplicate value when you set a column as a Primary Key.

2. To Insert a NULL Value

   You should not make this mistake if you set a column as a Primary Key because that column does not permit NULL values.

3. Do Not Set Multiple Primary Keys

   In a table, do not set multiple Primary Keys because a table can have only one Primary Key.

4. To Make the Primary Key of the Wrong Column

   You should not make the wrong column a Primary Key because the value of that column should not repeat. It should always contain unique values, and the value of that column should be different for each row.

5. Do Not Change the Primary Key Value Continuously

   You should not change the Primary Key column value because it identifies the record in the table.

6. Choose the Incorrect Data Type

   You should choose the correct data type for the Primary Key column because, if the number of records increases, that column should be able to store all records.

7. To Use AUTO_INCREMENT Incorrectly

   Do not use the Primary Key column with AUTO_INCREMENT incorrectly; otherwise, when you insert a duplicate value, MySQL displays an error message.

Is Composite Key and Composite Primary Key the Same?

Yes. In MySQL, the terms Composite Key and Composite Primary Key are commonly used to refer to a Primary Key that is created using two or more columns. Both terms describe the same concept.

What is a Composite Primary Key Constraint in SQL and Why is it used?

A Composite Primary Key Constraint is set on two or more columns in a table. It makes the combined values of these columns unique for the entire table and uniquely identifies each record.

It is used when a single column in a table cannot uniquely identify every record. In such cases, two or more columns whose combined values uniquely identify each record are used together to create a Composite Primary Key.

Syntax:

CREATE TABLE table_name
(
    column1 datatype,
    column2 datatype,
    column3 datatype,

    PRIMARY KEY (column1, column2)
);

Here,

➤ table_name → Specifies the name of the table.

➤ PRIMARY KEY (column1, column2) → column1 and column2 are the columns that together form the Composite Primary Key.

Example:

The following example creates a Student_Course table. In this table, Student_ID and Course_ID are combined to create a Composite Primary Key. This ensures that the combined values of these two columns are unique for every record in the table. This means every record is uniquely identified by the combined values of Student_ID and Course_ID.

CREATE TABLE Student_Course
(
    Student_ID INT,
    Course_ID VARCHAR(10),
    Student_Name VARCHAR(50),

    PRIMARY KEY (Student_ID, Course_ID)
);

Output:

After executing the above query, the Student_Course table is created successfully.

The following table shows how records can be stored in the Student_Course table.

+------------+-----------+--------------+
| Student_ID | Course_ID | Student_Name |
+------------+-----------+--------------+
| 101        | C101      | Rahul        |
| 101        | C102      | Rahul        |
| 102        | C101      | Amit         |
+------------+-----------+--------------+

Explanation:

In the above table, Student_ID and Course_ID together create the Composite Primary Key.
Both columns combined uniquely identify each record.
Therefore, the same combination of Student_ID and Course_ID cannot be added again in the table.