[FIXED] Cannot add foreign key constraint in phpmyadmin - mysql

Issue

I have a table called teachers. I cannot use the id from teachers to create a composite table in slot table with the following query.

CREATE TABLE `teachers` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `first_name` varchar(255) NOT NULL,
  `last_name` varchar(255) NOT NULL,
  `password` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
);

ALTER TABLE `teachers`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `teachers_email_unique` (`email`);

to create slot table

CREATE TABLE `slot` (
  `teacher_id` bigint(20) NOT NULL,
  `is_confirmed` tinyint(1) NOT NULL,
    PRIMARY kEY (`teacher_id`),
    foreign key (`teacher_id`) references `teachers`(`id`) on delete CASCADE

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Solution

Data type of the referencing and referenced fields, should be exactly the same while defining a Foreign Key constraint. In your teachers table, id is BIGINT UNSIGNED, while in your slot table, it is BIGINT only. Add UNSIGNED clause as well:

CREATE TABLE `slot` (
  `teacher_id` bigint(20) UNSIGNED NOT NULL,
  `is_confirmed` tinyint(1) NOT NULL,
    PRIMARY kEY (`teacher_id`),
    foreign key (`teacher_id`) references `teachers`(`id`) on delete CASCADE

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Answered By - Madhur Bhaiya