Relate two fields to the same primary key PHP - MySQL

Question

Relate two fields to the same primary key PHP - MySQL

Navigation

#1 by (1 votes)

1

This is how I have the code MySQL of my tables.

tasks :

CREATE TABLE tasks(
task_id INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,
created_by INTEGER UNSIGNED NOT NULL,
support_by INTEGER UNSIGNED,
customer_id INTEGER UNSIGNED NOT NULL,
request_of VARCHAR(45) NOT NULL,
description TEXT NOT NULL,
status_task ENUM('PENDIENTE', 'EN PROCESO', 'COMPLETADA', 'CANCELADA') NOT NULL,
start_date DATETIME NOT NULL,
finish_date DATETIME,
FOREIGN KEY (created_by) REFERENCES technicians(technician_id)
    ON DELETE RESTRICT ON UPDATE CASCADE,

FOREIGN KEY (support_by) REFERENCES technicians(technician_id)
    ON DELETE RESTRICT ON UPDATE CASCADE,

FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
    ON DELETE RESTRICT ON UPDATE CASCADE
);

technicians:

CREATE TABLE technicians(
technician_id INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,
name_tech VARCHAR(45) NOT NULL,
username_tech VARCHAR(15) UNIQUE NOT NULL,
pass_tech VARCHAR(255) NOT NULL,
role ENUM('technician', 'administrator') NOT NULL
);

It shows me the following error when wanting to insert data:

Fatal error: Uncaught PDOException: SQLSTATE [23000]: Integrity constraint violation: 1452 Can not add or update to child row: a foreign key constraint fails ( amber . tasks , CONSTRAINT tasks_ibfk_2 FOREIGN KEY ( support_by ) REFERENCES technicians ( technician_id ) ON UPDATE CASCADE) in C: \ xampp \ htdocs \ amber \ db \ import.php: 107 Stack trace: # 0 C: \ xampp \ htdocs \ amber \ db \ import. php (107): PDOStatement-> execute () # 1 {main} thrown in C: \ xampp \ htdocs \ amber \ db \ import.php on line 107

Important:

What I want to do is to insert the support_by field as NULL but it does not allow it, I leave the code to insert it in the following link: import.php and the file tasks.json - Nilton Venegas 15 min ago

mysql php

asked by Nilton Venegas 22.12.2017 в 19:05

source

1 answer

How can I make an entry always up MYSQL PHP Error Undefined offset in PHP (Create table with stored procedure result)

score 1 · Accepted Answer

What I want to do is to insert the field support_by as NULL but it does not allow it

Yes it allows it. Test, with a simplified example, that you can assign NULL in a case like this: Demo

The problem, simply, is that the value you are assigning is not NULL , even if you think it is.

Looking at your code, I see that your INSERT is the following:

INSERT INTO tasks (task_id, created_by, support_by, customer_id, request_of,
                   description, status_task, start_date, finish_date)
           VALUES (:task_id, :created_by, :support_by, :customer_id, :request_of,
                   :description, :status_task, :start_date, :finish_date)

So you are assigning a value to the column support_by . If this value is not null (which obviously is your case), then MySQL will throw you an error if this value does not exist in the technicians table.

Keep in mind that trying to pass an empty string, which may well be what you are doing, is not the same as assigning a NULL . Depending on the configuration of your MySQL, if you try to assign an empty string, you might throw an error by not assigning a numeric value. But sometimes, instead of complaining about it, it automatically converts the empty string to 0 . This is probably what is happening in your case. Note, with the following demonstration, how this can happen: Demo

The solution is that you really assign a value NULL , not just an empty string. Or, if you know that you will never want to assign a value to that column during the INSERT , because the idea is that you will add it later with a UPDATE , then you should not even include that column in the statement% % co:

INSERT INTO tasks (task_id, created_by, customer_id, request_of,
                   description, status_task, start_date, finish_date)
           VALUES (:task_id, :created_by, :customer_id, :request_of,
                   :description, :status_task, :start_date, :finish_date)