Select Git revision
createTables.sql
-
Charlie Darques authoredCharlie Darques authored
createTables.sql 1.41 KiB
-- Création des tables nécessaires au SI de l'application
DROP TABLE IF EXISTS userAccount, msg, thread, reactions, follow CASCADE;
CREATE TABLE userAccount (
userID SERIAL,
username VARCHAR(50),
password VARCHAR(20),
CONSTRAINT pk_user PRIMARY KEY (userID)
);
CREATE TABLE msg (
msgID SERIAL,
userID_msg int,
threadID int,
msg text,
CONSTRAINT fk_message FOREIGN KEY (userID_msg) REFERENCES userAccount(userID),
CONSTRAINT pk_message PRIMARY KEY (msgID)
);
CREATE TABLE thread (
threadID SERIAL,
userID_thread int,
threadName VARCHAR(50),
CONSTRAINT fk_thread FOREIGN KEY (userID_thread) REFERENCES userAccount(userID),
CONSTRAINT pk_thread PRIMARY KEY (threadID)
);
CREATE TABLE reactions (
userID_reactions int,
msgID_reactions int,
reaction boolean,
CONSTRAINT fk_reactions_user FOREIGN KEY (userID_reactions) REFERENCES userAccount(userID),
CONSTRAINT fk_reactions_message FOREIGN KEY (msgID_reactions) REFERENCES msg(msgID),
CONSTRAINT pk_reactions PRIMARY KEY (userID_reactions, msgID_reactions)
);
CREATE TABLE follow (
userID_follow int,
threadID_follow int,
followDate date,
CONSTRAINT fk_follow_user FOREIGN KEY (userID_follow) REFERENCES userAccount(userID),
CONSTRAINT fk_follow_thread FOREIGN KEY (threadID_follow) REFERENCES thread(threadID),
CONSTRAINT pk_follow PRIMARY KEY (userID_follow, threadID_follow)
);