Skip to content

PRIMARY KEY Primary Key Constraint

The PRIMARY KEY constraint can be used to ensure that each row of data in a table can be uniquely determined by a certain key value. And at most, only one PRIMARY KEY constraint can be defined on each database table.

Follow the rules

When defining a primary key, the following rules need to be followed:

-Nelegance: The value of the primary key column must be unique, that is, each row in the table must have a different primary key value.

-Non-empty: The values ​​of primary key columns cannot be empty, i.e. they cannot contain NULL values.

-Immutability: The value of the primary key column cannot be changed or updated after insertion. This is to maintain the uniqueness of the primary key. If you do need to change the primary key value, you usually need to delete the original row first and then insert a new row with the new primary key value.

-Minimum: The primary key can be composed of a single column or a combination of multiple columns. Composite primary keys can be used to uniquely identify rows, but their composite values ​​must be unique and there cannot be duplicate combinations.

-Reference Integrity: The primary key is usually used as a reference to a foreign key.

-Automatic index creation: Primary key columns will automatically create indexes to improve retrieval performance.

Syntax Description

> column_name data_type PRIMARY KEY;

Example

mysql> create table t1(a int primary key, b int, c int, primary key(b,c));
ERROR 20301 (HY000): invalid input: more than one primary key defined
mysql> create table t2(a int, b int, c int, primary key(b,c));
Query OK, 0 rows affected (0.01 sec)

mysql> create table t3(a int, b int, c int, primary key(a));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t2 values(1,1,1);
Query OK, 1 row affected (0.02 sec)

mysql> insert into t2 values(1,1,2);
Query OK, 1 row affected (0.01 sec)

mysql> insert into t3 values(1,1,1);
Query OK, 1 row affected (0.01 sec)

mysql> insert into t3 values(2,1,1);
Query OK, 1 row affected (0.01 sec)

Example Explanation: In the above example, t1 contains two sets of primary keys, so the creation failed. t2 and t3 have only one set of primary keys, so it can be created. None of the four insert statements violate the constraints and can be successfully executed.

limit

MatrixOne Intelligence does not support deleting the PRIMARY KEY constraint.