11/24/2023 0 Comments Mysql foreign key constraint![]() This usually happens when that other column is a primary key, but it can also not be a primary key so long as there is an index. I already checked that the date Table is created before the other oneĪnd that the columns are from the same datatype.ĭoes anyone have any idea what might be wrong ?įoreign keys in MySQL must reference a column in another table which has an index, ideally a unique index. In the table and the referenced table do not match for constraint.ĬONSTRAINT PK_date PRIMARY KEY (dateID,annee,mois,semaine,jour)Īstreinte_mensu table: CREATE TABLE astreinte_mensu(įOREIGN KEY (annee) REFERENCES date(annee),įOREIGN KEY (mois) REFERENCES date(mois),įOREIGN KEY (Personne1Nuit) REFERENCES contact(PersonID),įOREIGN KEY (Personne2Nuit) REFERENCES contact(PersonID),įOREIGN KEY (Personne1Jour) REFERENCES contact(PersonID),įOREIGN KEY (Personne2Jour) REFERENCES contact(PersonID) Referenced columns appear as the first columns, or column types (errno: 150 "Foreign key constraint is incorrectly formed")ĭetailed error output: Cannot find an index in the referenced table where the Though one can also set user fields to null instead of deleting the row completely, and in that case you would go with solution number one as one would never actually need to fully delete a user record.I work on a database with MySQL, I have a main calendar table date that share some columns (year and month) with another one astreinte_mensu, I've created some PRIMARY KEYS succesfully but when I come to make the FOREIGN KEYS i got the following error: ERROR 1005 (HY000) at line 33: Can't create table `testDB`.`astreinte_mensu` If the clinic does not care about history of the appointments then you can go with solution number one. In this way when user is deleted you will still have the appointment in the db, but the user_id will be null as that user does not exist anymore. In this case you would go with solution number 2. But the clinic still wants to keep history of all the appointments in the db. Lets say this is for a clinic and the clinic gets a request from a user to delete him from the db. ![]() Add on delete set null meaning that when user is deleted appointments user_id relating to that user should be set to null (though you will have to change user_id int(10) UNSIGNED NOT NULL to user_id int(10) UNSIGNED DEFAULT NULL.Add on delete cascade meaning that when user is deleted appointments relating to that user should also be deleted as suggested by Shadow (point number two).There are a couple of options, but in the end there are really only two main options: `updated_at` timestamp NULL DEFAULT NULL,ĪDD KEY `appointments_doctor_id_foreign` (`doctor_id`),ĪDD KEY `appointments_user_id_foreign` (`user_id`) ĪDD CONSTRAINT `appointments_doctor_id_foreign` FOREIGN KEY (`doctor_id`) REFERENCES `doctors` (`id`),ĪDD CONSTRAINT `appointments_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) `date` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL, `time` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL, ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci Īppointments table CREATE TABLE `appointments` ( `created_at` timestamp NULL DEFAULT NULL, `remember_token` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL, ![]() `dateofbirth` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL, ![]() `password` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL, `email` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL, `postcode` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL, `address` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL, `surname` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL, `firstname` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL, Not sure if it changes anything but just incase I created the tables using laravel I'm getting this error when trying to delete a user from the database, I know it's doing this because the user I'm trying to delete is a foreign key in the appointments table, but I don't know how to correct it or where I have gone wrong. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |