MySQL外键删除 [英] MySQL Foreign Key On Delete

问题描述

我试图找出关系和删除选项。

我有两个表， User 和 UserStaff ，与 User 至 UserStaff （用户可以有多个工作人员）。

当我的 User 被删除时，我想删除所有 UserStaff code>与该用户关联的表。当我的 UserStaff 被删除时，我不希望发生任何事情 User 。我知道这是一个级联的关系，但是我不确定是哪一种。

我是否在我的 UserStaff 表中选择现有的外键并使其级联，或者是在 User 中创建一个新的外键c>并设置为级联？

解决方案

是的，这是可能的。您应该在UserStaff表中创建FK。用这种方式：



用户表



  CREATE TABLE`User` （
`Id` int（11）NOT NULL AUTO_INCREMENT，
`Name` varchar（255）DEFAULT NULL，
 PRIMARY KEY（`Id`）
）ENGINE = InnoDB DEFAULT CHARSET = UTF8; 
  

UserStaff Table



  CREATE TABLE UserStaff（
`Id` int（11）NOT NULL AUTO_INCREMENT，
`UserId` int（11）NOT NULL DEFAULT'0'，
 PRIMARY KEY （`Id`），
 KEY`UserId`（`UserId`），
 CONSTRAINT`UserStaff_ibfk_1` 
 FOREIGN KEY（`UserId`）
 REFERENCES`User` `）
 ON DELETE CASCADE ON UPDATE CASCADE 
）ENGINE = InnoDB DEFAULT CHARSET = utf8; 
  




I am trying to figure out relationships and deletion options.

I have two tables, User and UserStaff, with a 1:n relationship from User to UserStaff (a user can have multiple staff members).

When my User is deleted, I want to delete all of the UserStaff tables associated with that User. When my UserStaff is deleted, I don't want anything to happen to User. I understand that this is a cascading relationship, but I'm not sure which way.

i.e. Do I select the existing foreign key in my UserStaff table and make it cascading, or do I create a new foreign key in User and set that to cascading?

解决方案

Yes, it's possible. You should make the FK in UserStaff table. In this way:

User Table

CREATE TABLE `User` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `Name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

UserStaff Table

CREATE TABLE `UserStaff` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `UserId` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`Id`),
  KEY `UserId` (`UserId`),
  CONSTRAINT `UserStaff_ibfk_1` 
    FOREIGN KEY (`UserId`) 
    REFERENCES `User` (`Id`) 
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

这篇关于MySQL外键删除的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！