1、主键约束
1.1、概念:
是表中一列或多列的组合。主键约束要求主键列的数据唯一且不允许为空。用来唯一的标识表中的一条记录。
1.2、分类
单子段主键
多字段联合主键
例子1create table tb1
(
id int primary key,
name varchar(10),
)engine=myisam character set utf8;
增加主键会自动增加主键索引
例子2create table tb1
(
uid int,
name varchar(10),
primart key(uid)
)engine=myisam character set utf8
例子3create table tb1
(
uid int,
name varchar(10),
certid int(20),
primart key(uid,certid)
)engine=myisam character set utf8;
增加测试数据insert into tb1 values (1,'123','will'),(2,'321','free');
2、外键约束
1)外键搭配主键使用,若不为空值,则每一个外键值必须等于另外一个表中主键的某个值。
2)外键数据类型不许与主键一致
注意:外键可以空值
例子create table tb2
(
fid int,
phone int,
location varchar(20),
constarint fk_tb1 foreign key(fid)
references tb1(uid)
);
增加测试数据insert into tb2 values (1,138000000000,'dg xxx');
insert into tb2 values (3,138000000001,'dg xxx');
insert into tb1 values (3,'456','nasa');
insert into tb2 values (3,138000000002,'dg xxx');
insert into tb2 (phone,localtion) values (138000000003,'sh xxx');
删除外键alter table tb2 drop foreign key fk_tb1;
3、非空约束
用于约束对应列中的值不能有空值create table tb1
(
uid int primary key,
name varchar(10) not null,
phone int
)engine=myisam character set utf8;
测试数据insert into tb1 varlues (1,'will',13800000000)
insert into tb1 (uid,phone) varlues (2,13800000001)
4、唯一性约束
用于约束对应列中的值不能重复,可以有空值,但只能出现一个空值。create table t3
(
id int,
certid int unique
);
测试数据insert into t3 values (1,111),(2,222),(3,333);
select * from t3;
insert into t3 values (4,333);
5、默认约束
1)用于约束对应列中的值得默认值
2)除非默认为空值,否则不可插入空值。create table t1
(
uid int primary key,
name varchar(10) not null,
sex enum(`F` ,`M`,`un`) default `un`
)engine=myisam character set utf8;
6、自增长
用于系统自动生成字段的主键值create table t1
(
uid int primary key auto_increment,
name varchar(10) not null,
sex enum(`F`,`M`,`un`) default 'un'
)engine=myisam character set utf8;
范例:create table t6 (
id int primary key auto_increment,
name varchar(10)
);
验证:insert into t6 values (1,`will`);
insert into t6 values ('free');
insert into t6 values ('duoduo');