This is the error message I get when I run the show DB innobd status command . I am new to php and mysql and any help will be appreciated . Thank you in advance .
When I submit data into my database, the information goes into the asset tables but not the assert_details ?
LATEST FOREIGN KEY ERROR
------------------------
120811 22:40:43 Transaction:
TRANSACTION 1A4F, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1248, 2 row lock(s), undo log entries 2
MySQL thread id 73, OS thread handle 0x10ccc4000, query id 339 localhost root update
INSERT INTO asset_details (asset_type,make, model, serial_number, os,os_version,memory, hdd, processor,notes)
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.')
Foreign key constraint fails for table `inventory1`.`asset_details`:
,
CONSTRAINT `asset_tag` FOREIGN KEY (`asset_tag`) REFERENCES `asset` (`asset_tag`) ON DELETE CASCADE ON UPDATE CASCADE
Trying to add in child table, in index `asset_tag` tuple:
DATA TUPLE: 2 fields;
0: len 4; hex 80000000; asc ;;
1: len 4; hex 80000001; asc ;;
But in parent table `inventory1`.`asset`, in index `PRIMARY`,
the closest match we can find is record:
PHYSICAL RECORD: n_fields 8; compact format; info bits 0
0: len 4; hex 80000007; asc ;;
1: len 6; hex 000000001a4f; asc O;;
2: len 7; hex c0000001d40110; asc ;;
3: SQL NULL;
4: len 14; hex 4465636f6d6d697373696f6e6564; asc Decommissioned;;
5: SQL NULL;
6: len 7; hex 4d6f6e69746f72; asc Monitor;;
7: SQL NULL;
This is the script to create the tables in my database .
This the script to create the the tables
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';
DROP SCHEMA IF EXISTS `inventory1` ;
CREATE SCHEMA IF NOT EXISTS `inventory1` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci ;
USE `inventory1` ;
-- -----------------------------------------------------
-- Table `inventory1`.`department`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `inventory1`.`department` ;
CREATE TABLE IF NOT EXISTS `inventory1`.`department` (
`department_id` INT NOT NULL AUTO_INCREMENT ,
`department_name` VARCHAR(45) NOT NULL ,
`job_id` VARCHAR(45) NOT NULL ,
PRIMARY KEY (`department_id`) )
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `inventory1`.`employee`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `inventory1`.`employee` ;
CREATE TABLE IF NOT EXISTS `inventory1`.`employee` (
`employee_id` INT NULL AUTO_INCREMENT ,
`department_id` INT NOT NULL ,
`fname` VARCHAR(45) NULL ,
`lname` VARCHAR(45) NULL ,
`email` VARCHAR(45) NULL ,
`phone_number` VARCHAR(45) NULL ,
`hire_date` VARCHAR(45) NULL ,
`job_id` VARCHAR(45) NULL ,
`manager_id` VARCHAR(45) NULL ,
PRIMARY KEY (`employee_id`) ,
INDEX `department_id` (`department_id` ASC) ,
CONSTRAINT `department_id`
FOREIGN KEY (`department_id` )
REFERENCES `inventory1`.`department` (`department_id` )
ON DELETE CASCADE
ON UPDATE CASCADE)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `inventory1`.`invoice`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `inventory1`.`invoice` ;
CREATE TABLE IF NOT EXISTS `inventory1`.`invoice` (
`invoice_id` INT NOT NULL AUTO_INCREMENT ,
`invoice_number` INT NULL ,
`invoice_date` INT UNSIGNED NULL ,
`purchase_price` INT UNSIGNED NULL ,
`quantity` INT UNSIGNED NULL ,
`order_date` INT UNSIGNED NULL ,
`vender` INT UNSIGNED NULL ,
`warranty_end` DATE NULL ,
`notes` VARCHAR(255) NULL ,
PRIMARY KEY (`invoice_id`) )
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `inventory1`.`asset`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `inventory1`.`asset` ;
CREATE TABLE IF NOT EXISTS `inventory1`.`asset` (
`asset_tag` INT NOT NULL ,
`invoice_id` INT NULL ,
`status` VARCHAR(25) NULL ,
`cap_ex` VARCHAR(20) NULL ,
`asset_type` VARCHAR(25) NULL ,
`invoice_number` INT NULL ,
PRIMARY KEY (`asset_tag`) ,
CONSTRAINT `invoice_id`
FOREIGN KEY (`invoice_id` )
REFERENCES `inventory1`.`invoice` (`invoice_id` )
ON DELETE CASCADE
ON UPDATE CASCADE)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `inventory1`.`location`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `inventory1`.`location` ;
CREATE TABLE IF NOT EXISTS `inventory1`.`location` (
`location_id` INT NOT NULL AUTO_INCREMENT ,
`location_name` VARCHAR(45) NULL ,
`rack` INT NULL ,
`row` INT NULL ,
`unit` INT NULL ,
PRIMARY KEY (`location_id`) )
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `inventory1`.`physical_asset`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `inventory1`.`physical_asset` ;
CREATE TABLE IF NOT EXISTS `inventory1`.`physical_asset` (
`physical_asset_id` INT NOT NULL AUTO_INCREMENT ,
`location_id` INT NOT NULL ,
`employee_id` INT NOT NULL ,
`physical_asset_name` VARCHAR(45) NOT NULL ,
PRIMARY KEY (`physical_asset_id`) ,
INDEX `location_id` (`location_id` ASC) ,
INDEX `employee_id` (`employee_id` ASC) ,
CONSTRAINT `location_id`
FOREIGN KEY (`location_id` )
REFERENCES `inventory1`.`location` (`location_id` )
ON DELETE CASCADE
ON UPDATE CASCADE,
CONSTRAINT `employee_id`
FOREIGN KEY (`employee_id` )
REFERENCES `inventory1`.`employee` (`employee_id` )
ON DELETE CASCADE
ON UPDATE CASCADE)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `inventory1`.`asset_details`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `inventory1`.`asset_details` ;
CREATE TABLE IF NOT EXISTS `inventory1`.`asset_details` (
`asset_id` INT NULL AUTO_INCREMENT ,
`asset_tag` INT NOT NULL ,
`physical_asset_id` INT NULL ,
`asset_type` VARCHAR(45) NULL ,
`manufacturer` VARCHAR(45) NULL ,
`os` VARCHAR(45) NULL ,
`os_version` VARCHAR(45) NULL ,
`make` VARCHAR(45) NULL ,
`model` VARCHAR(45) NULL ,
`serial_number` VARCHAR(45) NULL ,
`processor` VARCHAR(45) NULL ,
`ram` VARCHAR(45) NULL ,
`memory` VARCHAR(45) NULL ,
`hdd` VARCHAR(45) NULL ,
`host_name` VARCHAR(45) NULL ,
`notes` VARCHAR(250) NULL ,
PRIMARY KEY (`asset_id`) ,
INDEX `physical_asset_id` (`physical_asset_id` ASC) ,
INDEX `asset_tag` (`asset_tag` ASC) ,
CONSTRAINT `physical_asset_id`
FOREIGN KEY (`physical_asset_id` )
REFERENCES `inventory1`.`physical_asset` (`physical_asset_id` )
ON DELETE CASCADE
ON UPDATE CASCADE,
CONSTRAINT `asset_tag`
FOREIGN KEY (`asset_tag` )
REFERENCES `inventory1`.`asset` (`asset_tag` )
ON DELETE CASCADE
ON UPDATE CASCADE)
ENGINE = InnoDB;
SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
LATEST FOREIGN KEY ERROR
------------------------
120811 22:40:43 Transaction:
TRANSACTION 1A4F, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1248, 2 row lock(s), undo log entries 2
MySQL thread id 73, OS thread handle 0x10ccc4000, query id 339 localhost root update
INSERT INTO asset_details (asset_type,make, model, serial_number, os,os_version,memory, hdd, processor,notes)
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.')
Foreign key constraint fails for table `inventory1`.`asset_details`:
,
CONSTRAINT `asset_tag` FOREIGN KEY (`asset_tag`) REFERENCES `asset` (`asset_tag`) ON DELETE CASCADE ON UPDATE CASCADE
Trying to add in child table, in index `asset_tag` tuple:
DATA TUPLE: 2 fields;
0: len 4; hex 80000000; asc ;;
1: len 4; hex 80000001; asc ;;
But in parent table `inventory1`.`asset`, in index `PRIMARY`,
the closest match we can find is record:
PHYSICAL RECORD: n_fields 8; compact format; info bits 0
0: len 4; hex 80000007; asc ;;
1: len 6; hex 000000001a4f; asc O;;
2: len 7; hex c0000001d40110; asc ;;
3: SQL NULL;
4: len 14; hex 4465636f6d6d697373696f6e6564; asc Decommissioned;;
5: SQL NULL;
6: len 7; hex 4d6f6e69746f72; asc Monitor;;
7: SQL NULL;