jc6ScAJD

1CREATE TABLE IF NOT EXISTS users (
2  id UUID PRIMARY KEY NOT NULL,
3  email TEXT UNIQUE NOT NULL,
4  username TEXT UNIQUE NOT NULL,
5  password TEXT NOT NULL,
6  salt TEXT NOT NULL,
7  name TEXT,
8  last_name TEXT,
9  sex CHAR(1),
10  birthday DATE,
11  created_at TIMESTAMP,
12  updated_at TIMESTAMP
13);
14
15CREATE TRIGGER validate_sex AFTER INSERT ON users FOR EACH ROW BEGIN
16  IF new.sex COLLATE latin1_bin NOT IN ('M','F','O') AND new.sex IS NOT NULL THEN
17    SIGNAL SQLSTATE '22222' SET MESSAGE_TEXT='Valid values for sex are either ''M'', ''F'', ''O'' or NULL.';
18  END IF
19END
20
21CREATE TABLE IF NOT EXISTS countries (
22  country_code CHAR(5) PRIMARY KEY NOT NULL,
23  full_name TEXT UNIQUE NOT NULL,
24);
25
26CREATE TABLE IF NOT EXISTS addresses (
27  zip_code TEXT PRIMARY KEY NOT NULL,
28  country CHAR(5) NOT NULL,
29  city TEXT NOT NULL,
30  CONSTRAINT country_pk FOREIGN KEY (country) REFERENCES countries (country_code)
31);
32
33CREATE TABLE IF NOT EXISTS languages (
34  id INT PRIMARY KEY NOT NULL,
35  name TEXT UNIQUE NOT NULL
36);
37
38CREATE TABLE IF NOT EXISTS user_languages (
39  user_id UUID NOT NULL,
40  language_id INT NOT NULL,
41  PRIMARY KEY(user_id, language_id),
42  CONSTRAINT user_fk FOREIGN KEY (user_id) REFERENCES users(id),
43  CONSTRAINT language_fk FOREIGN KEY (language_id) REFERENCES languages(id)
44);