JXLdytCK

1//Twitter DATABASE AND TABLES
2
3
4DATABASE:
5
6CREATE DATABASE IF NOT EXISTS Twitter DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci;
7
8TABELE:
9
10CREATE TABLE IF NOT EXISTS Tweet(
11`id` INT NOT NULL AUTO_INCREMENT,
12`userId` INT NOT NULL,
13`text` VARCHAR(142) NOT NULL,
14`creationDate` datetime NOT NULL,
15PRIMARY KEY(`id`),
16FOREIGN KEY(`userId`) REFERENCES Users(`id`)
17ON DELETE CASCADE,
18UNIQUE INDEX `id_UNIQUE` (`id` ASC)
19
20)
21ENGINE = InnoDB;
22
23
24CREATE TABLE IF NOT EXISTS Comments(
25`id` INT NOT NULL AUTO_INCREMENT,
26`userId` INT NOT NULL,
27`postId` INT NOT NULL,
28`creation_date` datetime NOT NULL,
29`text` VARCHAR(62) NOT NULL,
30PRIMARY KEY(`id`),
31FOREIGN KEY(`userId`) REFERENCES Users(`id`)
32ON DELETE CASCADE,
33FOREIGN KEY(`postId`) REFERENCES Tweet(`id`)
34ON DELETE CASCADE,
35UNIQUE INDEX `id_UNIQUE` (`id` ASC)
36) ENGINE = InnoDB;
37
38
39CREATE TABLE IF NOT EXISTS Messages(
40`id` INT NOT NULL AUTO_INCREMENT,
41`userIdSend` INT NOT NULL,
42`userIdGet` INT NOT NULL,
43`text` VARCHAR(62) NOT NULL,
44`creation_date` datetime NOT NULL,
45`read` boolean NOT NULL DEFAULT false,
46PRIMARY KEY(`id`),
47FOREIGN KEY(`userIdSend`) REFERENCES Users(`id`)
48ON DELETE CASCADE,
49FOREIGN KEY(`userIdGet`) REFERENCES Tweet(`id`)
50ON DELETE CASCADE,
51UNIQUE INDEX `id_UNIQUE` (`id` ASC)
52) ENGINE = InnoDB;