MySQL constraint based on previous row value

Question

I have 2 tables:

Table A:

AID
1
2
3
4

Table B:

BID AID Status
1   1   Open
2   2   Open
3   3   Closed
4   1   Open - don't allow this row until AID 1 Status changes to closed
5   2   Open - don't allow this row until AID 2 Status changes to closed
6   3   Open - allow this row because AId 3 Status is closed
7   3   Open - don't allow this row until AID 3 Status changes to closed

How can I constraint adding another row in B table, until I change the status for AID to closed. Or should I move the Status column to A table? But even then how can I check in B table the status of A rows?

MySQL does not enforce check constraints. Your options then include using a trigger and/or a stored proc. — Tim Biegeleisen, Jun 23 '18 at 09:39
IF aid 4 does not exist are you allowed to insert with a status of closed? — P.Salmon, Jun 23 '18 at 09:42
Yes if AID 4 was not used yet it should be considered closed — Victordb, Jun 23 '18 at 09:56
Would building the logic into the insert be the best approach or using a trigger/stored proc? — Victordb, Jun 23 '18 at 09:57

Strawberry · Accepted Answer · 2018-06-23T10:02:52.413

By way of demonstration:

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(BID SERIAL PRIMARY KEY
,AID INT NOT NULL
,Status VARCHAR(12) NOT NULL
);

INSERT INTO my_table VALUES
(1,1,'Open'),
(2,2,'Open'),
(3,3,'Closed');

SELECT * FROM my_table;
+-----+-----+--------+
| BID | AID | Status |
+-----+-----+--------+
|   1 |   1 | Open   |
|   2 |   2 | Open   |
|   3 |   3 | Closed |
+-----+-----+--------+

INSERT INTO my_table (aid,status) 
SELECT 1
     , 'Open' 
  FROM (SELECT 1) x 
  LEFT 
  JOIN 
     ( SELECT a.* 
         FROM my_table a 
         JOIN 
            ( SELECT aid
                   , MAX(bid) bid 
                FROM my_table 
               GROUP  
                  BY aid
            ) b
           ON b.aid = a.aid
          AND b.bid = a.bid
      ) y
     ON y.aid = 1
    AND y.status = 'Open'
  WHERE y.bid IS NULL;

+-----+-----+--------+
| BID | AID | Status |
+-----+-----+--------+
|   1 |   1 | Open   |
|   2 |   2 | Open   |
|   3 |   3 | Closed |
+-----+-----+--------

INSERT INTO my_table (aid,status) VALUES
(1,'Closed');

SELECT * FROM my_table;
+-----+-----+--------+
| BID | AID | Status |
+-----+-----+--------+
|   1 |   1 | Open   |
|   2 |   2 | Open   |
|   3 |   3 | Closed |
|   4 |   1 | Closed |
+-----+-----+--------+

INSERT INTO my_table (aid,status) 
SELECT 1
     , 'Open' 
  FROM (SELECT 1) x 
  LEFT 
  JOIN 
     ( SELECT a.* 
         FROM my_table a 
         JOIN 
            ( SELECT aid
                   , MAX(bid) bid 
                FROM my_table 
               GROUP  
                  BY aid
            ) b
           ON b.aid = a.aid
          AND b.bid = a.bid
      ) y
     ON y.aid = 1
    AND y.status = 'Open'
  WHERE y.bid IS NULL;

SELECT * FROM my_table;
+-----+-----+--------+
| BID | AID | Status |
+-----+-----+--------+
|   1 |   1 | Open   |
|   2 |   2 | Open   |
|   3 |   3 | Closed |
|   4 |   1 | Closed |
|   5 |   1 | Open   |
+-----+-----+--------+

MySQL constraint based on previous row value

1 Answers1