Exercise 1: Can I create a table without declaring a primary key?
(1) create table user(
name varchar(10)
)engine=innodb;
(2) insert into user values_(‘shenjian’)__; _
(3) insert into user values_(‘shenjian’)__; _
Voiceover: When creating a table, insert two identical elements without declaring a primary key.
Question, execute the above statement consecutively, execute the result:
(1)
B Insert statement (2) error
C Insert statement (3) error
D no error is reported
【 答案 】 D
InnoDB must have a clustered index, and row records are physically sorted by the clustered index. You must have a clustered index, not a primary key.
(1) If a primary key is defined, the primary key is the clustered index;
(2) If no primary key is defined, the first not null and unique column is the clustered index;
(3) If there is no column that meets the criteria, a hidden row-ID is automatically created as the clustered index;
Voice-over: This example falls into the third category.
Exercise 2: When creating a table, can not declare primary key is not empty?
(1) create table user(
id int,
name varchar(10),
primary key(id)
)engine=innodb;
(2) insert into user(name) values(‘shenjian’);
(3) insert into user(name) values(‘shenjian’);
Voiceover: When creating a table, insert two identical elements without declaring non-null.
Question, execute the above statement consecutively, execute the result:
(1)
B Insert statement (2) error
C Insert statement (3) error
_D does not return error _
[C] insert statement (3) error
Table construction clauses do not actively set the primary key to be non-null (id in the example above). InnoDB automatically adds a non-null and unique constraint.
The primary key column must be non-empty and the default value is 0.
insert into user(name) values(‘shenjian’);
The id is 0 by default. The first insertion succeeds, but the second insertion conflicts with the primary key. A lot of people got this question wrong.
Exercise 3: when creating a table, can select more than one field primary key?
(1) create table user(
id int not null,
name varchar(10) not null,
primary key(id, name)
)engine=innodb;
(2) insert into user values(1, ‘shenjian‘);
(3) insert into user values(1, ‘zhangsan’);
(4) insert into user values(2, ‘shenjian‘);
Voiceover: When creating a table, declare the joint primary key (a,b), insert several elements, some a duplicate, some B duplicate.
Question, execute the above statement consecutively, execute the result:
(1)
B Insert statement (2) error
C Insert statement (3) error
D Insert statement (3) error
E does not report errors
[E] [c] [D]
This is known as a federated primary key, and requires not that each column be unique, but that the columns of the federated primary key be “combinatorial unique.”
Voice-over: Federated indexes, federated primary keys, something like that.
Exercise 4: Can I actively insert auto-increment primary keys?
(1) create table user(
id int auto_increment,
name varchar(10) not null,
primary key(id)
)engine=innodb;
(2) insert into user(name) values(‘shenjian’);
(3) insert into user_(id, name)_ values_(10,’shenjian’)__; _
(4) insert into user(name) values(‘shenjian’);
Voiceover: When creating a table, increment the ID primary key, insert several elements, some contain the increment ID, some do not.
Question, execute the above statement consecutively, execute the result:
(1)
B Insert statement (2) error
C Insert statement (3) error
D Insert statement (3) error
E does not report errors
[E] [c] [D]
The increment ID is often used as the primary key. InnoDB automatically incrementing rows without specifying a value when inserting them. Alternatively, you can specify values manually:
(1) If the manually specified value is the same as the existing value, the primary key conflicts;
(2) If there is no conflict with the existing value, the insert is successful;
(3) If no value is specified in the future, the value will continue to increase from the manual value;
In the example above, after 10 is manually inserted, rows to be inserted in the future will start from 11, and there will be a hole in the middle of the ID increment.
Exercise 5: Can you add a primary key to a table?
(1) create table user(
id int auto_increment,
name varchar(10) not null,
primary key(name, id)
)engine=innodb;
(2) insert into user(name) values(‘shenjian’);
(3) insert into user_(id, name)_ values_(10,’shenjian’); _
(4) insert into user(name) values(‘shenjian’);
Voiceover: when creating a table, declare the joint primary key (a,b), and one of them is the increment ID, insert several elements, including the increment ID, some do not contain.
Question, execute the above statement consecutively, execute the result:
(1)
B Insert statement (2) error
C Insert statement (3) error
D Insert statement (3) error
_E does not return an error _
(1) 【 答案 】 b
This does not mean that the increment ID cannot be used as a joint primary key, but if it is, it must be in the first column.
primary key(name, id_)_
Instead of
primary key(id, name)
The execution is successful. A lot of people got this question wrong.
conclusion
Can InnoDB create tables without primary keys?
You may not declare a primary key, but you must have a clustered index:
(1) there is a primary key, the primary key is clustered index;
(2) There is no primary key, and the first non-empty unique column is the clustered index;
(3) row-id is a clustered index; (4) row-id is a clustered index.
Primary keys and clustered indexes are not the same thing, so don’t confuse them.
2: InnoDB table construction, can not declare primary key is not empty?
You may not declare a primary key to be non-null, but a non-null constraint will be applied automatically.
3: Can InnoDB select multiple fields as primary keys when creating tables?
You can use the union primary key, and the combined column is unique.
4. Can InnoDB insert auto-increment primary key?
You can specify a value for an increment column, but it may result in a void.
5: Can InnoDB use joint autoincrement primary key when creating tables?
Yes, but the increment ID must be in the first column of the union primary key.
I want you to have a more systematic view of primary keys.
The architect’s Path– Share technical ideas
Related articles:
Buffer pool, once and for all! Change Buffer, Get it!
DCEEA, did you get that right?