注意:MySql中只有键才能定义为 auto_increment 型
create table company
(
company_id smallint not null,
company_name varchar(10) not null,
primary key(company_id)
);
(
company_id smallint not null,
company_name varchar(10) not null,
primary key(company_id)
);
方式一:在创建表的时候同时创建约束
create table person
(
id smallint not null auto_increment,
name varchar(10) not null,
company_id smallint,
//主键约束
primary key(id),
primary key(id),
//唯一约束,也可以指定约束的名称: unique key constraint_name (name),
unique key(name),
unique key(name),
//外键约束,也可以指定约束的名称:constraint constraint_name foreign key(company_id) references company(company_id)
foreign key(company_id) references company(company_id)
)
foreign key(company_id) references company(company_id)
)
方式二:创建表以后,再创建约束
create table person
(
id smallint not null,
name varchar(10) not null,
company_id smallint
)
//创建主键约束
(1)alter table person add primary key(id);
(2)alter table person add constraint primary_k primary key(id);
再将主键列设为auto_increment:
alter table person modify id smallint auto_increment;
alter table person modify id smallint auto_increment;
//删除主键约束
(1)alter table person drop primary key;
(1)alter table person drop primary key;
删除主键约束的时候,如果主键已经是auto_increment型,则无法删除,因为只有键才能是auto_increment型的
约束名称可以用 show create table person 命令查看
//创建外键约束,可在后面加上on delete restrict on update restrict
(1)alter table person add foreign key(company_id) references company(company_id);
(2)alter table person add constraint foreing_k foreign key(company_id) references company(company_id);
(1)alter table person add foreign key(company_id) references company(company_id);
(2)alter table person add constraint foreing_k foreign key(company_id) references company(company_id);
创建外键约束以后,系统自动为外键列创建了一个key,名称可以用show create table table_name查看
用下面的方式删除外键约束后,这个key仍然存在
//删除外键约束
(1)alter table person drop foreign key foreign_k;
//创建唯一约束
(1)alter table person add unique key(name);
(2)alter table person add constraint unique_k unique key(name);
(1)alter table person add unique key(name);
(2)alter table person add constraint unique_k unique key(name);
//删除唯一约束
因为创建unique约束后,系统会自动给此列创建索引,用show create table table_name查看索引名称
alter table person drop index name;