· 6 years ago · Jun 29, 2019, 11:32 PM
1Executing SQL script in server
2ERROR: Error 1005: Can't create table `books`.`books` (errno: 150 "Foreign key constraint is incorrectly formed")
3SQL Code:
4 -- -----------------------------------------------------
5 -- Table `books`.`books`
6 -- -----------------------------------------------------
7 CREATE TABLE IF NOT EXISTS `books`.`books` (
8 `bookID` INT(11) NOT NULL AUTO_INCREMENT,
9 `name` VARCHAR(255) NULL,
10 `price` DECIMAL(10,2) NULL,
11 `book_prices_book_priceID` INT(11) NOT NULL,
12 `book_types_book_typeID` INT(11) NOT NULL,
13 `transactions_transactionID` INT(11) NOT NULL,
14 `transactions_transaction_types_transaction_typeID` INT(11) NOT NULL,
15 `ISBN` VARCHAR(13) NULL,
16 PRIMARY KEY (`bookID`),
17 INDEX `fk_books_book_prices1_idx` (`book_prices_book_priceID` ASC),
18 INDEX `fk_books_transactions1_idx` (`transactions_transactionID` ASC, `transactions_transaction_types_transaction_typeID` ASC),
19 CONSTRAINT `fk_books_book_prices1`
20 FOREIGN KEY (`book_prices_book_priceID`)
21 REFERENCES `books`.`book_prices` (`book_priceID`)
22 ON DELETE NO ACTION
23 ON UPDATE NO ACTION,
24 CONSTRAINT `fk_books_transactions1`
25 FOREIGN KEY (`transactions_transactionID` , `transactions_transaction_types_transaction_typeID`)
26 REFERENCES `books`.`transactions` (`transactionID` , `transaction_types_transaction_typeID`)
27 ON DELETE NO ACTION
28 ON UPDATE NO ACTION)
29 ENGINE = InnoDB
30
31SQL script execution finished: statements: 8 succeeded, 1 failed
32
33Fetching back view definitions in final form.
34Nothing to fetch
35
36-- MySQL Workbench Forward Engineering
37
38SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
39SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
40SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
41
42-- -----------------------------------------------------
43-- Schema books
44-- -----------------------------------------------------
45
46-- -----------------------------------------------------
47-- Schema books
48-- -----------------------------------------------------
49CREATE SCHEMA IF NOT EXISTS `books` DEFAULT CHARACTER SET utf8 ;
50USE `books` ;
51
52-- -----------------------------------------------------
53-- Table `books`.`book_prices`
54-- -----------------------------------------------------
55CREATE TABLE IF NOT EXISTS `books`.`book_prices` (
56 `book_priceID` INT(11) NOT NULL AUTO_INCREMENT,
57 `bookID` INT(11) NULL,
58 `price` DECIMAL(10,2) NULL,
59 `currency` CHAR(2) NULL,
60 `date_start` DATETIME NULL,
61 `date_end` DATETIME NULL,
62 PRIMARY KEY (`book_priceID`))
63ENGINE = InnoDB;
64
65
66-- -----------------------------------------------------
67-- Table `books`.`transaction_types`
68-- -----------------------------------------------------
69CREATE TABLE IF NOT EXISTS `books`.`transaction_types` (
70 `transaction_typeID` INT(11) NOT NULL AUTO_INCREMENT,
71 `transactionID` INT(11) NULL,
72 PRIMARY KEY (`transaction_typeID`))
73ENGINE = InnoDB;
74
75
76-- -----------------------------------------------------
77-- Table `books`.`transactions`
78-- -----------------------------------------------------
79CREATE TABLE IF NOT EXISTS `books`.`transactions` (
80 `transactionID` INT(11) NOT NULL AUTO_INCREMENT,
81 `transaction_types_transaction_typeID` INT(11) NOT NULL,
82 `date` DATETIME NULL,
83 PRIMARY KEY (`transactionID`),
84 INDEX `fk_transactions_transaction_types1_idx` (`transaction_types_transaction_typeID` ASC),
85 CONSTRAINT `fk_transactions_transaction_types1`
86 FOREIGN KEY (`transaction_types_transaction_typeID`)
87 REFERENCES `books`.`transaction_types` (`transaction_typeID`)
88 ON DELETE NO ACTION
89 ON UPDATE NO ACTION)
90ENGINE = InnoDB;
91
92
93-- -----------------------------------------------------
94-- Table `books`.`books`
95-- -----------------------------------------------------
96CREATE TABLE IF NOT EXISTS `books`.`books` (
97 `bookID` INT(11) NOT NULL AUTO_INCREMENT,
98 `name` VARCHAR(255) NULL,
99 `price` DECIMAL(10,2) NULL,
100 `book_prices_book_priceID` INT(11) NOT NULL,
101 `book_types_book_typeID` INT(11) NOT NULL,
102 `transactions_transactionID` INT(11) NOT NULL,
103 `transactions_transaction_types_transaction_typeID` INT(11) NOT NULL,
104 `ISBN` VARCHAR(13) NULL,
105 PRIMARY KEY (`bookID`),
106 INDEX `fk_books_book_prices1_idx` (`book_prices_book_priceID` ASC),
107 INDEX `fk_books_transactions1_idx` (`transactions_transactionID` ASC, `transactions_transaction_types_transaction_typeID` ASC),
108 CONSTRAINT `fk_books_book_prices1`
109 FOREIGN KEY (`book_prices_book_priceID`)
110 REFERENCES `books`.`book_prices` (`book_priceID`)
111 ON DELETE NO ACTION
112 ON UPDATE NO ACTION,
113 CONSTRAINT `fk_books_transactions1`
114 FOREIGN KEY (`transactions_transactionID` , `transactions_transaction_types_transaction_typeID`)
115 REFERENCES `books`.`transactions` (`transactionID` , `transaction_types_transaction_typeID`)
116 ON DELETE NO ACTION
117 ON UPDATE NO ACTION)
118ENGINE = InnoDB;
119
120
121-- -----------------------------------------------------
122-- Table `books`.`batch_transaction`
123-- -----------------------------------------------------
124CREATE TABLE IF NOT EXISTS `books`.`batch_transaction` (
125 `transactionID` INT(11) NOT NULL AUTO_INCREMENT,
126 `batchID` INT(11) NOT NULL,
127 `transactions_transactionID` INT(11) NOT NULL,
128 `transactions_transaction_types_transaction_typeID` INT(11) NOT NULL,
129 `date` DATETIME NULL,
130 PRIMARY KEY (`transactionID`),
131 INDEX `fk_batch_transaction_transactions1_idx` (`transactions_transactionID` ASC, `transactions_transaction_types_transaction_typeID` ASC),
132 CONSTRAINT `fk_batch_transaction_transactions1`
133 FOREIGN KEY (`transactions_transactionID` , `transactions_transaction_types_transaction_typeID`)
134 REFERENCES `books`.`transactions` (`transactionID` , `transaction_types_transaction_typeID`)
135 ON DELETE NO ACTION
136 ON UPDATE NO ACTION)
137ENGINE = InnoDB;
138
139
140-- -----------------------------------------------------
141-- Table `books`.`batches`
142-- -----------------------------------------------------
143CREATE TABLE IF NOT EXISTS `books`.`batches` (
144 `batchID` INT(11) NOT NULL AUTO_INCREMENT,
145 `batch_transaction_transactionID` INT(11) NULL,
146 `book_typeID` INT(11) NOT NULL,
147 `price` DECIMAL(10,2) NULL,
148 `supplierID` INT(11) NULL,
149 PRIMARY KEY (`batchID`),
150 INDEX `fk_batches_batch_transaction1_idx` (`batch_transaction_transactionID` ASC),
151 CONSTRAINT `fk_batches_batch_transaction1`
152 FOREIGN KEY (`batch_transaction_transactionID`)
153 REFERENCES `books`.`batch_transaction` (`transactionID`)
154 ON DELETE NO ACTION
155 ON UPDATE NO ACTION)
156ENGINE = InnoDB;
157
158
159-- -----------------------------------------------------
160-- Table `books`.`book_types`
161-- -----------------------------------------------------
162CREATE TABLE IF NOT EXISTS `books`.`book_types` (
163 `book_typeID` INT(11) NOT NULL AUTO_INCREMENT,
164 `name` VARCHAR(255) NULL,
165 `books_bookID` INT(11) NOT NULL,
166 `books_book_prices_book_priceID` INT(11) NOT NULL,
167 `books_book_types_book_typeID` INT(11) NOT NULL,
168 `batches_batchID` INT(11) NOT NULL,
169 PRIMARY KEY (`book_typeID`),
170 INDEX `fk_product_types_products1_idx` (`books_bookID` ASC, `books_book_prices_book_priceID` ASC, `books_book_types_book_typeID` ASC),
171 INDEX `fk_product_types_batches1_idx` (`batches_batchID` ASC),
172 CONSTRAINT `fk_book_types_books1`
173 FOREIGN KEY (`books_bookID` , `books_book_prices_book_priceID` , `books_book_types_book_typeID`)
174 REFERENCES `books`.`books` (`bookID` , `book_prices_book_priceID` , `book_types_book_typeID`)
175 ON DELETE NO ACTION
176 ON UPDATE NO ACTION,
177 CONSTRAINT `fk_book_types_batches1`
178 FOREIGN KEY (`batches_batchID`)
179 REFERENCES `books`.`batches` (`batchID`)
180 ON DELETE NO ACTION
181 ON UPDATE NO ACTION)
182ENGINE = InnoDB;
183
184
185-- -----------------------------------------------------
186-- Table `books`.`suppliers`
187-- -----------------------------------------------------
188CREATE TABLE IF NOT EXISTS `books`.`suppliers` (
189 `supplierID` INT(11) NOT NULL AUTO_INCREMENT,
190 `batches_batchID` INT(11) NOT NULL,
191 `name` VARCHAR(255) NULL,
192 PRIMARY KEY (`supplierID`),
193 INDEX `fk_suppliers_batches1_idx` (`batches_batchID` ASC),
194 CONSTRAINT `fk_suppliers_batches1`
195 FOREIGN KEY (`batches_batchID`)
196 REFERENCES `books`.`batches` (`batchID`)
197 ON DELETE NO ACTION
198 ON UPDATE NO ACTION)
199ENGINE = InnoDB;
200
201
202-- -----------------------------------------------------
203-- Table `books`.`customer_transaction`
204-- -----------------------------------------------------
205CREATE TABLE IF NOT EXISTS `books`.`customer_transaction` (
206 `transactionID` INT(11) NOT NULL AUTO_INCREMENT,
207 `customerID` INT(11) NULL,
208 `transactions_transactionID` INT(11) NOT NULL,
209 `date` DATETIME NULL,
210 PRIMARY KEY (`transactionID`),
211 INDEX `fk_client_transaction_transactions1_idx` (`transactions_transactionID` ASC),
212 CONSTRAINT `fk_customer_transaction_transactions1`
213 FOREIGN KEY (`transactions_transactionID`)
214 REFERENCES `books`.`transactions` (`transactionID`)
215 ON DELETE NO ACTION
216 ON UPDATE NO ACTION)
217ENGINE = InnoDB;
218
219
220-- -----------------------------------------------------
221-- Table `books`.`customers`
222-- -----------------------------------------------------
223CREATE TABLE IF NOT EXISTS `books`.`customers` (
224 `customerID` INT(11) NOT NULL AUTO_INCREMENT,
225 `books_bookID` INT(11) NULL,
226 `books_book_prices_book_priceID` INT(11) NOT NULL,
227 `books_book_types_book_typeID` INT(11) NOT NULL,
228 `customer_transaction_transactionID` INT(11) NOT NULL,
229 PRIMARY KEY (`customerID`),
230 INDEX `fk_clients_products1_idx` (`books_bookID` ASC, `books_book_prices_book_priceID` ASC, `books_book_types_book_typeID` ASC),
231 INDEX `fk_clients_client_transaction1_idx` (`customer_transaction_transactionID` ASC),
232 CONSTRAINT `fk_customrs_products1`
233 FOREIGN KEY (`books_bookID` , `books_book_prices_book_priceID` , `books_book_types_book_typeID`)
234 REFERENCES `books`.`books` (`bookID` , `book_prices_book_priceID` , `book_types_book_typeID`)
235 ON DELETE NO ACTION
236 ON UPDATE NO ACTION,
237 CONSTRAINT `fk_customers_customer_transaction1`
238 FOREIGN KEY (`customer_transaction_transactionID`)
239 REFERENCES `books`.`customer_transaction` (`transactionID`)
240 ON DELETE NO ACTION
241 ON UPDATE NO ACTION)
242ENGINE = InnoDB;
243
244
245-- -----------------------------------------------------
246-- Table `books`.`discounts`
247-- -----------------------------------------------------
248CREATE TABLE IF NOT EXISTS `books`.`discounts` (
249 `discountID` INT(11) NOT NULL AUTO_INCREMENT,
250 `Name` VARCHAR(255) NOT NULL,
251 `transactions_transactionID` INT(11) NULL,
252 `transactions_transaction_types_transaction_typeID` INT(11) NULL,
253 PRIMARY KEY (`discountID`),
254 INDEX `fk_discounts_transactions1_idx` (`transactions_transactionID` ASC, `transactions_transaction_types_transaction_typeID` ASC),
255 CONSTRAINT `fk_discounts_transactions1`
256 FOREIGN KEY (`transactions_transactionID` , `transactions_transaction_types_transaction_typeID`)
257 REFERENCES `books`.`transactions` (`transactionID` , `transaction_types_transaction_typeID`)
258 ON DELETE NO ACTION
259 ON UPDATE NO ACTION)
260ENGINE = InnoDB;
261
262
263-- -----------------------------------------------------
264-- Table `books`.`discount_types`
265-- -----------------------------------------------------
266CREATE TABLE IF NOT EXISTS `books`.`discount_types` (
267 `discount_typeID` INT(11) NOT NULL AUTO_INCREMENT,
268 `Type` VARCHAR(255) NULL,
269 `discounts_discountID` INT(11) NOT NULL,
270 PRIMARY KEY (`discount_typeID`),
271 INDEX `fk_discount_types_discounts1_idx` (`discounts_discountID` ASC),
272 CONSTRAINT `fk_discount_types_discounts1`
273 FOREIGN KEY (`discounts_discountID`)
274 REFERENCES `books`.`discounts` (`discountID`)
275 ON DELETE NO ACTION
276 ON UPDATE NO ACTION)
277ENGINE = InnoDB;
278
279
280-- -----------------------------------------------------
281-- Table `books`.`storagedistribution`
282-- -----------------------------------------------------
283CREATE TABLE IF NOT EXISTS `books`.`storagedistribution` (
284 `storagedistributionID` INT(11) NOT NULL AUTO_INCREMENT,
285 `Name` VARCHAR(255) NULL,
286 `transactions_transactionID` INT(11) NOT NULL,
287 `transactions_transaction_types_transaction_typeID` INT(11) NOT NULL,
288 PRIMARY KEY (`storagedistributionID`),
289 INDEX `fk_storagedistribution_transactions1_idx` (`transactions_transactionID` ASC, `transactions_transaction_types_transaction_typeID` ASC),
290 CONSTRAINT `fk_storagedistribution_transactions1`
291 FOREIGN KEY (`transactions_transactionID` , `transactions_transaction_types_transaction_typeID`)
292 REFERENCES `books`.`transactions` (`transactionID` , `transaction_types_transaction_typeID`)
293 ON DELETE NO ACTION
294 ON UPDATE NO ACTION)
295ENGINE = InnoDB;
296
297
298SET SQL_MODE=@OLD_SQL_MODE;
299SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
300SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;