How do I create a Select statement that can be used in a Create Assertion Check? -MySQL

Question

I am a sql and mysql newbie, so I am not sure about how this works. I know that mysql doesn't recognise create assertion checks. But I am trying to do one for a boolean value to enforce a constraint for a particular Customer in my Customer Table.

I've been told that I can use CREATE TRIGGER for this, but haven't found a good enough example to see how this can be applied.

CREATE ASSERTION CHECK(

Contains_Nuts FROM Bagel
WHERE Contains_Nuts = TRUE

SELECT Name, DOB
FROM Customer WHERE Nut_Allergy = TRUE);

I know this question is quite vague and that the code is entirely wrong so let me know what I need to add to make it clearer.

As requested here are the Table designs that I want to apply,

CREATE TABLE Customer(
CustomerID INTEGER NOT NULL, 
Name VARCHAR(255) NOT NULL,
Address_HouseNum VARCHAR(5),
Address_PostCode VARCHAR(8) NOT NULL,
Address_StreetName VARCHAR(255),
Title VARCHAR(6),
Email VARCHAR(255),
DOB DATE, 
Order_History INTEGER,            
Nut_Allergy Boolean, 
PRIMARY KEY (CustomerID)
);

CREATE TABLE Bagel(
BagelID INTEGER NOT NULL,
Contains_Nuts BOOLEAN NOT NULL,
Price DECIMAL(4,2) NOT NULL, 
Description VARCHAR(255),
PRIMARY KEY(BagelID)
);

CREATE TABLE `Order`(
OrderID INTEGER NOT NULL, 
O_CustomerID INTEGER NOT NULL,  
O_BagelCardID VARCHAR(16),
O_BagelID INTEGER,
O_BagelFillingID INTEGER NOT NULL,
O_DrinkID INTEGER,
Order_date DATE NOT NULL, 
Order_Cost DECIMAL(4,2) NOT NULL, -- Assumption : Order_cost is the final price worked out after a discount has/hasnot been applied. 
Discount_applied BOOLEAN, 
PRIMARY KEY(OrderID,O_CustomerID,O_BagelCardID,O_BagelID,O_BagelFillingID,O_DrinkID),

FOREIGN KEY (O_CustomerID) 
REFERENCES Customer(CustomerID)
ON DELETE RESTRICT
ON UPDATE CASCADE,

FOREIGN KEY (O_BagelCardID) 
REFERENCES BagelCard(BagelCardID)
ON DELETE RESTRICT
ON UPDATE CASCADE,

FOREIGN KEY (O_BagelID) 
REFERENCES Bagel(BagelID)
ON DELETE RESTRICT
ON UPDATE CASCADE,

FOREIGN KEY (O_BagelFillingID) 
REFERENCES BagelFilling(BagelFillingID)
ON DELETE RESTRICT
ON UPDATE CASCADE,

FOREIGN KEY (O_DrinkID) 
REFERENCES Drink(DrinkID)
ON DELETE RESTRICT
ON UPDATE CASCADE

);

To get it right: You have a table `orders` and a table `customers` and want to make sure that an order for a particular customer can only be placed (inserted into `orders`) if that customer has the attribute `nut_allergy` set to FALSE. Right? Can you please show the structures of both tables (`desc orders` and `desc customer`)? — PerlDuck, Mar 13 '16 at 12:40
Correct, I have quite a bit of tables, but the Contains_Nuts attribute is stored in another table called `Bagel` I placed `Orders` as sort of a placeholder in the example I tried to give. I am just trying to get my select statement to yield False. — LovesPie, Mar 13 '16 at 12:46
So what is the junction table, i.e. how do `bagel` and `customer` relate to each other? Either they have attributes in common (bad) or there is a third table with `(bagle_id, customer_id)` (better). Without knowing that it's not possible to give you an example. — PerlDuck, Mar 13 '16 at 12:53
Oh sorry, yes the third table would be `order`, which contains O_BagelID and O_CustomerID — LovesPie, Mar 13 '16 at 12:55

score 0 · Answer 1 · edited May 23 '17 at 11:59

I'd try this INSERT trigger:

DELIMITER $$
CREATE TRIGGER check_healthyness BEFORE INSERT ON orders
FOR EACH ROW
  BEGIN
    IF EXISTS ( select 1
                  from bagel b join customer c
                    on (b.BagelID=NEW.O_BagelID 
                        and c.CustomerID=NEW.O_CustomerID)
                 where (c.nut_allergy=true 
                        and b.contains_nuts=true) ) THEN
        -- complain:
        signal sqlstate '45000' set message_text = 'Bagel contains nuts';               
    END IF;               
  END $$
DELIMITER ;

Refer to this for the SIGNAL clause (use MySQL 5.5+).

This trigger will make INSERTs into orders impossible if it would place an order of nutty bagels for a given customer. You may also establish an UPDATE trigger (in addition to this one) to prevent UPDATEs to the order table.

I haven't tested it, but the SELECT statement should give you an idea.

How do I create a Select statement that can be used in a Create Assertion Check? -MySQL

1 Answers1

Linked