NotesOrganizer/tables.sql at master · halcaponey/NotesOrganizer · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
#------------------------------------------------------------
#        Script MySQL.
#------------------------------------------------------------

#------------------------------------------------------------
# Database creation
#------------------------------------------------------------

DROP DATABASE IF EXISTS note_organizer;
CREATE DATABASE note_organizer;
USE note_organizer;


#------------------------------------------------------------
# Table: note
#------------------------------------------------------------

CREATE TABLE note(
        id          int (11) Auto_increment  NOT NULL,
        title       Varchar (150) NOT NULL,
        description Text,
        PRIMARY KEY (id)
)ENGINE=InnoDB;


#------------------------------------------------------------
# Table: categorie
#------------------------------------------------------------

CREATE TABLE categorie(
        id   int (11) Auto_increment  NOT NULL,
        name Varchar (150) NOT NULL,
        id_parent Int,
        PRIMARY KEY (id)
)ENGINE=InnoDB;


#------------------------------------------------------------
# Table: note_categorie
#------------------------------------------------------------

CREATE TABLE note_categorie(
        id_note      Int NOT NULL,
        id_categorie Int NOT NULL,
        PRIMARY KEY (id_note ,id_categorie)
)ENGINE=InnoDB;

#------------------------------------------------------------
# Foreign keys
#------------------------------------------------------------

ALTER TABLE categorie ADD CONSTRAINT FK_categorie_id_parent FOREIGN KEY (id_parent) REFERENCES categorie(id);
ALTER TABLE note_categorie ADD CONSTRAINT FK_note_categorie_id FOREIGN KEY (id_note) REFERENCES note(id);
ALTER TABLE note_categorie ADD CONSTRAINT FK_note_categorie_id_categorie FOREIGN KEY (id_categorie) REFERENCES categorie(id);