blog.sql

CREATE DATABASE php_blog;

USE php_blog

CREATE TABLE user (
    ID INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(30) NOT NULL,
    last_name VARCHAR(30)  NOT NULL,
    email VARCHAR(50) NOT NULL UNIQUE,
    password CHAR(60) NOT NULL,
    reg_date TIMESTAMP
        DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE article (
    ID INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title TEXT NOT NULL,
    content MEDIUMTEXT NOT NULL,
    creation_date TIMESTAMP
        DEFAULT CURRENT_TIMESTAMP,
    last_edit_date TIMESTAMP
        DEFAULT CURRENT_TIMESTAMP
        ON UPDATE CURRENT_TIMESTAMP,
    userID INT(10) UNSIGNED,
    FOREIGN KEY (userID)
        REFERENCES user (ID)
        ON DELETE SET NULL
);

CREATE TABLE comment (
    ID INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    comment TEXT NOT NULL,
    creation_date TIMESTAMP
        DEFAULT CURRENT_TIMESTAMP,
    last_edit_date TIMESTAMP
        DEFAULT CURRENT_TIMESTAMP
        ON UPDATE CURRENT_TIMESTAMP,
    userID INT(10) UNSIGNED,
    articleID INT(10) UNSIGNED,
    FOREIGN KEY (userID)
        REFERENCES user (ID)
        ON DELETE SET NULL,
    FOREIGN KEY (articleID)
        REFERENCES article (ID)
        ON DELETE CASCADE
);