drop database emaille;
create database emaille;
\connect emaille

CREATE TABLE person (
    id integer,
    nname text,
    vname text,
    PRIMARY KEY (id)   -- das ist ein CONSTRAINT
                       -- PRIMARY KEY = UNIQUE & NOT NULL
);

INSERT INTO person values (20, 'neumann', 'alfred e.');
--INSERT INTO person values (2020, 'neumann', 'alfred e.');
INSERT INTO person (nname, vname, id) VALUES ('walkes', 'otto', 3);
INSERT INTO person (nname, vname, id) VALUES ('walkes', 'otto', 4);

CREATE TABLE emaille(
       emaille text,
       person integer,
       PRIMARY KEY (emaille),
       FOREIGN KEY (person) REFERENCES person(id)
       	       ON UPDATE cascade
	       ON DELETE cascade
);

--CREATE TABLE emaille(
--       emaille text primary key,
--       person integer references person(id)
--);

INSERT INTO emaille VALUES ('alfred@mad.org',20);
INSERT INTO emaille VALUES ('alfred1@mad.org',20);
INSERT INTO emaille VALUES ('alfred2@mad.org',20);
INSERT INTO emaille VALUES ('alfred3@mad.org',20);
INSERT INTO emaille VALUES ('alfred4@mad.org',20);
INSERT INTO emaille VALUES ('alfred5@mad.org',20);
SELECT * FROM emaille;

begin;
savepoint vorher;
DELETE FROM person WHERE nname='neumann';
select * from person;

SELECT * FROM emaille;
--UPDATE person SET id=9999 WHERE id=20;
--SELECT * from person;

ROLLBACK TO vorher;
SELECT * FROM emaille;
commit;
--SELECT * FROM person INNER JOIN emaille on person.id = emaille.person;
