Course
Primary Key
MySQL Tutorial
This SQL tutorial is structured for beginners to guide them from the foundational concepts to advanced data manipulation and querying techniques in SQL. By the end of this tutorial, you will have developed a robust understanding of SQL, equipping you with the knowledge to efficiently manage and analyze data across various database systems. This guide sets the stage for your growth into a skilled data professional, ready to tackle complex data challenges and contribute to the field of data analysis and database management.
Primary Key
A PRIMARY KEY is a constraint applied on a field of a MySQL table. When this is applied, the values in that particular table column are uniquely identified. It is the most appropriate candidate key to be the main key of any table.
A table can have only one PRIMARY KEY, which may consist of single or multiple fields. When multiple fields are used as a primary key, they are called a Composite Key.
You can either create a primary key while creating a new table or you can apply it on an already existing table in the database. But if it is being applied on an existing table, you must make sure that the table does not already contain a primary key and .
Creating MySQL Primary Key
To create a primary key on a new MySQL table, you must specify the column as the PRIMARY KEY while creating a new table using the CREATE TABLE statement.
Following are some points to remember while creating a Primary Key on a table −
- The Primary Key column must only contain unique values.
- It can not hold NULL values.
- One table can have only one Primary Key.
- A Primary Key length cannot be more than 900 bytes.
Syntax
Following is the syntax to define a column of a table as a primary key −
CREATE TABLE table_name( column_name NOT NULL PRIMARY KEY(column_name));
Example
In the following example, let us create a table with the name CUSTOMERS in a MySQL database using the CREATE TABLE query. In this query, we will add the PRIMARY KEY constraint on a column named ID.
CREATE TABLE CUSTOMERS ( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25) UNIQUE, SALARY DECIMAL (18, 2), PRIMARY KEY(ID));
Output
The table structure displayed will contain a UNI index on the ADDRESS column as shown −
Verification
To verify further that the PRIMARY KEY constraint is applied on the ID column, let us insert different types of values into the CUSTOMERS table using the following queries −
INSERT INTO CUSTOMERS VALUES (1, 'Ramesh', 23, 'Pune', 2000.00),(1, 'John', 25, 'Hyderabad', 3000.00);
Following error is displayed −
ERROR 1062 (23000): Duplicate entry '1' for key 'customers.PRIMARY'
As we can see above, you cannot insert duplicate and null values into this primary key column.
Creating Primary Key on Existing Column
We can also add a primary key on an existing column of a table, if it was not created (for any reason) while creating a new table. However, adding a primary key on an existing table is only possible if the table does not already contain a primary key (as a MySQL table must not contain multiple primary keys), and the column it is being applied on must only contain unique values.
You can add the primary key on an existing table using the ALTER TABLE... ADD CONSTRAINT statement.
Syntax
Following is the syntax to create a unique constraint on existing columns of a table −
ALTER TABLE table_name ADD CONSTRAINT PRIMARY KEY (column_name);
Example
Using the ALTER TABLE statement, you can add a PRIMARY KEY on an existing column in the CUSTOMERS table created previously. In the following example, we are applying the PRIMARY KEY on the ID column as shown below −
ALTER TABLE CUSTOMERS ADD CONSTRAINT PRIMARY KEY (ADDRESS);
Output
The table structure displayed will contain a UNI index on the ADDRESS column as shown −
But if the column, on which the PRIMARY KEY is added, contains duplicate or null values, it cannot be set as a primary key.
Dropping MySQL Primary Key
MySQL provides the ALTER TABLE... DROP statement to drop the primary key from a table.
Syntax
Following is the syntax to drop the PRIMARY KEY constraint using the ALTER TABLE... DROP statement −
ALTER TABLE table_name DROP PRIMARY KEY;
Example
Let us consider the CUSTOMERS table with the primary key constraint present on a column named ID. You can drop this constraint from the column ID by executing the following statement
ALTER TABLE CUSTOMERS DROP PRIMARY KEY;
Output
The table structure displayed will contain a UNI index on the ADDRESS column as shown −
Creating Primary Key Using Client Program
We can also apply a Primary Key on a table field using a client program.
Syntax
PHPNodeJSJavaPython
To apply primary key on a field through a PHP program, we need to execute the CREATE query with PRIMARY KEY keyword using the mysqli function query() as follows −
$sql = 'CREATE TABLE customers(cust_ID INT NOT NULL UNIQUE, cust_Name VARCHAR(30), cust_login_ID INT AUTO_INCREMENT PRIMARY KEY)';$mysqli->query($sql);
Example
Following are the programs −
PHPNodeJSJavaPython
$dbhost = 'localhost';$dbuser = 'root';$dbpass = 'password';$dbname = 'TUTORIALS';$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);if ($mysqli->connect_errno) { printf("Connect failed: %s", $mysqli->connect_error); exit();}// printf('Connected successfully.');
$sql = 'CREATE TABLE customers(cust_ID INT NOT NULL UNIQUE, cust_Name VARCHAR(30), cust_login_ID INT AUTO_INCREMENT PRIMARY KEY)';if ($mysqli->query($sql)) { echo "Primary key column created successfully in customers table \n";}if ($mysqli->errno) { printf("Table could not be created!.", $mysqli->error);}$mysqli->close();
Output
The output obtained is as follows −
Primary key column created successfully in customers table