Error Code: 3780. Referencing column 'deal_id' and referenced column 'd_id' in foreign key constraint 'cal_ibfk_1' are incompatible

user_13931587 0
Viewed 2159
CREATE TABLE eng(
    d_id INT,
    min_call_time DATETIME DEFAULT NULL,
    max_call_time DATETIME DEFAULT NULL,
    call INT DEFAULT 0,
    min_meeting_time DATETIME DEFAULT NULL,
    max_meeting_time DATETIME DEFAULT NULL,
    cmeeting INT DEFAULT 0,
    activities INT DEFAULT 0,
    PRIMARY KEY (d_id)
);

CREATE TABLE cal(
    id INT,
    d_id INT,
    c_meetings INT,
    c_active INT,
    c_cancelled INT,
    min_start_time DATETIME,
    max_start_time DATETIME,
    PRIMARY KEY (id),
    FOREIGN KEY (d_id) REFERENCES eng(d_id)
);

Error:

Error Code: 3780. Referencing column 'deal_id' and referenced column 'd_id' in foreign key constraint 'cal_ibfk_1' are incompatible.

I am using Mysql 8.0

Code works fine of DB fiddle though: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=4b0d9723ae09dcebc677d2f014161222

Not working on Mysql.

This (Error number: 3780 Referencing column '%s' and referenced column '%s' in foreign key constraint '%s' are incompatible) is not the same as mine. Datatypes are the same in mine.

I really appreciate any help you can provide.

foreign-keys mysql sql
1 Answers

I think the issue here is the d_id column in the cal table is not declared as NOT NULL. This means that potentially a d_id value in that table could be NULL, and therefore could not be referenced back to d_id in the parent eng table. To remedy this problem, consider the following slight change to your cal table definition:

CREATE TABLE cal(
    id INT,
    d_id INT NOT NULL,        -- change is here
    c_meetings INT,
    c_active INT,
    c_cancelled INT,
    min_start_time DATETIME,
    max_start_time DATETIME,
    PRIMARY KEY (id),
    FOREIGN KEY (d_id) REFERENCES eng(d_id)
);

Note that primary key columns in MySQL are implicitly declared as NOT NULL, even if not implicitly defined as such. Therefore, your eng table is really being defined as:

CREATE TABLE eng(
    d_id INT NOT NULL,
    ...,
    PRIMARY KEY (d_id)
);

The issue with a potential NULL value for d_id in the child cal table is that NULL logically means "not known," and therefore such a value cannot possibly be connected back to the d_id primary key in the eng table.

user_18632290
Related