EcgddMfw

1DROP DATABASE IF EXISTS TP2_CERVO_MARIABELEN;
2CREATE DATABASE TP2_CERVO_MARIABELEN;
3USE TP2_CERVO_MARIABELEN;
4
5CREATE TABLE intereses (
6	ID_INTERES TINYINT(2) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
7	INTERES VARCHAR(50) NOT NULL UNIQUE
8) ENGINE = innoDB;
9
10CREATE TABLE usuarios (
11	ID_USUARIO INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
12	NOMBRE VARCHAR(75),
13	APELLIDO VARCHAR(75),
14	EMAIL VARCHAR(255) NOT NULL UNIQUE,
15	CLAVE VARCHAR(255) NOT NULL,
16	FOTO VARCHAR(40) DEFAULT 'default.png',
17	ALTA DATETIME NOT NULL,
18	UBICACION VARCHAR(255),
19	EDAD TINYINT(2) UNSIGNED NOT NULL,
20
21	FKINTERES TINYINT(2) UNSIGNED,
22
23	FOREIGN KEY(FKINTERES) REFERENCES intereses(ID_INTERES) ON DELETE SET NULL ON UPDATE CASCADE
24) ENGINE = innoDB;
25
26CREATE TABLE categorias (
27	ID_CATEGORIA TINYINT(1) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
28	CATEGORIA VARCHAR(20) NOT NULL UNIQUE
29) ENGINE = innoDB;
30
31CREATE TABLE publicaciones (
32	ID_PUBL INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
33	FOTO VARCHAR(40) DEFAULT 'default.png',
34	VOTOS INT NOT NULL,
35	TITULO VARCHAR(100) NOT NULL,
36	UBICACION VARCHAR(255) NOT NULL,
37	ALTA DATETIME NOT NULL,
38	
39	FKCATEGORIA TINYINT(1) UNSIGNED,
40  
41	FOREIGN KEY(FKCATEGORIA) REFERENCES categorias(ID_CATEGORIA) ON DELETE RESTRICT ON UPDATE CASCADE
42) ENGINE = innoDB;
43
44CREATE TABLE usuario_comenta_publ (
45	ID_COMENTARIO INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
46	FKUSUARIO INT UNSIGNED,
47	FKPUBL INT UNSIGNED,
48	FECHA DATETIME NOT NULL,
49	COMENTARIO VARCHAR(255) NOT NULL,
50
51	FOREIGN KEY(FKUSUARIO) REFERENCES usuarios(ID_USUARIO) ON DELETE CASCADE ON UPDATE CASCADE,
52	FOREIGN KEY(FKPUBL) REFERENCES publicaciones(ID_PUBL) ON DELETE CASCADE ON UPDATE CASCADE
53) ENGINE = innoDB;