· 6 years ago · Mar 15, 2019, 08:14 PM
1LATEST FOREIGN KEY ERROR
2------------------------
3120811 22:40:43 Transaction:
4TRANSACTION 1A4F, ACTIVE 0 sec inserting
5mysql tables in use 1, locked 1
64 lock struct(s), heap size 1248, 2 row lock(s), undo log entries 2
7MySQL thread id 73, OS thread handle 0x10ccc4000, query id 339 localhost root update
8INSERT INTO asset_details (asset_type,make, model, serial_number, os,os_version,memory, hdd, processor,notes)
9 Values( 'Monitor','Apple ','Mac Book Pro 17 inch ','65655453445545','Macintosh','Snow Lion ','5 gb ','1 TB','Intel i 9','This is Paskale New Mac book pro computer.')
10Foreign key constraint fails for table `inventory1`.`asset_details`:
11,
12 CONSTRAINT `asset_tag` FOREIGN KEY (`asset_tag`) REFERENCES `asset` (`asset_tag`) ON DELETE CASCADE ON UPDATE CASCADE
13Trying to add in child table, in index `asset_tag` tuple:
14DATA TUPLE: 2 fields;
15 0: len 4; hex 80000000; asc ;;
16 1: len 4; hex 80000001; asc ;;
17
18But in parent table `inventory1`.`asset`, in index `PRIMARY`,
19the closest match we can find is record:
20PHYSICAL RECORD: n_fields 8; compact format; info bits 0
21 0: len 4; hex 80000007; asc ;;
22 1: len 6; hex 000000001a4f; asc O;;
23 2: len 7; hex c0000001d40110; asc ;;
24 3: SQL NULL;
25 4: len 14; hex 4465636f6d6d697373696f6e6564; asc Decommissioned;;
26 5: SQL NULL;
27 6: len 7; hex 4d6f6e69746f72; asc Monitor;;
28 7: SQL NULL;
29
30SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
31SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
32SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';
33
34DROP SCHEMA IF EXISTS `inventory1` ;
35CREATE SCHEMA IF NOT EXISTS `inventory1` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci ;
36USE `inventory1` ;
37
38-- -----------------------------------------------------
39-- Table `inventory1`.`department`
40-- -----------------------------------------------------
41DROP TABLE IF EXISTS `inventory1`.`department` ;
42
43CREATE TABLE IF NOT EXISTS `inventory1`.`department` (
44 `department_id` INT NOT NULL AUTO_INCREMENT ,
45 `department_name` VARCHAR(45) NOT NULL ,
46 `job_id` VARCHAR(45) NOT NULL ,
47 PRIMARY KEY (`department_id`) )
48ENGINE = InnoDB;
49
50
51-- -----------------------------------------------------
52-- Table `inventory1`.`employee`
53-- -----------------------------------------------------
54DROP TABLE IF EXISTS `inventory1`.`employee` ;
55
56CREATE TABLE IF NOT EXISTS `inventory1`.`employee` (
57 `employee_id` INT NULL AUTO_INCREMENT ,
58 `department_id` INT NOT NULL ,
59 `fname` VARCHAR(45) NULL ,
60 `lname` VARCHAR(45) NULL ,
61 `email` VARCHAR(45) NULL ,
62 `phone_number` VARCHAR(45) NULL ,
63 `hire_date` VARCHAR(45) NULL ,
64 `job_id` VARCHAR(45) NULL ,
65 `manager_id` VARCHAR(45) NULL ,
66 PRIMARY KEY (`employee_id`) ,
67 INDEX `department_id` (`department_id` ASC) ,
68 CONSTRAINT `department_id`
69 FOREIGN KEY (`department_id` )
70 REFERENCES `inventory1`.`department` (`department_id` )
71 ON DELETE CASCADE
72 ON UPDATE CASCADE)
73ENGINE = InnoDB;
74
75
76-- -----------------------------------------------------
77-- Table `inventory1`.`invoice`
78-- -----------------------------------------------------
79DROP TABLE IF EXISTS `inventory1`.`invoice` ;
80
81CREATE TABLE IF NOT EXISTS `inventory1`.`invoice` (
82 `invoice_id` INT NOT NULL AUTO_INCREMENT ,
83 `invoice_number` INT NULL ,
84 `invoice_date` INT UNSIGNED NULL ,
85 `purchase_price` INT UNSIGNED NULL ,
86 `quantity` INT UNSIGNED NULL ,
87 `order_date` INT UNSIGNED NULL ,
88 `vender` INT UNSIGNED NULL ,
89 `warranty_end` DATE NULL ,
90 `notes` VARCHAR(255) NULL ,
91 PRIMARY KEY (`invoice_id`) )
92ENGINE = InnoDB;
93
94
95-- -----------------------------------------------------
96-- Table `inventory1`.`asset`
97-- -----------------------------------------------------
98DROP TABLE IF EXISTS `inventory1`.`asset` ;
99
100CREATE TABLE IF NOT EXISTS `inventory1`.`asset` (
101 `asset_tag` INT NOT NULL ,
102 `invoice_id` INT NULL ,
103 `status` VARCHAR(25) NULL ,
104 `cap_ex` VARCHAR(20) NULL ,
105 `asset_type` VARCHAR(25) NULL ,
106 `invoice_number` INT NULL ,
107 PRIMARY KEY (`asset_tag`) ,
108 CONSTRAINT `invoice_id`
109 FOREIGN KEY (`invoice_id` )
110 REFERENCES `inventory1`.`invoice` (`invoice_id` )
111 ON DELETE CASCADE
112 ON UPDATE CASCADE)
113ENGINE = InnoDB;
114
115
116-- -----------------------------------------------------
117-- Table `inventory1`.`location`
118-- -----------------------------------------------------
119DROP TABLE IF EXISTS `inventory1`.`location` ;
120
121CREATE TABLE IF NOT EXISTS `inventory1`.`location` (
122 `location_id` INT NOT NULL AUTO_INCREMENT ,
123 `location_name` VARCHAR(45) NULL ,
124 `rack` INT NULL ,
125 `row` INT NULL ,
126 `unit` INT NULL ,
127 PRIMARY KEY (`location_id`) )
128ENGINE = InnoDB;
129
130
131-- -----------------------------------------------------
132-- Table `inventory1`.`physical_asset`
133-- -----------------------------------------------------
134DROP TABLE IF EXISTS `inventory1`.`physical_asset` ;
135
136CREATE TABLE IF NOT EXISTS `inventory1`.`physical_asset` (
137 `physical_asset_id` INT NOT NULL AUTO_INCREMENT ,
138 `location_id` INT NOT NULL ,
139 `employee_id` INT NOT NULL ,
140 `physical_asset_name` VARCHAR(45) NOT NULL ,
141 PRIMARY KEY (`physical_asset_id`) ,
142 INDEX `location_id` (`location_id` ASC) ,
143 INDEX `employee_id` (`employee_id` ASC) ,
144 CONSTRAINT `location_id`
145 FOREIGN KEY (`location_id` )
146 REFERENCES `inventory1`.`location` (`location_id` )
147 ON DELETE CASCADE
148 ON UPDATE CASCADE,
149 CONSTRAINT `employee_id`
150 FOREIGN KEY (`employee_id` )
151 REFERENCES `inventory1`.`employee` (`employee_id` )
152 ON DELETE CASCADE
153 ON UPDATE CASCADE)
154ENGINE = InnoDB;
155
156
157-- -----------------------------------------------------
158-- Table `inventory1`.`asset_details`
159-- -----------------------------------------------------
160DROP TABLE IF EXISTS `inventory1`.`asset_details` ;
161
162CREATE TABLE IF NOT EXISTS `inventory1`.`asset_details` (
163 `asset_id` INT NULL AUTO_INCREMENT ,
164 `asset_tag` INT NOT NULL ,
165 `physical_asset_id` INT NULL ,
166 `asset_type` VARCHAR(45) NULL ,
167 `manufacturer` VARCHAR(45) NULL ,
168 `os` VARCHAR(45) NULL ,
169 `os_version` VARCHAR(45) NULL ,
170 `make` VARCHAR(45) NULL ,
171 `model` VARCHAR(45) NULL ,
172 `serial_number` VARCHAR(45) NULL ,
173 `processor` VARCHAR(45) NULL ,
174 `ram` VARCHAR(45) NULL ,
175 `memory` VARCHAR(45) NULL ,
176 `hdd` VARCHAR(45) NULL ,
177 `host_name` VARCHAR(45) NULL ,
178 `notes` VARCHAR(250) NULL ,
179 PRIMARY KEY (`asset_id`) ,
180 INDEX `physical_asset_id` (`physical_asset_id` ASC) ,
181 INDEX `asset_tag` (`asset_tag` ASC) ,
182 CONSTRAINT `physical_asset_id`
183 FOREIGN KEY (`physical_asset_id` )
184 REFERENCES `inventory1`.`physical_asset` (`physical_asset_id` )
185 ON DELETE CASCADE
186 ON UPDATE CASCADE,
187 CONSTRAINT `asset_tag`
188 FOREIGN KEY (`asset_tag` )
189 REFERENCES `inventory1`.`asset` (`asset_tag` )
190 ON DELETE CASCADE
191 ON UPDATE CASCADE)
192ENGINE = InnoDB;
193
194
195
196SET SQL_MODE=@OLD_SQL_MODE;
197SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
198SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
199
200
201 LATEST FOREIGN KEY ERROR
202 ------------------------
203 120811 22:40:43 Transaction:
204 TRANSACTION 1A4F, ACTIVE 0 sec inserting
205 mysql tables in use 1, locked 1
206 4 lock struct(s), heap size 1248, 2 row lock(s), undo log entries 2
207 MySQL thread id 73, OS thread handle 0x10ccc4000, query id 339 localhost root update
208 INSERT INTO asset_details (asset_type,make, model, serial_number, os,os_version,memory, hdd, processor,notes)
209 Values( 'Monitor','Apple ','Mac Book Pro 17 inch ','65655453445545','Macintosh','Snow Lion ','5 gb ','1 TB','Intel i 9','This is Paskale New Mac book pro computer.')
210 Foreign key constraint fails for table `inventory1`.`asset_details`:
211 ,
212 CONSTRAINT `asset_tag` FOREIGN KEY (`asset_tag`) REFERENCES `asset` (`asset_tag`) ON DELETE CASCADE ON UPDATE CASCADE
213 Trying to add in child table, in index `asset_tag` tuple:
214 DATA TUPLE: 2 fields;
215 0: len 4; hex 80000000; asc ;;
216 1: len 4; hex 80000001; asc ;;
217
218 But in parent table `inventory1`.`asset`, in index `PRIMARY`,
219 the closest match we can find is record:
220 PHYSICAL RECORD: n_fields 8; compact format; info bits 0
221 0: len 4; hex 80000007; asc ;;
222 1: len 6; hex 000000001a4f; asc O;;
223 2: len 7; hex c0000001d40110; asc ;;
224 3: SQL NULL;
225 4: len 14; hex 4465636f6d6d697373696f6e6564; asc Decommissioned;;
226 5: SQL NULL;
227 6: len 7; hex 4d6f6e69746f72; asc Monitor;;
228 7: SQL NULL;
229
230INSERT INTO asset_details (asset_type,make, model, serial_number, os,os_version,memory, hdd, processor,notes)
231 Values( 'Monitor','Apple ','Mac Book Pro 17 inch ','65655453445545','Macintosh','Snow Lion ','5 gb ','1 TB','Intel i 9','This is Paskale New Mac book pro computer.')