By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE Project (projectID INTEGER, startDate TEXT, endDate TEXT)
INSERT INTO Project VALUES
(1, '2021-05-15', '2021-08-31'),
(2, '2021-06-01', '2021-06-30');
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)),
PRIMARY KEY (pID, projectID),
FOREIGN KEY (projectID) REFERENCES Project(projectID)
);
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;
INSERT INTO Plan VALUES (1, 1, 'name1', '2021-05-31', '2021-08-01')
INSERT INTO Plan VALUES (1, 1, 'name1', '2021-03-31', '2021-08-01')
Invalid dates
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;
UPDATE Plan
SET startDate = '2021-06-30'
WHERE pID = 1;
UPDATE Plan
SET startDate = '2021-03-31'
WHERE pID = 1;
Invalid dates