
PRAGMA FOREIGN_KEYS = on;

DROP TABLE IF EXISTS ASSEMBLY_LINE;
DROP TABLE IF EXISTS RECIPE;

CREATE TABLE ASSEMBLY_LINE (
    NUMBER integer PRIMARY KEY AUTOINCREMENT,
    RECIPE text,
    MIXINS_OR_NOT text NOT NULL,
    CAPACITY real,
    FOREIGN KEY (RECIPE) REFERENCES RECIPE(NAME) ON UPDATE CASCADE ON DELETE CASCADE
);

CREATE TABLE RECIPE (
    NAME text PRIMARY KEY,
    BASEFLAVOR text,
    CARTONS_ORDERED integer
    );

INSERT INTO RECIPE VALUES ('NYSFC', 'chocolate', 150),
    ('MMV','Vanilla',200);

INSERT INTO ASSEMBLY_LINE (NUMBER, RECIPE, CAPACITY, MIXINS_OR_NOT)
    values
    (1,'NYSFC',50,'Yes'),
    (2,'NYSFC',150,'Yes'),
    (3,'MMV',25,'No'),
    (4,'MMV',25,'No');

UPDATE RECIPE set name='PF' where name='MMV';
DELETE from RECIPE where name='NYSFC';
