WJWvH7E9

1/*Borramos la base de datos si esta ya existe*/
2DROP DATABASE IF EXISTS gameDB;
3
4/*Creamos la base de datos*/
5CREATE DATABASE gameDB;
6USE gameDB;
7CREATE TABLE IF NOT EXISTS usuario(
8id_user int(16)NOT NULL AUTO_INCREMENT,
9username varchar(16)NOT NULL UNIQUE,
10nombre varchar(32)NOT NULL,
11apellido varchar(64)NOT NULL,
12email varchar(64)NOT NULL UNIQUE,
13password varchar(64)NOT NULL,
14fecha_alta DATETIME NOT NULL,
15ultima_conexion DATETIME,
16CONSTRAINT PK_id_user PRIMARY KEY (id_user)
17);
18
19CREATE TABLE IF NOT EXISTS alianza(
20id_alianza int(16)NOT NULL AUTO_INCREMENT,
21nombre varchar(32)NOT NULL UNIQUE,
22id_lider int(16)NOT NULL UNIQUE ,
23CONSTRAINT PK_id_alianza PRIMARY KEY (id_alianza)
24);
25
26CREATE TABLE IF NOT EXISTS user_info(
27id_user int(16)NOT NULL UNIQUE,
28raza varchar(32)NOT NULL,
29id_alianza int(16) default null ,
30FOREIGN KEY (id_alianza) REFERENCES alianza(id_alianza)ON DELETE SET NULL,
31FOREIGN KEY (id_user) REFERENCES usuario(id_user)ON DELETE CASCADE
32);
33
34ALTER TABLE alianza
35ADD CONSTRAINT FK_id_lider
36FOREIGN KEY (id_lider) REFERENCES user_info(id_user)ON DELETE RESTRICT;