Skip to content
Snippets Groups Projects
Select Git revision
  • a51fb285dbd739d09a2f46ab585ddf072d75f891
  • main default protected
2 results

createTables.sql

Blame
  • 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)
    );