· 8 years ago · Nov 16, 2017, 02:44 PM
1-- MySQL Workbench Forward Engineering
2
3SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
4SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
5SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
6
7-- -----------------------------------------------------
8-- Schema inver
9-- -----------------------------------------------------
10
11-- -----------------------------------------------------
12-- Schema inver
13-- -----------------------------------------------------
14CREATE SCHEMA IF NOT EXISTS `inver` DEFAULT CHARACTER SET utf8 ;
15USE `inver` ;
16
17-- -----------------------------------------------------
18-- Table `inver`.`users`
19-- -----------------------------------------------------
20CREATE TABLE IF NOT EXISTS `inver`.`users` (
21 `id_user` INT(11) NOT NULL AUTO_INCREMENT,
22 `name` VARCHAR(45) NULL DEFAULT NULL,
23 `surname` VARCHAR(45) NULL DEFAULT NULL,
24 `email` VARCHAR(45) NULL DEFAULT NULL,
25 `password` VARCHAR(230) NULL DEFAULT NULL,
26 `role` VARCHAR(45) NULL DEFAULT NULL,
27 PRIMARY KEY (`id_user`))
28ENGINE = InnoDB
29DEFAULT CHARACTER SET = utf8;
30
31
32-- -----------------------------------------------------
33-- Table `inver`.`greenhouse`
34-- -----------------------------------------------------
35CREATE TABLE IF NOT EXISTS `inver`.`greenhouse` (
36 `fiwareServicePath` VARCHAR(45) NOT NULL,
37 `metros` VARCHAR(45) NULL,
38 `lugar` VARCHAR(45) NULL,
39 `users_id_user` INT(11) NOT NULL,
40 INDEX `fk_greenhouse1_users1_idx` (`users_id_user` ASC),
41 PRIMARY KEY (`fiwareServicePath`),
42 CONSTRAINT `fk_greenhouse1_users1`
43 FOREIGN KEY (`users_id_user`)
44 REFERENCES `inver`.`users` (`id_user`)
45 ON DELETE NO ACTION
46 ON UPDATE NO ACTION)
47ENGINE = InnoDB;
48
49
50-- -----------------------------------------------------
51-- Table `inver`.`sensors`
52-- -----------------------------------------------------
53CREATE TABLE IF NOT EXISTS `inver`.`sensors` (
54 `entityId` VARCHAR(45) NOT NULL,
55 `sensor_name` VARCHAR(45) CHARACTER SET 'utf8' COLLATE 'utf8_spanish_ci' NULL DEFAULT NULL,
56 PRIMARY KEY (`entityId`))
57ENGINE = InnoDB
58DEFAULT CHARACTER SET = utf8
59COLLATE = utf8_spanish_ci;
60
61
62-- -----------------------------------------------------
63-- Table `inver`.`sensors_has_greenhouse`
64-- -----------------------------------------------------
65CREATE TABLE IF NOT EXISTS `inver`.`sensors_has_greenhouse` (
66 `sensors_entityId` VARCHAR(45) NOT NULL,
67 `greenhouse_fiwareServicePath` VARCHAR(45) NOT NULL,
68 PRIMARY KEY (`greenhouse_fiwareServicePath`, `sensors_entityId`),
69 INDEX `fk_sensors_has_greenhouse_greenhouse1_idx` (`greenhouse_fiwareServicePath` ASC),
70 INDEX `fk_sensors_has_greenhouse_sensors1_idx` (`sensors_entityId` ASC),
71 CONSTRAINT `fk_sensors_has_greenhouse_sensors1`
72 FOREIGN KEY (`sensors_entityId`)
73 REFERENCES `inver`.`sensors` (`entityId`)
74 ON DELETE NO ACTION
75 ON UPDATE NO ACTION,
76 CONSTRAINT `fk_sensors_has_greenhouse_greenhouse1`
77 FOREIGN KEY (`greenhouse_fiwareServicePath`)
78 REFERENCES `inver`.`greenhouse` (`fiwareServicePath`)
79 ON DELETE NO ACTION
80 ON UPDATE NO ACTION)
81ENGINE = InnoDB
82DEFAULT CHARACTER SET = utf8
83COLLATE = utf8_spanish_ci;
84
85
86SET SQL_MODE=@OLD_SQL_MODE;
87SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
88SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
89
90Executing SQL script in server
91ERROR: Error 1215: Cannot add foreign key constraint
92SQL Code:
93 -- -----------------------------------------------------
94 -- Table `inver`.`sensors_has_greenhouse`
95 -- -----------------------------------------------------
96 CREATE TABLE IF NOT EXISTS `inver`.`sensors_has_greenhouse` (
97 `sensors_entityId` VARCHAR(45) NOT NULL,
98 `greenhouse_fiwareServicePath` VARCHAR(45) NOT NULL,
99 PRIMARY KEY (`greenhouse_fiwareServicePath`, `sensors_entityId`),
100 INDEX `fk_sensors_has_greenhouse_greenhouse1_idx` (`greenhouse_fiwareServicePath` ASC),
101 INDEX `fk_sensors_has_greenhouse_sensors1_idx` (`sensors_entityId` ASC),
102 CONSTRAINT `fk_sensors_has_greenhouse_sensors1`
103 FOREIGN KEY (`sensors_entityId`)
104 REFERENCES `inver`.`sensors` (`entityId`)
105 ON DELETE NO ACTION
106 ON UPDATE NO ACTION,
107 CONSTRAINT `fk_sensors_has_greenhouse_greenhouse1`
108 FOREIGN KEY (`greenhouse_fiwareServicePath`)
109 REFERENCES `inver`.`greenhouse` (`fiwareServicePath`)
110 ON DELETE NO ACTION
111 ON UPDATE NO ACTION)
112 ENGINE = InnoDB
113 DEFAULT CHARACTER SET = utf8
114 COLLATE = utf8_spanish_ci
115
116SQL script execution finished: statements: 8 succeeded, 1 failed
117
118Fetching back view definitions in final form.
119Nothing to fetch
120
121COLLATE = utf8_spanish_ci
122
123CREATE DATABASE IF NOT EXISTS `ra33` DEFAULT CHARACTER SET latin1 COLLATE
124latin1_swedish_ci;
125USE `ra33`;
126
127CREATE TABLE `empresas` (
128 `id` int(10) UNSIGNED NOT NULL,
129 `nombre` varchar(50) NOT NULL,
130 `direccion` varchar(50) NOT NULL,
131 `telefono` varchar(50) NOT NULL,
132 `fundacion` date NOT NULL,
133 `tipo` varchar(50) NOT NULL
134) ENGINE=InnoDB DEFAULT CHARSET=latin1;
135
136CREATE TABLE `empresa_producto` (
137 `id_empresa` int(10) UNSIGNED DEFAULT NULL,
138 `id_producto` int(10) UNSIGNED DEFAULT NULL
139) ENGINE=InnoDB DEFAULT CHARSET=latin1;
140
141CREATE TABLE `productos` (
142 `id` int(10) UNSIGNED NOT NULL,
143 `nombre` varchar(50) NOT NULL,
144 `precio_base` float NOT NULL DEFAULT '0'
145) ENGINE=InnoDB DEFAULT CHARSET=latin1;