The primary key in Mysql and the tutorial on setting its own usage _mysql

1, the method of declaring the primary key:
You can add a primary key to a table when you create it, such as:
CREATE TABLE tbl_name ([Field description omitted ...], PRIMARY KEY (index_col_name));
You can also add a primary key to a table when you update the table structure, such as:

ALTER TABLE tbl_name ADD PRIMARY KEY (index_col_name,...);
/*
Create a QQ table, the qq_id is set as the primary key, and does not have a NOT NULL constraint on it
/CREATE TABLE QQ (
qq_id int),
nick_name varchar ( 255) Not NULL,
primary KEY (QQ_ID)/
*
Insert a data, the QQ number set to 10000 (we also fantasize about), nickname set to "Simaopig"
* * *
Insert Into QQ (qq_id, Nick_name)
VALUES (
' 10000 ', ' Simaopig ');

A primary key is considered to be the best combination of not NULL and a unique constraint. These columns are implicitly defined if these columns are not explicitly defined as not null,mysql.

2, the primary key is also the index:
just already said, the primary key is actually index, even in the term of MySQL "key" is equal to "index", so "foreign key" must first set to "index." Therefore, the primary key should also be the same as the index, which can be used for both individual fields and multiple fields.
For example, I live in 3 units, 501 room, my name is Keoko, then only 3 unit 501 room in this community table only to determine my home. Because 2 units, 501 of the room may also be a Keoko, so there are only two fields to determine me, that is, can be the two groups of cooperative primary key. Combined primary keys, each column implicitly defines a NOT NULL constraint, and the two are added together to define a unique unique constraint.

/* Create firewall table, set host and port combination as primary key, notice I did not set port NOT NULL
constraint
/CREATE TABLE firewall (
host varchar (one) NOT NULL ,
Port smallint (4),
access enum (' Deny ', ' allow ') not NULL,
primary KEY (Host,port))/
*
inserts a new record, No problem
. 1 row (s) inserted.
* *
INSERT into firewall (
host,
port,
access)
VALUES (
' 202.65.3.87 ', ', ', ' deny ');

3, set the primary key self-increase
Here's an example of how to set up a primary key self-increase:
First create a database, create a table

mysql> CREATE DATABASE Ssh2; 

Query OK, 1 row affected (0.04 sec) 

mysql> use SSH2; 

Database changed 

Mysql> CREATE TABLE User ( 
  -> ID integer primary key, 
  -> FirstName varchar (MB) NOT NULL, 
  -> Lastna Me varchar NOT NULL,-> the age 
  integer 
  ->); 

Query OK, 0 rows affected (0.46 sec) 

Add an additional function to the primary key:

Mysql> ALTER TABLE user modify ID integer auto_increment; 

Query OK, 1 row affected (0.28 sec) 
records:1 duplicates:0 warnings:0 

In this way, the ID of the primary key in the user table above can be increased by itself.

Add default values and enhancements to the primary key ID above.

Mysql> ALTER TABLE user modify ID integer auto_increment; 

Query OK, 0 rows affected (0.39 sec) 
records:0 duplicates:0 warnings:0 

Mysql> ALTER TABLE user modify ID integer default ' 1 '; 

Query OK, 0 rows affected (0.16 sec) 
records:0 duplicates:0 warnings:0 

Mysql> ALTER TABLE user modify ID integer auto_increment; 

Query OK, 1 row affected (0.28 sec) 
records:1 duplicates:0 warnings:0 

MySQL Get system time:

Mysql> ALTER TABLE user add createtime timestamp default current_timestamp; 

Query OK, 2 rows affected (0.17 sec) 
Records:2 duplicates:0 warnings:0 

MySQL set primary key can not be empty, but also to automatically grow (there is no default setting, but the default is 1, starting from 1 growth.) , and get the system default date:

Mysql> CREATE TABLE DD ( 
  -> ID int primary key NOT NULL auto_increment, 
  -> name varchar (), 
  -> Tim E timestamp default current_timestamp 
  ->); 

Query OK, 0 rows affected (0.10 sec) 

 
Mysql> INSERT INTO DD (name) VALUES (' Fhihgifds ');

Query OK, 1 row affected (0.14 sec) 

Mysql> INSERT INTO DD (name) VALUES (' Steven '); 

Query OK, 1 row affected (0.08 sec) 

Mysql> select * FROM DD; 

+----+-----------+---------------------+ 
| id | name   | 
+----+-----------+---------------------+ 
| 1 | fhihgifds | 2011-03-27 01:58:46 | 
| 2 | Steven  | 2011-03-27 01:59:35 | 
+----+-----------+---------------------+ 
2 rows in Set (0.08 sec) 

Mysql> INSERT INTO DD (name) VALUES (' Anthony '); 

Query OK, 1 row affected (0.09 sec) 

Mysql> select * FROM DD; 

 +----+-----------+---------------------+ | id | name | time | +----+-----------+---------------------+ 
| 1 | Fhihgifds | 
2011-03-27 01:58:46 | | 2 | Steven | 
2011-03-27 01:59:35 | | 3 | Anthony | 
2011-03-27 02:00:07 | +----+-----------+---------------------+ 3 rows in Set (0.00 sec)