/    /  MySQL – Primary Key

MySQL – Primary Key

 

p1

In a table, a primary key consists of a column or cluster of columns that uniquely identifies each row. Primary keys are based on the following rules:

  • The primary key must contain unique values. When a primary key consists of multiple columns, each combination of values in these columns must be unique.
  • The primary key column cannot contain NULL values. Attempts to insert or update NULL values into primary key columns will result in an error. It is important to note that MySQL implicitly adds a NOT NULL constraint to primary key columns.
  • The primary key of a table can be one and only one.

It is recommended that the data type of the primary key column be an integer, e.g., INT, BIGINT. Additionally, you should ensure that the range of values of the integer type belong to the table’s value range.

If you insert a new row into the table, the AUTO_INCREMENT attribute automatically generates a sequential integer.

MySQL creates an index called PRIMARY when you define the primary key for a table.

Syntax

CREATE TABLE table_name(  
    col1 datatype PRIMARY KEY,  
    col2 datatype,  
    ...  
); 

The following syntax can be used to create more than one primary key column in the table:

CREATE TABLE table_name  
(  
  col1 col_definition,  
  col2 col_definition,  
  ...  
  CONSTRAINT [constraint_name]   
   PRIMARY KEY (column_name(s))  
);

Table_name – This is the name of the table we are going to create.

Col1, col2 – This is the name of the columns in the table.

Constraint_name – This is the primary key’s name.

Column_name(s) – This is the name of the column(s) that will serve as the primary key.

Example:

For example, in the “employees” table, the “employee_id” column could be designated as the primary key. This would ensure that each employee has a unique ID and can be easily identified and referenced in other tables.

CREATE TABLE employees (
  employee_id INT PRIMARY KEY AUTO_INCREMENT,
  first_name VARCHAR(50) NOT NULL,
  last_name VARCHAR(50) NOT NULL,
  hire_date DATE NOT NULL
);

It will create a primary key in the “employee_id” column of the “employees” table.

p2