Checking if date in table B is between date in Table A before inserting SQLite-CodePudding

I have a table called Project with start and end dates. I also have a table called Plan which have its own start and end dates column. But I some way to validate that the Plans start/end date is between the matching Project start/end date.

I dont know if its appropriate to add a check when I create the table or when I insert rows to the Plan table. So far I have tried both with no luck.

The following code gives me an error message no such column. Does anyone know how to fix this problem? Thanks in advance.

https://i.stack.imgur.com/UC5Ai.png

%%sql
DROP TABLE IF EXISTS Plan;
CREATE TABLE Plan (
    pID varchar(255) NOT NULL UNIQUE,
    projectID varchar(255) NOT NULL UNIQUE,
    name varchar(255) NOT NULL DEFAULT ' ',
    startDate DATE NOT NULL DEFAULT '2000-12-31',
    endDate DARE NOT NULL DEFAULT '2000-12-31'
    CHECK (JulianDay(startDate) <= JulianDay(endDate) AND (startDate >= Project.startDate) AND 
           (endDate <= Project.endDate)),
    PRIMARY KEY (pID, projectID),
    FOREIGN KEY (projectID) REFERENCES Project(projectID)
);

CodePudding user response：

You need a BEFORE INSERT trigger:

CREATE TRIGGER trg_ins_plan BEFORE INSERT ON Plan
BEGIN
  SELECT 
    CASE 
      WHEN NOT EXISTS (
          SELECT 1 
          FROM Project p 
          WHERE p.projectID = NEW.projectID AND p.startDate <= NEW.startDate AND p.endDate >= NEW.endDate
        )
        THEN RAISE(ABORT, 'Invalid dates')
    END;  
END;

and a BEFORE UPDATE trigger:

CREATE TRIGGER trg_upd_plan BEFORE UPDATE ON Plan
BEGIN
  SELECT 
    CASE 
      WHEN NOT EXISTS (
          SELECT 1 
          FROM Project p 
          WHERE p.projectID = NEW.projectID AND p.startDate <= NEW.startDate AND p.endDate >= NEW.endDate
        )
        THEN RAISE(ABORT, 'Invalid dates')
    END;  
END;

Also, change the CHECK constraint for the dates to:

CHECK (JulianDay(startDate) <= JulianDay(endDate))

or just:

CHECK (startDate <= endDate)

See the demo.