I have 3 tables: company
, case
and report
.
company:
CREATE TABLE IF NOT EXISTS `db`.`company` (
`name` VARCHAR(50) NOT NULL,
`password` VARCHAR(50) NOT NULL,
PRIMARY KEY (`name`))
ENGINE = InnoDB;
case:
CREATE TABLE IF NOT EXISTS `db`.`case` (
`id` VARCHAR(50) NOT NULL,
`image` VARCHAR(255) NULL,
`title` VARCHAR(100) NULL,
`description` VARCHAR(255) NULL,
`address` VARCHAR(255) NULL,
`date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`company_name` VARCHAR(50) NOT NULL,
PRIMARY KEY (`company_name`, `id`),
CONSTRAINT `fk_case_company`
FOREIGN KEY (`company_name`)
REFERENCES `db`.`company` (`name`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
report:
CREATE TABLE IF NOT EXISTS `db`.`report` (
`type` VARCHAR(50) NULL,
`image` VARCHAR(255) NULL,
`description` VARCHAR(255) NULL,
`approval` BINARY NULL DEFAULT 0,
`date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`id` INT NOT NULL AUTO_INCREMENT,
`case_company_name` VARCHAR(50) NOT NULL,
`case_id` VARCHAR(50) NOT NULL,
PRIMARY KEY (`id`, `case_company_name`, `case_id`),
INDEX `fk_report_case1_idx` (`case_company_name` ASC, `case_id` ASC),
CONSTRAINT `fk_report_case1`
FOREIGN KEY (`case_company_name` , `case_id`)
REFERENCES `db`.`case` (`company_name` , `id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
When I insert a row into the case
table using phpMyAdmin
:
So the foreign key is working as I imagined. But when I try to insert a row in the report
table, which is using a compound foreign key consisting of 2 columns, it looks like this:
Why is this? To me, it looks like the connection isn't made between the two tables and that case_company_name
and case_id
are just regular columns.
Most probably it only means that phpmyadmin does not work with composite references this way. You can easily check existing constraints using SHOW CREATE TABLE report
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments