PRAGMA foreign_keys = ON;

SELECT * FROM sqlite_schema;

CREATE TABLE payroll (
  user_id INTEGER PRIMARY KEY,
  name TEXT,
  job TEXT,
  salary INT
);

CREATE TABLE regist (
  user_id INT REFERENCES payroll,
  model TEXT
);

INSERT INTO payroll
  VALUES 
    (123, 'Jack', 'TA', 50000),
    (345, 'Allison', 'TA', 60000),
    (567, 'Magda', 'Prof', 90000),
    (789, 'Dan', 'Prof', 100000);

SELECT * FROM payroll;

INSERT INTO regist
  VALUES 
    (123, 'Charger'),
    (567, 'Civic'),
    (567, 'Pinto');

SELECT * FROM regist;

-- should fail
INSERT INTO regist VALUES (7777, 'hi');

SELECT *
FROM payroll p, regist r
WHERE p.user_id = r.user_id;

SELECT *
FROM payroll p
LEFT OUTER JOIN regist r
ON p.user_id = r.user_id;


DROP TABLE regist;

CREATE TABLE regist (
  user_id INT REFERENCES payroll,
  model TEXT,
  color TEXT,
  license_plate TEXT
);

INSERT INTO regist
  VALUES 
    (123, 'Charger', 'black', 'A77362'),
    (567, 'Civic', 'blue', 'CBU3543'),
    (567, 'Pinto', 'brown', 'BWC1922');

SELECT * FROM regist;

-- Dan co-owns the pinto with Magda
INSERT INTO regist
  VALUES
    (789, 'Pinto', 'brown', 'BWC1922');

-- Dan paints the pinto red without telling Magda
UPDATE regist
  SET color = 'red'
  WHERE user_id = 789;
-- incorrectly only updates one pinto row

SELECT * FROM regist;

-- We'd like the license_plate to be a primary key

DROP TABLE regist;

CREATE TABLE cars (
  model TEXT,
  color TEXT,
  license_plate TEXT PRIMARY KEY
);

INSERT INTO cars
  VALUES 
    ('Charger', 'black', 'A77362'),
    ('Civic', 'blue', 'CBU3543'),
    ('Pinto', 'brown', 'BWC1922');

SELECT * FROM cars;

CREATE TABLE regist (
  user_id INT REFERENCES payroll,
  license_plate REFERENCES cars
);
SELECT * FROM regist;

INSERT INTO regist
  VALUES 
    (123, 'A77362');

INSERT INTO regist
  VALUES
    (567, 'CBU3543'),
    (567, 'BWC1922');

-- Dan co-owns the Pinto
INSERT INTO regist
  VALUES
    (789, 'BWC1922');

UPDATE cars
  SET color = 'red'
  WHERE license_plate = 'BWC1922';