Database development MySQL primary key

4, primary key

There are several attributes in a record in a relational database. If an attribute group (note that it is a group) can uniquely identify a record, the attribute group can become a primary key.

for example

Student table (student number, name, gender, class) in which each student's student number is unique, and the student number is a primary key, which is called a single column primary key    

Course list (course number, course name, credit) in which course number is unique, and course number is a primary key, which is called single column primary key score    

Table (student number, course number, grade) the only attribute in the grade table cannot uniquely identify a record. The combination of student number and course number can uniquely identify a record. Therefore, the attribute group of student number and course number is a primary key, which is called multi column primary key

The student number in the grade table is not the primary key of the grade table, but it corresponds to the student number in the student table, and the student table
The student number in the student table is the primary key of the student table, which means that the student number in the score table is the foreign key of the student table.

Similarly, the course number in the grade table is the foreign key primary key keyword of the course table, which is used to define the column as the primary key.

Single column primary key

Use primary key after a field

 create table t6(
     id int primary key,
     name varchar(20),
     number char(10));

 desc t6;

The execution result is:

mysql>  create table t6(     id int primary key,     name varchar(20),     number char(10));
Query OK, 0 rows affected (0.53 sec)

mysql> desc t6;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | NO   | PRI | NULL    |       |
| name   | varchar(20) | YES  |     | NULL    |       |
| number | char(10)    | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

Define the primary key separately after all front ends

create table t7 (
	id int,
	name varchar (20),
	number char (10),
	constraint pk_id primary key(id)); #Create a primary key and name it

desc t7; 

The execution result is:

mysql> create table t7(
    -> id int,
    -> name varchar(20),
    -> number varchar(20),
    -> constraint pk_id primary key(id));
Query OK, 0 rows affected (0.68 sec)

mysql> desc t7;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | NO   | PRI | NULL    |       |
| name   | varchar(20) | YES  |     | NULL    |       |
| number | varchar(20) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

Multi column primary key

mysql> create table t8(
     -> student_number char(10),
     -> class_number char(10),
     -> achievement int not null default 0,
     -> primary key(student_number, class_number));
 Query OK, 0 rows affected (0.73 sec)

 mysql> desc t8;
 +----------------+----------+------+-----+---------+-------+
 | Field          | Type     | Null | Key | Default | Extra |
 +----------------+----------+------+-----+---------+-------+
 | student_number | char(10) | NO   | PRI | NULL    |       |
 | class_number   | char(10) | NO   | PRI | NULL    |       |
 | achievement    | int(11)  | NO   |     | 0       |       |
 +----------------+----------+------+-----+---------+-------+
 3 rows in set (0.00 sec)

 mysql> insert into t8 values
     -> ("18023300", "186000", 100),
     -> ("19205200", "192000", 90);
 Query OK, 2 rows affected (0.18 sec)
 Records: 2  Duplicates: 0  Warnings: 0

 mysql> insert into t8 values("18023300", "186000", 90);
 ERROR 1062 (23000): Duplicate entry '18023300-186000' for key 'PRIMARY'

 mysql> select * from t8;
 +----------------+--------------+-------------+
 | student_number | class_number | achievement |
 +----------------+--------------+-------------+
 | 18023300       | 186000       |         100 |
 | 19205200       | 192000       |          90 |
 +----------------+--------------+-------------+
 2 rows in set (0.00 sec)

auto_increment

Every time we insert data into a table, we have to remember the id, which is a great test of our brain. Sometimes we may not remember it very clearly, so it is easy to report errors. Using auto increment constraint fields will grow automatically, and the constrained fields must be constrained by the key at the same time.

 create table t9(
     id int primary key auto_increment,
     name varchar(20),
     sex enum("male", "female"));

 desc t9;

 insert into t9(name) values("Alex"),("Coco");
 select * from t9;

 insert into t9 values(5, "BeiBei", "female");
 select * from t9;

 delete from t9;
 select * from t9;

 insert into t9(name, sex) values("Alex", "male"),("Coco", "female");
 select * from t9;

 truncate t9;
 insert into t9(name, sex) values("Alex", "male"),("Coco", "female");
 select * from t9;

The execution result is:

mysql> create table t9(
    -> id int primary key auto_increment,    # Define self growing fields
    -> name varchar(20),
    -> sex enum("male", "female"));
Query OK, 0 rows affected (1.60 sec)

mysql> desc t9;
+-------+-----------------------+------+-----+---------+----------------+
| Field | Type                  | Null | Key | Default | Extra          |
+-------+-----------------------+------+-----+---------+----------------+
| id    | int(11)               | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20)           | YES  |     | NULL    |                |
| sex   | enum('male','female') | YES  |     | NULL    |                |
+-------+-----------------------+------+-----+---------+----------------+
3 rows in set (0.05 sec)

mysql> insert into t9(name) values("Alex"),("Coco");
Query OK, 2 rows affected (0.31 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from t9;    # If no id is specified, it will grow automatically
+----+------+------+
| id | name | sex  |
+----+------+------+
|  1 | Alex | NULL |
|  2 | Coco | NULL |
+----+------+------+
2 rows in set (0.00 sec)

mysql> insert into t9 values(5, "BeiBei", "female");    # Can also refer to the specified id
Query OK, 1 row affected (0.23 sec)

mysql> select * from t9;
+----+--------+--------+
| id | name   | sex    |
+----+--------+--------+
|  1 | Alex   | NULL   |
|  2 | Coco   | NULL   |
|  5 | BeiBei | female |
+----+--------+--------+
3 rows in set (0.00 sec)

mysql>  delete from t9;
Query OK, 3 rows affected (0.52 sec)

mysql> select * from t9;
Empty set (0.00 sec)

mysql> insert into t9(name, sex) values("Alex", "male"),("Coco", "female");
Query OK, 2 rows affected (0.18 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from t9;    # For the auto increasing field, after deleting with delete, insert the value, and the field will continue to grow according to the position before deletion
+----+------+--------+
| id | name | sex    |
+----+------+--------+
|  6 | Alex | male   |
|  7 | Coco | female |
+----+------+--------+
2 rows in set (0.00 sec)

mysql> truncate t9;        # Truncate should be used to clear the table. Compared to delete records one by one, truncate is used to clear the table directly and delete large tables
Query OK, 0 rows affected (0.87 sec)

mysql> insert into t9(name, sex) values("Alex", "male"),("Coco", "female");
Query OK, 2 rows affected (0.09 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from t9;
+----+------+--------+
| id | name | sex    |
+----+------+--------+
|  1 | Alex | male   |
|  2 | Coco | female |
+----+------+--------+
2 rows in set (0.00 sec)

After creating the table, you can modify the initial value of the auto increment subdivision

create table t10(
	id int primary key auto_increment,
	name varchar(20),
	sex enum('male','female') default 'male');

alter table t10 auto_increment = 5;		# Modify step size

show create table t10;

insert into t10(name) values("Alex");

select * from t10;

show create table t10;

The execution result is:

mysql> create table t10(
    -> id int primary key auto_increment,
    -> name varchar(20),
    -> sex enum('male','female') default 'male');
Query OK, 0 rows affected (0.72 sec)

mysql> alter table t10 auto_increment = 5;
Query OK, 0 rows affected (0.33 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table t10;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                             |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t10   | CREATE TABLE `t10` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `sex` enum('male','female') DEFAULT 'male',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> insert into t10(name) values("Alex");
Query OK, 1 row affected (0.24 sec)

mysql> select * from t10;
+----+------+------+
| id | name | sex  |
+----+------+------+
|  5 | Alex | male |
+----+------+------+
1 row in set (0.00 sec)

mysql> show create table t10;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                             |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t10   | CREATE TABLE `t10` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `sex` enum('male','female') DEFAULT 'male',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

To view MySQL steps:

show session variables like "auto_inc%";

The execution result is:

mysql> show session variables like "auto_inc%";
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 1     |	# Auto increment indicates step size
| auto_increment_offset    | 1     |	# Auto increment offset indicates the actual offset
+--------------------------+-------+
2 rows in set, 1 warning (0.15 sec)

You can set the step size with the following command:

set session auth_increment_increment=2 #Change session level step size

set global auth_increment_increment=2 #Change global level step size (all sessions take effect)

Setting the global start offset and step size requires restarting MySQL.

If the value of auto increment offset is greater than the value of auto increment, the value of auto increment offset will be ignored.

View column details Unlock all columns now
664 original articles published, praised 152, visited 230000+
His message board follow

Tags: MySQL Attribute Session Database

Posted on Wed, 12 Feb 2020 08:32:27 -0500 by emopoops