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
0 Comments:
Post a Comment
Note: Only a member of this blog may post a comment.