12. Talking about the MySQL primary key

The following content is excerpted from [1]

A primary key does not have a clear concept definition. It is an index and a unique index. It must be defined as a "primary key" and cannot be repeated, A table can have only one primary key.

1. Primary Key declaration method:

You can add a primary key to the table when creating the table, for example:

Create Table tbl_name ([field description omitted...], primary key (index_col_name ));

You can also add a primary key to the table when updating the table structure, for example:

Alter table tbl_name add primary key (index_col_name ,...);

/*

Create a QQ table, set qq_id as the primary key, and do not impose a not null constraint on it

*/

Create Table QQ (

Qq_id int (10 ),

Nick_name varchar (255) not null,

Primary Key (qq_id ))

/*

Insert a piece of data, set the QQ number to 10000 (I also think about it), and set the nickname to "simaopig"

*/

Insert into QQ (qq_id, nick_name)

Values (

'123', 'simaopig ');

2. Example

Primary keys are considered to be the best combination of not null and unique constraints. If these columns are not explicitly defined as not null, MySQL will implicitly define these columns.

3. The primary key is also an index:

As I have already said, the primary key is actually an index. Even in MySQL terminology, the "key" is equivalent to "Index". Therefore, the "foreign key" must be set to "Index" first ". Therefore, the primary key should be the same as the index, which can act on individual fields and multiple fields.

For example, if I live in Room 3, Room 501, and my name is xiaozi, only Room 3, Room 501 can be uniquely identified in the Community table. Because Unit 2 and Room 501 may also have a shard, only two fields can uniquely identify me,That is to say, the two can be combined as the primary key. The primary key of the combination. Each column implicitly defines the not null constraint, and the unique constraint of unique is defined together.

/*

Create a firewall table and set the host and Port Combination as the primary key. Note that I have not set the port not null constraint.

*/

Create Table firewall (

Host varchar (11) not null,

Port smallint (4 ),

Access Enum ('deny', 'allow') not null,

Primary Key (host, Port ))

/*

Insert a new record.

1 row (s) inserted.

*/

Insert into firewall (

Host,

Port,

Access)

Values (

'192. 65.3.87 ', '21', 'deny ');

Original article:

[1] talking about MySQL primary key http://zhumeng8337797.blog.163.com/blog/static/100768914201110245101552/