87 lines
2.4 KiB
MySQL
87 lines
2.4 KiB
MySQL
|
drop trigger trg_novels on copper.novels;
|
||
|
|
drop trigger trg_authors on copper.authors;
|
||
|
|
drop trigger trg_actors on copper.actors;
|
||
|
|
|
||
|
|
ALTER TABLE copper.novels
|
||
|
|
DROP CONSTRAINT nvl_FK_thrs;
|
||
|
|
ALTER TABLE copper.novels
|
||
|
|
DROP CONSTRAINT nvl_FK_ctrs;
|
||
|
|
ALTER TABLE copper.authors
|
||
|
|
DROP CONSTRAINT thrs_FK_ctrs;
|
||
|
|
|
||
|
|
drop table copper.novels;
|
||
|
|
drop table copper.authors;
|
||
|
|
drop table copper.actors;
|
||
|
|
|
||
|
|
drop sequence copper.novels_star_seq;
|
||
|
|
drop sequence copper.authors_star_seq;
|
||
|
|
drop sequence copper.actors_star_seq;
|
||
|
|
|
||
|
|
CREATE SEQUENCE copper.novels_star_seq;
|
||
|
|
CREATE SEQUENCE copper.authors_star_seq;
|
||
|
|
CREATE SEQUENCE copper.actors_star_seq;
|
||
|
|
|
||
|
|
CREATE TABLE copper.novels (
|
||
|
|
star INTEGER DEFAULT nextval('copper.novels_star_seq') NOT NULL PRIMARY KEY,
|
||
|
|
title VARCHAR(1023) NOT NULL,
|
||
|
|
language VARCHAR(255) NOT NULL,
|
||
|
|
author integer NOT NULL,
|
||
|
|
modifier integer NOT NULL,
|
||
|
|
creationDate timestamp NOT NULL DEFAULT now(),
|
||
|
|
updateDate timestamp
|
||
|
|
) tablespace waxts;
|
||
|
|
|
||
|
|
CREATE TABLE copper.authors (
|
||
|
|
star INTEGER DEFAULT nextval('copper.authors_star_seq') NOT NULL PRIMARY KEY,
|
||
|
|
name VARCHAR(255) NOT NULL,
|
||
|
|
surname VARCHAR(255) NOT NULL,
|
||
|
|
modifier integer NOT NULL,
|
||
|
|
creationDate timestamp NOT NULL DEFAULT now(),
|
||
|
|
updateDate timestamp
|
||
|
|
) tablespace waxts;
|
||
|
|
|
||
|
|
CREATE TABLE copper.actors (
|
||
|
|
star INTEGER DEFAULT nextval('copper.actors_star_seq') NOT NULL PRIMARY KEY,
|
||
|
|
userid VARCHAR(255),
|
||
|
|
password VARCHAR(255),
|
||
|
|
lastUpdatePassword timestamp,
|
||
|
|
firstLogin timestamp,
|
||
|
|
lastLogin timestamp,
|
||
|
|
penalities INTEGER,
|
||
|
|
firstname VARCHAR(255) NOT NULL,
|
||
|
|
lastname VARCHAR(255) NOT NULL,
|
||
|
|
rating INTEGER NOT NULL DEFAULT 0,
|
||
|
|
creationDate timestamp NOT NULL DEFAULT now(),
|
||
|
|
updateDate timestamp
|
||
|
|
) tablespace waxts;
|
||
|
|
|
||
|
|
create trigger trg_novels before update
|
||
|
|
on copper.novels for each row
|
||
|
|
execute procedure upd_dt();
|
||
|
|
create trigger trg_authors before update
|
||
|
|
on copper.authors for each row
|
||
|
|
execute procedure upd_dt();
|
||
|
|
create trigger trg_actors before update
|
||
|
|
on copper.actors for each row
|
||
|
|
execute procedure upd_dt();
|
||
|
|
|
||
|
|
ALTER TABLE copper.novels
|
||
|
|
ADD CONSTRAINT nvl_FK_thrs
|
||
|
|
FOREIGN KEY(author)
|
||
|
|
REFERENCES copper.authors(star)
|
||
|
|
ON DELETE CASCADE
|
||
|
|
ON UPDATE CASCADE;
|
||
|
|
ALTER TABLE copper.novels
|
||
|
|
ADD CONSTRAINT nvl_FK_ctrs
|
||
|
|
FOREIGN KEY(modifier)
|
||
|
|
REFERENCES copper.actors(star)
|
||
|
|
ON DELETE CASCADE
|
||
|
|
ON UPDATE CASCADE;
|
||
|
|
ALTER TABLE copper.authors
|
||
|
|
ADD CONSTRAINT thrs_FK_ctrs
|
||
|
|
FOREIGN KEY(modifier)
|
||
|
|
REFERENCES copper.actors(star)
|
||
|
|
ON DELETE CASCADE
|
||
|
|
ON UPDATE CASCADE;
|
||
|
|
|