Search code examples
phpsqlforeign-keys

how to create Duplicatable entry at foreign key column in mysql

I created a site that gives you info about universities, every university has registration and exams, etc so in database I have two columns

THE FIRST TABLE IS THE UNIVERSITIES:

CREATE TABLE `universities` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `title` varchar(255) NOT NULL,
 `slug` varchar(255) NOT NULL,
 `views` int(11) NOT NULL DEFAULT 0,
 `image` varchar(255) NOT NULL,
 `banner` varchar(255) NOT NULL,
 `aboutUni` text NOT NULL,
 `aboutCity` text NOT NULL,
 `localRank` int(5) DEFAULT NULL,
 `globalRank` int(3) DEFAULT NULL,
 `foundedY` int(4) NOT NULL,
 `lang` varchar(10) DEFAULT NULL,
 `published` tinyint(1) NOT NULL DEFAULT 1,
 `created_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
 `user_id` int(11) DEFAULT NULL,
 `video` varchar(255) DEFAULT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `slug` (`slug`),
 UNIQUE KEY `title` (`title`),
 UNIQUE KEY `slug_2` (`slug`),
 KEY `universities_ibfk_1` (`user_id`),
 FULLTEXT KEY `title_2` (`title`),
 CONSTRAINT `universities_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=40 DEFAULT CHARSET=utf8

THE SECOND TABLE IS THE EXAMS

CREATE TABLE `bsc` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `university_id` int(11) DEFAULT NULL,
 `dates` text NOT NULL,
 `date_end` date DEFAULT NULL,
 `date` date DEFAULT NULL,
 `papers` text NOT NULL,
 `info` text NOT NULL,
 `method` text NOT NULL,
 `link` varchar(255) DEFAULT NULL,
 `register_link` varchar(255) NOT NULL,
 `links` text NOT NULL,
 `sort` int(2) NOT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `university_id` (`university_id`),
 UNIQUE KEY `university_id_2` (`university_id`),
 CONSTRAINT `bsc_ibfk_1` FOREIGN KEY (`university_id`) REFERENCES `universities` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=utf8mb4

NOTICE!: the tables have other columns but I think it's not important

university_id has a foreign key relation with universities.id

the problem is when I want to add tow exam to a university it appears me this error:

Duplicate entry '39' for key 'university_id'
Solution

  • The problem you faced Duplicate entry '39' for key 'university_id' results from trying to store in university_id a value that already existed somewhere, which is not allowed according to some constraints.

    as shown in the DDL part

    UNIQUE KEY `university_id` (`university_id`)

    This constrain is the main cause for that error, so you need to remove that constrain by

    ALTER TABLE `bsc` DROP INDEX `university_id`;

    Thanks for @Tangentially, who was the first one who expected that error in his comment.