IPSENH API Database script

ipsenh_db.sql

DROP TABLE IF EXISTS User_account;
DROP TABLE IF EXISTS User_role;

CREATE TABLE User_role (
	role_name VARCHAR(255),
	CONSTRAINT pk_role_name PRIMARY KEY (role_name)
);

CREATE TABLE User_account (
	id SERIAL,
	first_name VARCHAR(255) NOT NULL,
	insertion VARCHAR(255),
	last_name VARCHAR(255) NOT NULL,
	email VARCHAR(255) NOT NULL,
	date_of_birth DATE NOT NULL,
	password VARCHAR(255) NOT NULL,
	role VARCHAR(255) REFERENCES User_role (role_name),
	CONSTRAINT pk_id PRIMARY KEY (id),
	CONSTRAINT uni_email UNIQUE (email)
);

INSERT INTO User_role VALUES ('admin');
INSERT INTO User_role VALUES ('moduleleider');
INSERT INTO User_role VALUES ('user');

INSERT INTO User_account (first_name, last_name, email, date_of_birth, password, role) VALUES ('Jamie', 'Kalloe', 'jamie@kalloe.net', '1994-09-23', 'wachtwoord', 'admin');
INSERT INTO User_account (first_name, last_name, email, date_of_birth, password, role) VALUES ('Jamie2', 'Kalloe2', 'jamie2@kalloe.net', '1994-09-23', 'wachtwoord2', 'admin');