PHPFixing
  • Privacy Policy
  • TOS
  • Ask Question
  • Contact Us
  • Home
  • PHP
  • Programming
  • SQL Injection
  • Web3.0

Friday, February 18, 2022

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

 February 18, 2022     composite-primary-key, foreign-keys, mysql, phpmyadmin     No comments   

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
  • Share This:  
  •  Facebook
  •  Twitter
  •  Stumble
  •  Digg
Newer Post Older Post Home

0 Comments:

Post a Comment

Note: Only a member of this blog may post a comment.

Total Pageviews

Featured Post

Why Learn PHP Programming

Why Learn PHP Programming A widely-used open source scripting language PHP is one of the most popular programming languages in the world. It...

Subscribe To

Posts
Atom
Posts
Comments
Atom
Comments

Copyright © PHPFixing