how can I verify that each row of a table is associated with at least one row of another table in MySQL?

Question

I'm trying to verify that in a table of my DB each row is associated with at least one row of another table.

In particular I have these 3 tables:

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;

OK! So, the table "Product_Personage" expresses a many-to-many relationship between product and personage. I want to check that if a row in the product table has the "category" attribute set to "Comics" then this product must appear at least once in the Product_Personage table.

In the code I left what seems to me to be correct to do in the product table. That is CHECK.

I'm using MySQL and when execute this code I get an error that is:

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.

Does it make sense to do such a check, and if so is it correct to write it as I did? Thanks to all!

Where? I already use a FK constraint from Personage_Product table to Product table but that's not enough or am I wrong? Because if I add a product with category= "Comics" and nothing in Personage and in Personage_Product, it is correct for the DB but isn't correct for what I want to do. Because I want that if there is a product with Category = "Comics" there must be at least one Personage associated to this product in Personage_Product table. — , Sep 27 '19 at 18:13
Assuming you implement this check somehow (eg. with a trigger). You can't insert a new product with `category = 'comics'`, before it's already referenced in the `Product_Personage` table. And you can't reference it before you insert it. Or your question is too confusing. — Paul Spiegel, Sep 27 '19 at 18:56

score 1 · Answer 1 · answered Sep 27 '19 at 18:13

1

As in the mysql manual stated

 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

that is why

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

fails.

answered Sep 27 '19 at 18:13

nbk

45,398
8
30
47

Thanks a lot! I was going crazy without finding anything. So I understand that I have no way to verify this in any way? – Sep 27 '19 at 18:30
You get an error Message, by using foreign keys, when the id doesn't exist if the reference is the id of the table, so you cannot add a row without having a valid Product_Personage – nbk Sep 27 '19 at 18:48

how can I verify that each row of a table is associated with at least one row of another table in MySQL?

1 Answers1