Ошибка 3780 mysql workbench

I’m getting the following order trying to use a table’s primary key as another table’s primary key:

ERROR 3780 (HY000): Referencing column ‘optimization_id’ and referenced column ‘optimization_id’ in foreign key constraint ‘optimization.main — optimization.status’ are incompatible.

I’m using the primary key of OPTIMIZATION.main as the primary key of OPTIMIZATION.status as well since it’s a one-to-one relationship and I thought this would be easier/simpler than just adding an id in every table as the primary key. These are the only tables where I’m doing this, the other ones I’m not using the foreign key as the primary key as well. However, it seemed easier/simpler to do this for the few important tables I’m using to organize the data together.

My questions therefore are:

1. Why am I getting error 3780 using the provided code?
2. If this isn’t best practice, what would be another way of doing this?
3. Is it even possible to use a different table’s primary key (like
   main) as the primary key for another table (like status)?

In this DBfiddle is the code I’m using, generated by SqlDBM, to create these two tables.

Here is the visualization of the two tables (one visualized relationship isn’t applicable in this case) through the SqlDBM software.

CREATE TABLE `OPTIMIZATION`.`main`
(
 `optimization_id`       int unsigned NOT NULL ,
 `optimization_name`     varchar(250) NOT NULL ,
 `optimization_category` varchar(200) NULL ,

PRIMARY KEY (`optimization_id`)
);

CREATE TABLE `OPTIMIZATION`.`status`
(
 `current_status`  varchar(25) NULL ,
 `discovery_date`  datetime NULL ,
 `processed_date`  datetime NULL ,
 `processed_by`    varchar(100) NULL ,
 `optimization_id` int unsigned NOT NULL ,

PRIMARY KEY (`optimization_id`),
KEY `fkIdx_409` (`optimization_id`),
CONSTRAINT `optimization.main - optimization.status` FOREIGN KEY `fkIdx_409` (`optimization_id`) REFERENCES `OPTIMIZATION`.`main` (`optimization_id`)
);

2

I’m getting the following order trying to use a table’s primary key as another table’s primary key:

ERROR 3780 (HY000): Referencing column ‘optimization_id’ and referenced column ‘optimization_id’ in foreign key constraint ‘optimization.main — optimization.status’ are incompatible.

I’m using the primary key of OPTIMIZATION.main as the primary key of OPTIMIZATION.status as well since it’s a one-to-one relationship and I thought this would be easier/simpler than just adding an id in every table as the primary key. These are the only tables where I’m doing this, the other ones I’m not using the foreign key as the primary key as well. However, it seemed easier/simpler to do this for the few important tables I’m using to organize the data together.

My questions therefore are:

1. Why am I getting error 3780 using the provided code?
2. If this isn’t best practice, what would be another way of doing this?
3. Is it even possible to use a different table’s primary key (like
   main) as the primary key for another table (like status)?

In this DBfiddle is the code I’m using, generated by SqlDBM, to create these two tables.

Here is the visualization of the two tables (one visualized relationship isn’t applicable in this case) through the SqlDBM software.

CREATE TABLE `OPTIMIZATION`.`main`
(
 `optimization_id`       int unsigned NOT NULL ,
 `optimization_name`     varchar(250) NOT NULL ,
 `optimization_category` varchar(200) NULL ,

PRIMARY KEY (`optimization_id`)
);

CREATE TABLE `OPTIMIZATION`.`status`
(
 `current_status`  varchar(25) NULL ,
 `discovery_date`  datetime NULL ,
 `processed_date`  datetime NULL ,
 `processed_by`    varchar(100) NULL ,
 `optimization_id` int unsigned NOT NULL ,

PRIMARY KEY (`optimization_id`),
KEY `fkIdx_409` (`optimization_id`),
CONSTRAINT `optimization.main - optimization.status` FOREIGN KEY `fkIdx_409` (`optimization_id`) REFERENCES `OPTIMIZATION`.`main` (`optimization_id`)
);

2

0

Charlie Fish
29 Июн 2022 в 20:07

0

sanka
23 Янв 2022 в 04:51

0

DisplayName
14 Ноя 2021 в 10:04

I am making two tables and I want the personOne column from table b to reference the person column on table a, but for some reason it doesn’t work.

I have the below code as an example:

create table a(
person varchar(20),
cost varchar(10) not Null
)character set latin1
collate latin1_general_ci;

create table b(
personOne varchar(20),
personTwo varChar(2) not null,
key person_index (personOne),
CONSTRAINT C FOREIGN KEY (personOne) references a(person)
) engine=InnoDB default charset=latin1;

It is telling me an error:

Error Code: 3780. Referencing column ‘personOne’ and referenced column ‘person’ in foreign key constraint ‘C’ are incompatible.

I tried to to set table a engine to InnoDB, but that didn’t work. I researched the problem more but couldn’t figure out how to fix it.

Answer 1

The two columns must have the same collation. I’m guessing your table b is using the default collation for old versions of MySQL, which is latin1_swedish_ci.

You might like to review this checklist for foreign keys: https://stackoverflow.com/a/4673775/20860

I suggest the best choice is to declare both a and b tables with character set utf8mb4 and collation utf8mb4_unicode_520_ci if your version of MySQL is new enough to support it.

• 05:30

  Trying to take the file extension out of my URL

• 04:00

  display list that in each row 1 li

• 00:00

  Read audio channel data from video file nodejs

• 10:30

  session not saved after running on the browser

• 9:10

  Best way to trigger worker_thread OOM exception in Node.js

• 07:40

  Firebase Cloud Functions: PubSub, «res.on is not a function»

• 04:00

  TypeError: Cannot read properties of undefined (reading ‘createMessageComponentCollector’)

• 9:20

  AWS XRAY on Fargate service

• 7:40

  How to resolve getting Error 429 Imgur Api