主键
insert into user values(1, 'ZhangSanFeng');
Query OK, 1 row affected (0.00 sec)
insert into user values(1, 'ZhangSanFeng');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
联合主键
create table user2(id int, name varchar(20), passwd varchar(20), primary key(id, name));
insert into user2 values(1, 'zhangsan', '123’);
insert into user2 values(2, 'zhangsan', '123’);
自增约束
create table user3(id int primary key auto_increment, name varchar(20));
insert into user3 (name) values('ZhangSan’);
忘记创建主键约束
alter table user4 add primary key(id);
alter table user4 drop primary key;
唯一约束
alter table user5 add unique(name);
create table user6(id int, name varchar(20),unique(name));
create table user7(id int, name varchar(20) unique);
create table user8(id int, name varchar(20), unique(id, name));
//两个在一起不重复就OK删除唯一约束
alter table user8 drop index id;添加alter table user7 modify name varchar(20) unique;
对所有约束都适用
1建表时添加约束
2 alter add
3 alter modify
4删除 alter drop
非空约束
create table user9(id int, name varchar(20) not null);
默认约束
没有传值使用默认值
create table user10(id int, name var char(20), age int default 10);
外键约束涉及到两个表,父表,子表主表,父表
create table classes(id int primary key, name varchar(20));
create table students(id int primary key, name varchar(20), class_id int, foreign key(class_id) references classes(id));
insert into classes values(1, '一班');
insert into classes values(2, '二班');
insert into classes values(3, '三班');
insert into classes values(4, '四班’);
insert into students values(1001, 'Zhangsan', 1);
insert into students values(1002, 'Zhangsan', 2);
insert into students values(1003, 'Zhangsan', 3);
insert into students values(1004, 'Zhangsan', 4);
1 主表classes中没有的数据值在附表中是不可以使用的
2 主表中的记录被父表引用是不可以被删除的
delete from classes where id = 4;
Cannot delete or update a parent row: a foreign key constraint fails
网友评论