[ ] 表示内容是可选填，不填则默认是数据库自动分配名字
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 |
1在创建表时后面直接指定约束 2在创建表时最后面直接指定约束 3通过alter增加或删除约束modify 主键和唯一可以用drop ky_id; 唯一约束用key/index 默认约束用set 1.主键约束 单字段主键 create table student ( name varchar(10) primary key; age tinyint; ); alter table table1 modify name varchar(10);//取消主键约束; alter table table1 modify name varchar(10) [constraint key1] primary key;//添加主键约束; alter table table1 add primary key (name); create table student ( name varchar(10) prime key; age tinyint; [constraint ky_id1] primary key (name); ); 多字段主键 create table student ( name varchar(10) prime key; age tinyint; [constraint ky_id1] primary key (name，age); ); 删除、添加主键 alter table table1 add primary key (name); alter table table1 drop primary key; 2.唯一约束 create table student ( name varchar(10) unique; age tinyint; ); alter table table1 modify name varchar(10);//取消唯一约束; alter table table1 modify name varchar(10) unique;//添加唯一约束; 删除、添加唯一约束 alter table table1 add [constraint ky_id1] unique (name); alter table table1 drop key/index 唯一约束名; 3.非空约束 create table student ( name varchar(10) not null; age tinyint; ); alter table table1 modify name varchar(10);//取消非空约束; alter table table1 modify name varchar(10) not null;//添加非空约束; 4.默认约束 create table student ( name varchar(10) default '张三'; age tinyint; ); alter table table1 modify colunm1 set default '张三';//添加 alter table table1 modify colunm1 //取消 alter table table1 modify colunm1 drop default '张三';//取消 ; 5.外键约束(on delete cascade) create table student ( name varchar(10) default '张三'; age tinyint; [constraint k_id1] foreign key (name) references table2 (colunm1); ); alter table table1 add [constraint k_id1] foreign key (name) references table2 (colunm1); alter table table1 drop foreign key 约束名; 自增 aotu_increment=2;//默认增长值为1;可在初始化时设置，modify时修改(增加，取消); 设置自增数值后可修改 alter table table1 auto_increment = 5; |