时间戳

首页 > 解决方案 > 如何验证一个表的每一行是否与 MySQL 中另一个表的至少一行相关联？

mysql - 如何验证一个表的每一行是否与 MySQL 中另一个表的至少一行相关联？

问题描述

我正在尝试验证在我的数据库的表中，每一行都与另一个表的至少一行相关联。

特别是我有这3张桌子：

CREATE TABLE IF NOT EXISTS coltraneShop.Personage (
  `Name` VARCHAR(20) NOT NULL,

  CONSTRAINT PK_Personage PRIMARY KEY (`Name`)
);

CREATE TABLE IF NOT EXISTS coltraneShop.`Product_Personage` (
  `Product code` BIGINT UNSIGNED NOT NULL,
  `Personage's name` VARCHAR(20) NOT NULL,

  CONSTRAINT PK_Product_Personage PRIMARY KEY (`Product code`, `Personage's name`)
);
ALTER TABLE coltraneShop.`Product_Personage` ADD CONSTRAINT `FK_ProductPersonage_Personage` FOREIGN KEY (`Personage's name`) REFERENCES coltraneShop.Personage(`Name`)
ON DELETE CASCADE ON UPDATE CASCADE;

CREATE TABLE IF NOT EXISTS coltraneShop.Product (
  `Product code` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `Category` CHAR(9) NOT NULL,
  CHECK(`Category`="DVD" OR `Category`='Album' OR `Category`='Comics' OR `Category`='Book' OR `Category`='Videogame'),
  `Title` VARCHAR(50) NOT NULL,
  `Quantity` INT UNSIGNED NOT NULL,
  CHECK(`Quantity` BETWEEN 000 AND 999),
  `Year of publication` INT UNSIGNED NOT NULL,
  CHECK(`Year of publication` BETWEEN 0000 AND 9999), 
  `Price` DECIMAL(6,2) UNSIGNED NOT NULL,
  `Shipment's name` CHAR(13) NOT NULL, 

  CHECK(
    (SELECT count(*)
    FROM coltraneShop.Product_Personage AS PP
    WHERE `Category`= "Comics" AND PP.`Product code` = `Product code`) >= 1
),

CONSTRAINT PK_Product PRIMARY KEY (`Product code`)
);
ALTER TABLE coltraneShop.Product ADD CONSTRAINT `FK_Product_Shipment` FOREIGN KEY(`Shipment's name`) REFERENCES coltraneShop.Shipment(`Name`)
ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE coltraneShop.`Product_Personage` ADD CONSTRAINT `FK_ProductPersonage_Product` FOREIGN KEY (`Product code`) REFERENCES coltraneShop.Product(`Product code`)
ON DELETE CASCADE ON UPDATE CASCADE;

好的！因此，“Product_Personage”表表达了产品和人物之间的多对多关系。我想检查如果产品表中的一行将“类别”属性设置为“漫画”，那么该产品必须在 Product_Personage 表中至少出现一次。

在代码中，我在产品表中留下了我认为正确的做法。那就是检查。

我正在使用 MySQL，执行此代码时出现错误：

CREATE TABLE IF NOT EXISTS coltraneShop.Product (     `Product code` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
 `Category` CHAR(9) NOT NULL,
 CHECK(`Category`="DVD" OR `Category`='Album' OR `Category`='Comics' OR `Category`='Book' OR `Category`='Videogame'),     `Title` VARCHAR(50) NOT NULL,
 `Quantity` INT UNSIGNED NOT NULL,
 CHECK(`Quantity` BETWEEN 000 AND 999),
 `Year of publication` INT UNSIGNED NOT NULL,
 CHECK(`Year of publication` BETWEEN 0000 AND 9999),
  `Price` DECIMAL(6,2) UNSIGNED NOT NULL,
 `Shipment's name` CHAR(13) NOT NULL,
   CHECK(         (SELECT count(*)         FROM coltraneShop.Product_Personage AS PP         WHERE `Category`= "Comics" AND PP.`Product code` = `Product code`) >= 1     ),      
CONSTRAINT PK_Product PRIMARY KEY (`Product code`) )

Error Code: 3815. An expression of a check constraint 'Product_chk_4' contains disallowed function.

做这样的检查是否有意义，如果是这样，像我一样写它是否正确？谢谢大家！

标签： mysqlsqlmysqlcheck

解决方案

如mysql手册中所述

 Stored functions and user-defined functions are not permitted.

Stored procedure and function parameters are not permitted.

Variables (system variables, user-defined variables,
 and stored program local variables) are not permitted.

Subqueries are not permitted.

https://dev.mysql.com/doc/refman/8.0/en/create-table-check-constraints.html

这就是为什么

 CHECK((SELECT count(*) FROM coltraneShop.Product_Personage AS PP
     WHERE `Category`= "Comics" AND PP.`Product code` = `Product code`) >= 1),

失败。

推荐阅读