MySQL discrete notes-primary key

Reference: MySQL primary key Author: simaopig

A primary key is not clearly defined. It is an index and a unique index. It must be defined as a "primary key ".

Declaration method:

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

Or

Create Table tbl_name (ID int (10) primary key | ...);

Or update the table structure:

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

Primary Key is considered to beNot null and unique constraints are best combined. If these columns are not explicitly defined as not null, MySQL will implicitly define these columns.

At the same time, the primary key is also an index. Indexes can be used for multiple fields, and the same is true for primary keys,It can act on individual fields and multiple fields.The primary key of the combination. Each column implicitly defines the not null constraint and the unique constraint of unique is defined together..

ExampleCode: A firewall, which is determined by the combination of host and Port

/*
 
 
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 (
 
 
'202.65.3.87','21','Deny'
 
);
 
 
/*
 
 
Insertion failed because the primary key value of host and port 202.65.3.87-21 already exists.
 
 
#1062-duplicate entry '192. 65.3.87-21 'for key 'Primary'
 
 
*/
 
Insert into firewall (
 
 
Host,
 
 
Port,
 
 
Access
 
 
)
 
 
Values (
 
'202.65.3.87','21','Allow'
 
 
);
 
 
/*
 
 
The not NULL Port cannot be null.
 
#1048-column 'Port' cannot be null
 
 
*/
 
 
Insert into firewall (host, port, access)
 
 
Values (
 
 
'192.168.0.1', Null ,'Deny'
 
)
 
 
Both host and port can be repeated, but cannot be repeated at the same time, because it is a combination of primary keys. Neither of them can be inserted with null because it is the primary key.