· 7 years ago · Jan 13, 2019, 03:34 AM
1SQL Script Error 1005 Can't create table
2SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
3SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
4SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
5
6CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci ;
7CREATE SCHEMA IF NOT EXISTS `Final Project` ;
8USE `mydb` ;
9USE `Final Project` ;
10
11-- -----------------------------------------------------
12-- Table `Final Project`.`EMPLOYEE`
13-- -----------------------------------------------------
14DROP TABLE IF EXISTS `Final Project`.`EMPLOYEE` ;
15
16CREATE TABLE IF NOT EXISTS `Final Project`.`EMPLOYEE` (
17`EMP_ID` INT NOT NULL ,
18`EMP_LNAME` VARCHAR(15) NULL ,
19`EMP_FNAME` VARCHAR(15) NULL ,
20PRIMARY KEY (`EMP_ID`) )
21ENGINE = InnoDB;
22
23
24-- -----------------------------------------------------
25-- Table `Final Project`.`SERVICES`
26-- -----------------------------------------------------
27DROP TABLE IF EXISTS `Final Project`.`SERVICES` ;
28
29CREATE TABLE IF NOT EXISTS `Final Project`.`SERVICES` (
30`SERV_ID` INT NOT NULL ,
31`SERV_NAME` VARCHAR(15) NULL ,
32`SERV_COST` DECIMAL(3,2) NULL ,
33PRIMARY KEY (`SERV_ID`) )
34ENGINE = InnoDB;
35
36
37-- -----------------------------------------------------
38-- Table `Final Project`.`CUSTOMER`
39-- -----------------------------------------------------
40DROP TABLE IF EXISTS `Final Project`.`CUSTOMER` ;
41
42CREATE TABLE IF NOT EXISTS `Final Project`.`CUSTOMER` (
43
44
45`CUS_ID` INT NOT NULL ,
46 `CUS_LNAME` VARCHAR(15) NULL ,
47 `CUS_FNAME` VARCHAR(15) NULL ,
48 `CUS_PHONE` DECIMAL(10,0) NULL ,
49 PRIMARY KEY (`CUS_ID`) )
50
51ENGINE = InnoDB;
52
53
54-- -----------------------------------------------------
55-- Table `Final Project`.`JOB`
56-- -----------------------------------------------------
57DROP TABLE IF EXISTS `Final Project`.`JOB` ;
58
59CREATE TABLE IF NOT EXISTS `Final Project`.`JOB` (
60 `JOB_ID` INT NOT NULL ,
61 `CUSTOMER_CUS_ID` INT NOT NULL ,
62 `SERVICES_SERV_ID` INT NOT NULL ,
63 `EMPLOYEE_EMP_ID` INT NOT NULL ,
64 PRIMARY KEY (`JOB_ID`) ,
65 INDEX `fk_JOB_CUSTOMER1_idx` (`CUSTOMER_CUS_ID` ASC) ,
66 INDEX `fk_JOB_SERVICES1_idx` (`SERVICES_SERV_ID` ASC) ,
67 INDEX `fk_JOB_EMPLOYEE1_idx` (`EMPLOYEE_EMP_ID` ASC) ,
68 CONSTRAINT `fk_JOB_CUSTOMER1`
69 FOREIGN KEY (`CUSTOMER_CUS_ID` )
70 REFERENCES `Final Project`.`CUSTOMER` (`CUS_ID` )
71 ON DELETE NO ACTION
72 ON UPDATE NO ACTION,
73 CONSTRAINT `fk_JOB_SERVICES1`
74 FOREIGN KEY (`SERVICES_SERV_ID` )
75 REFERENCES `Final Project`.`SERVICES` (`SERV_ID` )
76 ON DELETE NO ACTION
77 ON UPDATE NO ACTION,
78 CONSTRAINT `fk_JOB_EMPLOYEE1`
79 FOREIGN KEY (`EMPLOYEE_EMP_ID` )
80 REFERENCES `Final Project`.`EMPLOYEE` (`EMP_ID` )
81 ON DELETE NO ACTION
82 ON UPDATE NO ACTION)
83ENGINE = InnoDB;
84
85
86-- -----------------------------------------------------
87-- Table `Final Project`.`INVOICE`
88-- -----------------------------------------------------
89DROP TABLE IF EXISTS `Final Project`.`INVOICE` ;
90
91CREATE TABLE IF NOT EXISTS `Final Project`.`INVOICE` (
92 `INV_NUM` DECIMAL(10,0) NOT NULL ,
93 `INV_DATE` DATE NULL ,
94 `INV_TOTAL` DECIMAL(3,2) NULL ,
95 `CUSTOMER_CUS_ID` INT NOT NULL ,
96 `JOB_JOB_ID` INT NOT NULL ,
97 `JOB_EMPLOYEE_EMP_ID` INT NOT NULL ,
98 PRIMARY KEY (`INV_NUM`) ,
99 INDEX `fk_INVOICE_CUSTOMER_idx` (`CUSTOMER_CUS_ID` ASC) ,
100 INDEX `fk_INVOICE_JOB1_idx` (`JOB_JOB_ID` ASC, `JOB_EMPLOYEE_EMP_ID` ASC) ,
101 CONSTRAINT `fk_INVOICE_CUSTOMER`
102 FOREIGN KEY (`CUSTOMER_CUS_ID` )
103 REFERENCES `Final Project`.`CUSTOMER` (`CUS_ID` )
104 ON DELETE NO ACTION
105 ON UPDATE NO ACTION,
106 CONSTRAINT `fk_INVOICE_JOB1`
107 FOREIGN KEY (`JOB_JOB_ID` )
108 REFERENCES `Final Project`.`JOB` (`JOB_ID` )
109 ON DELETE NO ACTION
110 ON UPDATE NO ACTION)
111ENGINE = InnoDB;
112
113
114
115SET SQL_MODE=@OLD_SQL_MODE;
116SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
117SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
118
119-- -----------------------------------------------------
120-- Data for table `Final Project`.`EMPLOYEE`
121-- -----------------------------------------------------
122START TRANSACTION;
123USE `Final Project`;
124INSERT INTO `Final Project`.`EMPLOYEE` (`EMP_ID`, `EMP_LNAME`, `EMP_FNAME`) VALUES (101, 'Vara', 'Tiffany');
125INSERT INTO `Final Project`.`EMPLOYEE` (`EMP_ID`, `EMP_LNAME`, `EMP_FNAME`) VALUES (102, 'Chadburn', 'Leah');
126INSERT INTO `Final Project`.`EMPLOYEE` (`EMP_ID`, `EMP_LNAME`, `EMP_FNAME`) VALUES (103, 'Boyd', 'Neil');
127INSERT INTO `Final Project`.`EMPLOYEE` (`EMP_ID`, `EMP_LNAME`, `EMP_FNAME`) VALUES (104, 'Goodfellow', 'Eric');
128INSERT INTO `Final Project`.`EMPLOYEE` (`EMP_ID`, `EMP_LNAME`, `EMP_FNAME`) VALUES (105, 'Weedon', 'Hugh');
129
130COMMIT;
131
132-- -----------------------------------------------------
133-- Data for table `Final Project`.`SERVICES`
134-- -----------------------------------------------------
135START TRANSACTION;
136USE `Final Project`;
137INSERT INTO `Final Project`.`SERVICES` (`SERV_ID`, `SERV_NAME`, `SERV_COST`) VALUES (9001, 'Mowing', 45.00);
138INSERT INTO `Final Project`.`SERVICES` (`SERV_ID`, `SERV_NAME`, `SERV_COST`) VALUES (9002, 'Edging', 15.00);
139INSERT INTO `Final Project`.`SERVICES` (`SERV_ID`, `SERV_NAME`, `SERV_COST`) VALUES (9003, 'Trimming', 20.00);
140INSERT INTO `Final Project`.`SERVICES` (`SERV_ID`, `SERV_NAME`, `SERV_COST`) VALUES (9004, 'Fertilization', 35.00);
141INSERT INTO `Final Project`.`SERVICES` (`SERV_ID`, `SERV_NAME`, `SERV_COST`) VALUES (9005, 'Raking', 25.00);
142
143COMMIT;
144
145-- -----------------------------------------------------
146-- Data for table `Final Project`.`CUSTOMER`
147-- -----------------------------------------------------
148START TRANSACTION;
149USE `Final Project`;
150INSERT INTO `Final Project`.`CUSTOMER` (`CUS_ID`, `CUS_LNAME`, `CUS_FNAME`, `CUS_PHONE`) VALUES (5001, 'Baxley', 'Matthew', 907-555-4313);
151INSERT INTO `Final Project`.`CUSTOMER` (`CUS_ID`, `CUS_LNAME`, `CUS_FNAME`, `CUS_PHONE`) VALUES (5002, 'Muller', 'Frank', 907-555-9384);
152INSERT INTO `Final Project`.`CUSTOMER` (`CUS_ID`, `CUS_LNAME`, `CUS_FNAME`, `CUS_PHONE`) VALUES (5003, 'Burt ', 'Brandon', 907-555-0218);
153INSERT INTO `Final Project`.`CUSTOMER` (`CUS_ID`, `CUS_LNAME`, `CUS_FNAME`, `CUS_PHONE`) VALUES (5004, 'Laster', 'Mike', 907-555-4974);
154INSERT INTO `Final Project`.`CUSTOMER` (`CUS_ID`, `CUS_LNAME`, `CUS_FNAME`, `CUS_PHONE`) VALUES (5005, 'Watts', 'Glenda', 907-555-3006);
155INSERT INTO `Final Project`.`CUSTOMER` (`CUS_ID`, `CUS_LNAME`, `CUS_FNAME`, `CUS_PHONE`) VALUES (5006, 'Huber', 'Jesse', 907-555-1767);
156INSERT INTO `Final Project`.`CUSTOMER` (`CUS_ID`, `CUS_LNAME`, `CUS_FNAME`, `CUS_PHONE`) VALUES (5007, 'Vargo', 'Jerry', 907-555-7943);
157INSERT INTO `Final Project`.`CUSTOMER` (`CUS_ID`, `CUS_LNAME`, `CUS_FNAME`, `CUS_PHONE`) VALUES (5008, 'Clemons', 'Renee', 907-555-2285);
158INSERT INTO `Final Project`.`CUSTOMER` (`CUS_ID`, `CUS_LNAME`, `CUS_FNAME`, `CUS_PHONE`) VALUES (5009, 'Ruiz', 'Anita', 907-555-9751);
159INSERT INTO `Final Project`.`CUSTOMER` (`CUS_ID`, `CUS_LNAME`, `CUS_FNAME`, `CUS_PHONE`) VALUES (5010, 'Cornel', 'Bill', 907-555-0025);
160
161COMMIT;
162
163-- -----------------------------------------------------
164-- Data for table `Final Project`.`JOB`
165-- -----------------------------------------------------
166START TRANSACTION;
167USE `Final Project`;
168INSERT INTO `Final Project`.`JOB` (`JOB_ID`, `CUSTOMER_CUS_ID`, `SERVICES_SERV_ID`, `EMPLOYEE_EMP_ID`) VALUES (201, 5004, '9001, 9004', 105);
169INSERT INTO `Final Project`.`JOB` (`JOB_ID`, `CUSTOMER_CUS_ID`, `SERVICES_SERV_ID`, `EMPLOYEE_EMP_ID`) VALUES (202, 5010, '9005', 101);
170INSERT INTO `Final Project`.`JOB` (`JOB_ID`, `CUSTOMER_CUS_ID`, `SERVICES_SERV_ID`, `EMPLOYEE_EMP_ID`) VALUES (203, 5005, '9001, 9002, 9003', 104);
171INSERT INTO `Final Project`.`JOB` (`JOB_ID`, `CUSTOMER_CUS_ID`, `SERVICES_SERV_ID`, `EMPLOYEE_EMP_ID`) VALUES (204, 5002, '9001, 9002', 102);
172INSERT INTO `Final Project`.`JOB` (`JOB_ID`, `CUSTOMER_CUS_ID`, `SERVICES_SERV_ID`, `EMPLOYEE_EMP_ID`) VALUES (205, 5003, '9001, 9002', 105);
173INSERT INTO `Final Project`.`JOB` (`JOB_ID`, `CUSTOMER_CUS_ID`, `SERVICES_SERV_ID`, `EMPLOYEE_EMP_ID`) VALUES (206, 5008, '9005', 103);
174INSERT INTO `Final Project`.`JOB` (`JOB_ID`, `CUSTOMER_CUS_ID`, `SERVICES_SERV_ID`, `EMPLOYEE_EMP_ID`) VALUES (207, 5009, '9001, 9003', 104);
175INSERT INTO `Final Project`.`JOB` (`JOB_ID`, `CUSTOMER_CUS_ID`, `SERVICES_SERV_ID`, `EMPLOYEE_EMP_ID`) VALUES (208, 5001, '9004', 101);
176INSERT INTO `Final Project`.`JOB` (`JOB_ID`, `CUSTOMER_CUS_ID`, `SERVICES_SERV_ID`, `EMPLOYEE_EMP_ID`) VALUES (209, 5007, '9001, 9002, 9003, 9004', 105);
177INSERT INTO `Final Project`.`JOB` (`JOB_ID`, `CUSTOMER_CUS_ID`, `SERVICES_SERV_ID`, `EMPLOYEE_EMP_ID`) VALUES (210, 5006, '9001, 9002, 9003', 105);
178INSERT INTO `Final Project`.`JOB` (`JOB_ID`, `CUSTOMER_CUS_ID`, `SERVICES_SERV_ID`, `EMPLOYEE_EMP_ID`) VALUES (211, 5004, '9001, 9002, 9003', 102);
179INSERT INTO `Final Project`.`JOB` (`JOB_ID`, `CUSTOMER_CUS_ID`, `SERVICES_SERV_ID`, `EMPLOYEE_EMP_ID`) VALUES (212, 5010, '9001, 9002', 103);
180INSERT INTO `Final Project`.`JOB` (`JOB_ID`, `CUSTOMER_CUS_ID`, `SERVICES_SERV_ID`, `EMPLOYEE_EMP_ID`) VALUES (213, 5005, '9004', 102);
181INSERT INTO `Final Project`.`JOB` (`JOB_ID`, `CUSTOMER_CUS_ID`, `SERVICES_SERV_ID`, `EMPLOYEE_EMP_ID`) VALUES (214, 5008, '9001, 9002, 9003, 9004', 104);
182INSERT INTO `Final Project`.`JOB` (`JOB_ID`, `CUSTOMER_CUS_ID`, `SERVICES_SERV_ID`, `EMPLOYEE_EMP_ID`) VALUES (215, 5001, 9001, 103);
183INSERT INTO `Final Project`.`JOB` (`JOB_ID`, `CUSTOMER_CUS_ID`, `SERVICES_SERV_ID`, `EMPLOYEE_EMP_ID`) VALUES (216, 5002, 9004, 101);
184
185COMMIT;
186
187-- -----------------------------------------------------
188-- Data for table `Final Project`.`INVOICE`
189-- -----------------------------------------------------
190START TRANSACTION;
191USE `Final Project`;
192INSERT INTO `Final Project`.`INVOICE` (`INV_NUM`, `INV_DATE`, `INV_TOTAL`, `CUSTOMER_CUS_ID`, `JOB_JOB_ID`, `JOB_EMPLOYEE_EMP_ID`) VALUES (100401, '20120402', 80.00, 5004, 201, 105);
193INSERT INTO `Final Project`.`INVOICE` (`INV_NUM`, `INV_DATE`, `INV_TOTAL`, `CUSTOMER_CUS_ID`, `JOB_JOB_ID`, `JOB_EMPLOYEE_EMP_ID`) VALUES (100402, '20120405', 25.00, 5010, 202, 101);
194INSERT INTO `Final Project`.`INVOICE` (`INV_NUM`, `INV_DATE`, `INV_TOTAL`, `CUSTOMER_CUS_ID`, `JOB_JOB_ID`, `JOB_EMPLOYEE_EMP_ID`) VALUES (100403, '20120405', 80.00, 5005, 203, 104);
195INSERT INTO `Final Project`.`INVOICE` (`INV_NUM`, `INV_DATE`, `INV_TOTAL`, `CUSTOMER_CUS_ID`, `JOB_JOB_ID`, `JOB_EMPLOYEE_EMP_ID`) VALUES (100404, '20120406', 60.00, 5002, 204, 102);
196INSERT INTO `Final Project`.`INVOICE` (`INV_NUM`, `INV_DATE`, `INV_TOTAL`, `CUSTOMER_CUS_ID`, `JOB_JOB_ID`, `JOB_EMPLOYEE_EMP_ID`) VALUES (100405, '20120407', 65.00, 5003, 205, 105);
197INSERT INTO `Final Project`.`INVOICE` (`INV_NUM`, `INV_DATE`, `INV_TOTAL`, `CUSTOMER_CUS_ID`, `JOB_JOB_ID`, `JOB_EMPLOYEE_EMP_ID`) VALUES (100406, '20120407', 25.00, 5008, 206, 103);
198INSERT INTO `Final Project`.`INVOICE` (`INV_NUM`, `INV_DATE`, `INV_TOTAL`, `CUSTOMER_CUS_ID`, `JOB_JOB_ID`, `JOB_EMPLOYEE_EMP_ID`) VALUES (100407, '20120408', 65.00, 5009, 207, 104);
199INSERT INTO `Final Project`.`INVOICE` (`INV_NUM`, `INV_DATE`, `INV_TOTAL`, `CUSTOMER_CUS_ID`, `JOB_JOB_ID`, `JOB_EMPLOYEE_EMP_ID`) VALUES (100408, '20120408', 35.00, 5001, 208, 101);
200INSERT INTO `Final Project`.`INVOICE` (`INV_NUM`, `INV_DATE`, `INV_TOTAL`, `CUSTOMER_CUS_ID`, `JOB_JOB_ID`, `JOB_EMPLOYEE_EMP_ID`) VALUES (100409, '20120408', 115.00, 5007, 209, 105);
201INSERT INTO `Final Project`.`INVOICE` (`INV_NUM`, `INV_DATE`, `INV_TOTAL`, `CUSTOMER_CUS_ID`, `JOB_JOB_ID`, `JOB_EMPLOYEE_EMP_ID`) VALUES (100410, '20120409', 80.00, 5006, 210, 105);
202INSERT INTO `Final Project`.`INVOICE` (`INV_NUM`, `INV_DATE`, `INV_TOTAL`, `CUSTOMER_CUS_ID`, `JOB_JOB_ID`, `JOB_EMPLOYEE_EMP_ID`) VALUES (100411, '20120410', 80.00, 5004, 211, 102);
203INSERT INTO `Final Project`.`INVOICE` (`INV_NUM`, `INV_DATE`, `INV_TOTAL`, `CUSTOMER_CUS_ID`, `JOB_JOB_ID`, `JOB_EMPLOYEE_EMP_ID`) VALUES (100412, '20120411', 60.00, 5010, 212, 103);
204INSERT INTO `Final Project`.`INVOICE` (`INV_NUM`, `INV_DATE`, `INV_TOTAL`, `CUSTOMER_CUS_ID`, `JOB_JOB_ID`, `JOB_EMPLOYEE_EMP_ID`) VALUES (100413, '20120411', 35.00, 5005, 213, 102);
205INSERT INTO `Final Project`.`INVOICE` (`INV_NUM`, `INV_DATE`, `INV_TOTAL`, `CUSTOMER_CUS_ID`, `JOB_JOB_ID`, `JOB_EMPLOYEE_EMP_ID`) VALUES (100414, '20120415', 115.00, 5008, 214, 104);
206INSERT INTO `Final Project`.`INVOICE` (`INV_NUM`, `INV_DATE`, `INV_TOTAL`, `CUSTOMER_CUS_ID`, `JOB_JOB_ID`, `JOB_EMPLOYEE_EMP_ID`) VALUES (100415, '20120419', 45.00, 5001, 215, 103);
207INSERT INTO `Final Project`.`INVOICE` (`INV_NUM`, `INV_DATE`, `INV_TOTAL`, `CUSTOMER_CUS_ID`, `JOB_JOB_ID`, `JOB_EMPLOYEE_EMP_ID`) VALUES (100416, '20120419', 35.00, 5002, 216, 101);
208
209COMMIT;'