外键约束:foreign key
编辑数据表的默认存储引擎,MySQL配置文件,default-storage-engine=INNODB
案例:
create table provices(
id smallint unsigned primary key auto_increment,
pname varchar(20) not null
);
create table users(
id smallint unsigned primary key auto_increment,
username varchar(10) not null,
pid smallint unsigned,
foreign key (pid) references provices(id)
);
其中provices的id为参考列
查看索引:show indexes from provices\G;
CREATE TABLE `users1` (
`id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`username` varchar(10) NOT NULL,
`pid` smallint(5) unsigned DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `pid` (`pid`),
CONSTRAINT `users1_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `provices` (`id`) ON DELETE CASCADE
)
修改数据表
a.添加单列
alter table tbl_name add [column] col_name column_definition [first | after col_name]col
b.添加多列
alter table tbl_name add [column] (col_name column_definition,…)
c.删除列
alter table tbl_name drop [column] col_name
d.添加主键约束
alter table tbl_name add [constraint [symbol]] primary key [index_type] (index_col_name,…)
案例:
create table users2(
username varchar(10) not null,
pid smallint unsigned
);
添加一列: alter table users2 add id smallint unsigned;
添加主键约束: alter table users2 add constraint PK_users2_id primary key (id);
e.添加唯一约束
alter table tbl_name add [constraint [symbol]] unique [index|key] [index_name] [index_type] (index_col_name,…)
案例:
alter table users2 add unique (username);
f.添加外键约束
alter table tbl_name add [constraint [symbol]] foreign key [index_name] (index_col_name,…) reference_definition
案例:
alter table users2 add foreign key (pid) references provices (id);
g.添加/删除默认约束
alter table tbl_name alter [COLUMN] col_name {set default literal | drop default}
案例:
alter table users2 add age tinyint unsigned not null;
alter table users2 alter age set default 15;
h.删除约束
删除主键约束:alter table tbl_name drop primary key
删除唯一约束:alter table tbl_name drop {index|key} index_name
删除外键约束:alter table tbl_name drop froeign key fk_symbol
i.修改列定义
alter table tbl_name modify [column] col_name column_definition [first|after col_name]
案例:
alter table users2 modify id smallint unsigned not null first;
j.修改列名称
alter table tbl_name change [column] old_col_name new_col_name column_definition [first|after col_name]
案例:
ALTER TABLE users2 CHANGE pid p_id TINYINT UNSIGNED
k.数据表更名
方法1
alter table tbl_name rename [to|as] new_tbl_name
方法2
rename table tbl_name to new_tbl_name [,tbl_name2 to new_tbl_name2]…
案例:
alter table users2 rename users3;