数据库外键 附数据和代码

参考这篇博客https://blog.csdn.net/zengraoli/article/details/16857835
数据库外键定义:

数据库外键作用:

数据库外键和主键的区别:

数据库外键demo;

创建数据库

DROP schema IF EXISTS `demo`;
CREATE schema demo;

创建表

drop table if exists `demo`.`user`;
CREATE TABLE user_info  
(  
    `user_id`  INTEGER(11) NOT NULL AUTO_INCREMENT,  
    `username` NVARCHAR(64) COLLATE utf8_general_ci DEFAULT NULL,  
    `password` NVARCHAR(64) COLLATE utf8_general_ci DEFAULT NULL,  
    `qianming` TEXT COLLATE utf8_general_ci,  
    `email`    NVARCHAR(64) COLLATE utf8_general_ci DEFAULT NULL,   
    `pic` VARCHAR(64) COLLATE utf8_general_ci DEFAULT NULL,  
    PRIMARY KEY (`user_id`)
)ENGINE=INNODB AUTO_INCREMENT=237 CHARACTER SET 'utf8' COLLATE 'utf8_general_ci' AUTO_INCREMENT=237;  
ALTER TABLE `demo`.`user_limits` 
AUTO_INCREMENT = 237 ;

MYISAM不支持外键。

DROP TABLE IF EXISTS `demo`.`user_limits`;
CREATE TABLE user_limits(  
    user_conut INTEGER(11) AUTO_INCREMENT PRIMARY KEY,  
    txtnewbrowsecount INT,  
    picnewbrowsecount INT,  
    videonewbrowsecount INT,  
    user_id INT,#外键user_info  
    FOREIGN KEY(user_id) REFERENCES  user_info(user_id) ON DELETE CASCADE ON UPDATE CASCADE ##创建外键关系语句  
)engine=INNODB AUTO_INCREMENT=237;  

在user_limits上foreign key栏中看到了外键的设置
这里写图片描述

在user_info中没有看到外键

往user_info表中插入测试数据

INSERT INTO `user_info`  
(`username`, `password`, `qianming`, `email`, `pic`)  
VALUES  
('zengraoli1', '123456', 'my name is zengraoli1', 'test@111.com', ''),  
('zengraoli2', '123456', 'my name is zengraoli2', 'test@112.com', ''),  
('zengraoli3', '123456', 'my name is zengraoli3', 'test@113.com', ''),  
('zengraoli4', '123456', 'my name is zengraoli4', 'test@114.com', ''),  
('zengraoli5', '123456', 'my name is zengraoli5', 'test@115.com', ''),  
('zengraoli6', '123456', 'my name is zengraoli6', 'test@116.com', '');COMMIT; 

往user_limits表中插入测试数据,报错,外键和user_info不匹配

Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (`demo`.`user_limits`, CONSTRAINT `user_limits_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `user_info` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE)

更改成这样,事务提交成功。

INSERT INTO `demo`.`user_limits`  
VALUES  
(NULL, 10, 10, 10, 237),  
(NULL, 10, 8, 10, 238),  
(NULL, 10, 10, 10, 239),  
(NULL, 10, 10, 10, 240),  
(NULL, 10, 0, 10, 241),  
(NULL, 10, 2, 10, 242);COMMIT; 

user_info


237 zengraoli1  123456  my name is zengraoli1   test@111.com    
238 zengraoli2  123456  my name is zengraoli2   test@112.com    
239 zengraoli3  123456  my name is zengraoli3   test@113.com    
240 zengraoli4  123456  my name is zengraoli4   test@114.com    
241 zengraoli5  123456  my name is zengraoli5   test@115.com    
242 zengraoli6  123456  my name is zengraoli6   test@116.com    

user_limits

7   10  10  10  237
8   10  8   10  238
9   10  10  10  239
10  10  10  10  240
11  10  0   10  241
12  10  2   10  242

删除user_info中的user_id = 239

DELETE FROM `demo`.`user_info` WHERE `user_id`='239';

user_limits中的user_id=239行也跟着删除了。

阅读更多
想对作者说点什么?

博主推荐

换一批

没有更多推荐了,返回首页