Compound Foreign Key INSERT

transporter_room_3

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:

enter image description here

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:

enter image description here

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.

user3714601

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.

edited at
0

Comments

0 comments
Login to comment

Related

From Dev

JPA compound key with foreign key and enum

From Dev

mysql foreign key to compound primary key

From Dev

Insert foreign key value

From Dev

Insert foreign key into table

From Dev

Symfony Insert Foreign Key Value

From Dev

Insert foreign key data in database

From Dev

Hibernate: insert data with foreign key

From Dev

PostgreSQL insert if foreign key exists

From Dev

Insert data into foreign key column

From Dev

Batch Insert - Foreign Key Not Working

From Dev

PDO insert with foreign key is failing

From Dev

How to insert a foreign key in a row?

From Dev

Insert statement conflicted with the Foreign key

From Dev

Foreign Key conflict on INSERT statement

From Dev

The INSERT statement conflicted with the FOREIGN KEY constraint error

From Dev

Entity Framework Foreign Key Error with Insert

From Dev

doctrine2 with codeigniter foreign key insert

From Dev

INSERT FOREIGN KEY in another table with executemany() in PostgreSQL

From Dev

Insert random data into a table that has foreign key

From Dev

INSERT Statement conflicted with Foreign Key constraint

From Dev

SQL insert data into table has foreign key

From Dev

Cannot INSERT because of Foreign key constraints - mysql

From Dev

Oracle - Insert Stored Procedure Foreign Key

From Dev

Can INSERT [...] ON CONFLICT be used for foreign key violations?

From Dev

Laravel insert FOREIGN KEY in Form Request method

From Dev

Java insert data into database with Foreign Key

From Dev

Generate insert statements with foreign key constraints

From Dev

Sqlite foreign key insert should fail

From Dev

Insert Record for Each Foreign Key to Result Set