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

CodePudding user response：

in main table the id is an int unsigned in the status table it is only int which are incompatible. change both to unsigned int or only int

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`)
);