DROP TABLE Composer_2;
DROP TABLE Composer;
DROP TABLE Tag;
DROP TABLE Relation;
DROP TABLE Member;
DROP TABLE Way;
DROP TABLE Node;
DROP TABLE Utilisateur;
DROP SEQUENCE idT_increment;

CREATE TABLE Utilisateur (
idU INT,
pseudo CHAR(30),
PRIMARY KEY (idU) );

CREATE TABLE Node (
id INT,
lat NUMBER(18,10),
lon NUMBER(18,10),
idU INT,
visible NUMBER(1),
version NUMBER(2),
changeset NUMBER(10),
timestampNode VARCHAR(20),
PRIMARY KEY (id) );


CREATE TABLE Way (
idW INT,
idU INT,
visibleWay NUMBER(1),
versionWay NUMBER(2),
changesetWay NUMBER(10),
timestampWay VARCHAR(20),
PRIMARY KEY (idW) );



CREATE TABLE Tag (
idT INT,
k VARCHAR(255),
v VARCHAR(255),
id INT, 
idW INT, 
idR INT,
PRIMARY KEY (idT) );


CREATE TABLE Member (
idM INT,
typeMember VARCHAR(20),
role VARCHAR(50),
id INT,
idW INT,
idR INT,
PRIMARY KEY (idM) );


CREATE TABLE Relation (
idR INT,
versionRelation NUMBER(2),
timestampRelation VARCHAR(20),
idU INT,
changesetRelation NUMBER(2),
PRIMARY KEY (idR) );


CREATE TABLE Composer (
idW INT,
id INT NOT NULL,
PRIMARY KEY (idW,
 id) );


CREATE TABLE Composer_2 (
idR INT,
idM INT,
PRIMARY KEY (idM,
 idR) );

CREATE SEQUENCE idT_increment
 START WITH 1
 INCREMENT BY 1
 CACHE 100;
  
  
ALTER TABLE Node ADD CONSTRAINT FK_Node_idU FOREIGN KEY (idU) REFERENCES Utilisateur (idU);
ALTER TABLE Way ADD CONSTRAINT FK_Way_idU FOREIGN KEY (idU) REFERENCES Utilisateur (idU);
ALTER TABLE Tag ADD CONSTRAINT FK_Tag_id FOREIGN KEY (id) REFERENCES Node (id); 
ALTER TABLE Tag ADD CONSTRAINT FK_Tag_idW FOREIGN KEY (idW) REFERENCES Way (idW); 
ALTER TABLE Tag ADD CONSTRAINT FK_Tag_idR FOREIGN KEY (idR) REFERENCES Relation (idR);
ALTER TABLE Member ADD CONSTRAINT FK_Member_id FOREIGN KEY (id) REFERENCES Node (id);
ALTER TABLE Member ADD CONSTRAINT FK_Member_idW FOREIGN KEY (idW) REFERENCES Way (idW);
ALTER TABLE Relation ADD CONSTRAINT FK_Relation_idU FOREIGN KEY (idU) REFERENCES Utilisateur (idU);
ALTER TABLE Composer ADD CONSTRAINT FK_Composer_idW FOREIGN KEY (idW) REFERENCES Way (idW);
ALTER TABLE Composer ADD CONSTRAINT FK_Composer_id FOREIGN KEY (id) REFERENCES Node (id);
ALTER TABLE Composer_2 ADD CONSTRAINT FK_Composer_2_idR FOREIGN KEY (idR) REFERENCES Relation (idR);
ALTER TABLE Composer_2 ADD CONSTRAINT FK_Composer_2_idM FOREIGN KEY (idM) REFERENCES Member (idM);

