时间戳

首页 > 解决方案 > MySQL中交叉检查用户行的外键约束

mysql - MySQL中交叉检查用户行的外键约束

问题描述

仅当外键数据实际上属于执行插入/更新的用户时，如何使用外键强制插入/更新查询成功。

例如，用户 1 创建的类别不能被用户 2 使用，反之亦然以禁止交叉访问。

Create Table Users(
    user_id int not null AUTO_INCREMENT,
    user_name varchar(50) not null,
    city varchar(50) default null,
    PRIMARY KEY (user_id)
);

-- Insert users
Insert into Users(user_name, city) Values('Nick', 'New york');
Insert into Users(user_name, city) Values('John', 'Seattle');

创建类别

Create Table Categories(
    category_id int not null AUTO_INCREMENT,
    user_id int not null,
    category_name varchar(50) not null,
    PRIMARY KEY (category_id),
  CONSTRAINT FK_CategoryUser FOREIGN KEY (user_id) REFERENCES Users(user_id)  
);

-- Insert category for Nick with user_id = 1
Insert into Categories(user_id, category_name) Values(1, 'Travel');

-- Insert category for John user_id = 2
Insert into Categories(user_id, category_name) Values(2, 'Science');

创建书籍

Create Table Books(
    book_id int not null AUTO_INCREMENT,
    user_id int not null,
    category_id int default null,    
    book_name varchar(50) not null,
    book_type varchar(50) not null,
    PRIMARY KEY (book_id),
  CONSTRAINT FK_BookUser FOREIGN KEY (user_id) REFERENCES Users(user_id),
  CONSTRAINT FK_BookCategory FOREIGN KEY (category_id) REFERENCES Categories(category_id)
);

现在尼克的插入书必须失败，因为带有行的“科学”类别category_id = 2属于约翰user_id = 1

Insert into Books(user_id, category_id, book_name, book_type)
Values(1, 2, 'My science book', 'online')

标签： mysqlsqlamazon-rds

解决方案

既然你有Categories.user_id，你可以把它作为一个键：

ALTER TABLE Categories ADD UNIQUE KEY (user_id, category_id);

然后从 Books 表中引用它：

ALTER TABLE Books ADD FOREIGN KEY (user_id, category_id) 
  REFERENCES Categories (user_id, category_id);

这要求只有当该用户被分配了相应的类别时才将一本书提供给该用户。但它允许 Books 中的多行具有相同的用户和类别对。

将 Categories 中的新键设为 UNIQUE KEY 似乎是多余的，因为 category_id 本身就已经是唯一的了。但在一般 SQL 语言中，外键必须引用 UNIQUE KEY。

推荐阅读