添加和删除外键

建表同时添加外键

CREATE TABLE event( 
id INT PRIMARY KEY AUTO_INCREMENT, 
username VARCHAR(20) NOT NULL, 
happen varchar(20), 
CONSTRAINT username
FOREIGN KEY(username) 
REFERENCES user(username)
ON UPDATE CASCADE 
ON DELETE CASCADE
);

View Code

格式

外键约束
symbol 约束名，最好写一下，不写会自动生成

[CONSTRAINT [symbol]] FOREIGN KEY
[index_name] (index_col_name, ...)
REFERENCES tbl_name (index_col_name,...)
[ON DELETE reference_option]
[ON UPDATE reference_option]
 
reference_option:
RESTRICT      -->      拒绝执行
CASCADE       -->      自动执行
SET NULL      -->      置为空
NO ACTION     -->      无动作

View Code

删除外键约束

ALTER TABLE table-name DROP FOREIGN KEY key-id;
如果不是道 key-id 需要用到：
show create table table-name;

View Code

增加外键时，要注意外键对应的主键一定要有对应值

ALTER TABLE event ADD CONSTRAINT username FOREIGN KEY(username) REFERENCES user(username) ON UPDATE CASCADE ON DELETE CASCADE;

View Code

 

转载于:https://www.cnblogs.com/tujw/p/10631213.html