mysql多字段主键和单字段主键_单字段主键

1.单字段主键

create table 表名 (属性名 数据类型 primary key,...);for example :create table t_dept(

deptno int primary key,

dname varchar(20),

loc varchar(40));

2.如果相关字段PK设置一个名字，可以执行sql语句constraint,如： create table t_dept(

deptno int,

dname varchar(20),

loc varchar(40),

constraint pk_dname primary key(dname));

3.多字段主键

create table 表名 (

属性名 数据类型，...

[constraint 约束名] primary key (属性名，属性名...));for example: create table t_dept(

deptno int,

dname varchar(20),

loc varchar(20),

constraint pk_dname_deptno primary key(deptno,dname；

------------------------------------------------------------------------

4. 例子；创建表时建立主键索引mysql> create table student (

-> id int(4) not null auto_increment,

-> name char(20) not null,

-> age tinyint(2) not null default '0',

-> dept varchar(16) default null,

-> primary key(id),

-> KEY index_name(name)

-> );

mysql> desc student;

+-------+-------------+------+-----+---------+----------------+

| Field | Type        | Null | Key | Default | Extra          |

+-------+-------------+------+-----+---------+----------------+

| id    | int(4)      | NO   | PRI | NULL    | auto_increment |

| name  | char(20)    | NO   | MUL | NULL    |                |

| age   | tinyint(2)  | NO   |     | 0       |                |

| dept  | varchar(16) | YES  |     | NULL    |                |

+-------+-------------+------+-----+---------+----------------+

-------------------------------------------------------------------

5.建表后通过alter命令增加主键索引(一般不这样做) mysql> desc student;

+-------+-------------+------+-----+---------+-------+

| Field | Type        | Null | Key | Default | Extra |

+-------+-------------+------+-----+---------+-------+

| id    | int(4)      | NO   |     | NULL    |       |

| name  | char(20)    | NO   |     | NULL    |       |

| age   | tinyint(2)  | NO   |     | 0       |       |

| dept  | varchar(16) | YES  |     | NULL    |       |

+-------+-------------+------+-----+---------+-------+

4 rows in set (0.00 sec)

mysql> alter table student change id id int primary key auto_increment;

Query OK, 0 rows affected (0.01 sec)

Records: 0  Duplicates: 0  Warnings: 0

mysql> desc student;

+-------+-------------+------+-----+---------+----------------+

| Field | Type        | Null | Key | Default | Extra          |

+-------+-------------+------+-----+---------+----------------+

| id    | int(11)     | NO   | PRI | NULL    | auto_increment |

| name  | char(20)    | NO   |     | NULL    |                |

| age   | tinyint(2)  | NO   |     | 0       |                |

| dept  | varchar(16) | YES  |     | NULL    |                |

+-------+-------------+------+-----+---------+----------------+

4 rows in set (0.00 sec)