Hello all,

I'm hoping someone can help with this because I'm sure its something stupid.I'm stuck on a foreign key for the table "candidats" named "films_id_films" that links to the table "films" with "id_films" .

 

Here is the error message:

ADD CONSTRAINT `candidats_ibfk_2` FOREIGN KEY
(`categories_id_categorie`) REFERENCES `categories` (`id_categorie`) ON
DELETE NO ACTION ON UPDATE NO ACTION;

#1064 - You have an error in your SQL syntax; check the manual
that corresponds to your MySQL server version for the right syntax to
use near 'ADD CONSTRAINT `candidats_ibfk_3` FOREIGN KEY (`film_id_film`)
REFERENCES `films' at line 1

 

And here is my SQL:

-- phpMyAdmin SQL Dump
-- version 3.5.1
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Apr 16, 2013 at 11:44 PM
-- Server version: 5.5.25
-- PHP Version: 5.4.4

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

--
-- Database: `inspirecinema`
--

-- --------------------------------------------------------

--
-- Table structure for table `admin`
--

CREATE TABLE `admin` (
  `id_admin` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
  `mdp_admin` varchar(25) DEFAULT NULL,
  `nom_admin` varchar(50) NOT NULL,
  `prenom_admin` varchar(50) DEFAULT NULL,
  `qualite_admin` int(50) DEFAULT NULL,
  `email_admin` varchar(75) NOT NULL,
  `tel_admin` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id_admin`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=7 ;

--
-- Dumping data for table `admin`
--

INSERT INTO `admin` (`id_admin`, `mdp_admin`, `nom_admin`, `prenom_admin`, `qualite_admin`, `email_admin`, `tel_admin`) VALUES
(1, 'toto', 'Alex', NULL, 1, '', NULL),
(2, 'nextformation', 'canemarchepas', NULL, 0, '', NULL),
(5, 'toto', 'Alex', NULL, 0, '', NULL),
(6, 'toto', 'Alex', NULL, 1, '', NULL);

-- --------------------------------------------------------

--
-- Table structure for table `affiches`
--

CREATE TABLE `affiches` (
  `id_affiche` bigint(20) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id_affiche`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

--
-- Table structure for table `candidats`
--

CREATE TABLE `candidats` (
  `id_candidats` varchar(20) NOT NULL DEFAULT '1',
  `affiche_candidat` varchar(50) DEFAULT NULL,
  `film_id_film` tinyint(1) unsigned NOT NULL,
  `categories_id_categorie` tinyint(2) unsigned NOT NULL,
  `ecole_id_ecole` tinyint(3) unsigned NOT NULL,
  `email_candidat` varchar(75) DEFAULT NULL,
  `password_candidat` varchar(20) DEFAULT NULL,
  `nom_candidat` varchar(50) DEFAULT NULL,
  `prenom_candidat` varchar(50) DEFAULT NULL,
  `datenaiss` date DEFAULT NULL,
  `titre` varchar(50) DEFAULT 'sans-titre',
  `description` mediumblob,
  `approuvee` tinyint(1) NOT NULL DEFAULT '0',
  `abus` tinyint(1) NOT NULL DEFAULT '0',
  `votes_candidat` mediumint(4) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id_candidats`),
  KEY `candidats_FKIndex4` (`film_id_film`),
  KEY `candidats_FKIndex3` (`ecole_id_ecole`),
  KEY `candidats_FKIndex2` (`categories_id_categorie`),
  KEY `film_id_film` (`film_id_film`),
  KEY `ecole_id_ecole` (`ecole_id_ecole`),
  KEY `categories_id_categorie` (`categories_id_categorie`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `candidats`
--

INSERT INTO `candidats` (`id_candidats`, `affiche_candidat`, `film_id_film`, `categories_id_categorie`, `ecole_id_ecole`, `email_candidat`, `password_candidat`, `nom_candidat`, `prenom_candidat`, `datenaiss`, `titre`, `description`, `approuvee`, `abus`, `votes_candidat`) VALUES
('bronwyn', NULL, 0, 2, 14, 'bronwyn@bronwyn.com', 'bronwyn', 'Billingslea', 'Bronwyn', NULL, 'Men in Black ', 0x696e726569c3b967727a6f67424e4f52474e525a676f676e7069727a, 1, 0, 0),
('fiona', 'blanche-neige.jpg', 0, 2, 13, 'fiona@fiona.com', 'fiona', 'Billingslea', 'Fiona', NULL, 'Snow White', 0x5768617427732074686174207768656e2069742773206174206f6f616d20676927206f276572206565682e20536f6674206c616420686f77206d756368206e6f626275742061206c616420626967206769726c277320626c6f7573652077686572652773207468612062696e2e20426967206769726c277320626c6f7573652062652072656574206368756666696e27206e6f726120627265616463616b6520627265616463616b65207427666f6f74206f27206f7572207374616972732e204d69636861656c2070616c696e20626967206769726c277320626c6f75736520627265616463616b652074686120776861742e, 1, 0, 0),
('loic', NULL, 0, 3, 13, 'loic@loic.com', 'loic', 'Billingslea', 'Loïc', NULL, 'Taken 2 bed', 0x426f626261722069732074686174207468696e6520736f6674206c616420626967206769726c277320626c6f757365206e652765726d696e642e204279202765636b2e20537570207769272027696d20612070696e7420276f206d696c6420736f6674206c61642e20417070656e73206173206d6179626520626967206769726c277320626c6f7573652e20426520726565742e20417070656e73206173206d617962652077686572652773207468612062696e20736f667420736f75746865726e2070616e7379206168276c6c20676927207468692073756d6d617420746f20726f6f657220616261687420612070696e7420276f206d696c642e2049732074686174207468696e652e2054696e74696e74696e2e20476f642773206f776e20636f756e74792077696c6c202765202765636b6572736c696b65207768617427732074686174207768656e2069742773206174206f6f616d2e2054696e74696e74696e2069732074686174207468696e6520616e7920726f6f61642077686572652074686572652773206d75636b207468657265277320627261737320676927206f2765722e, 0, 1, 0);

-- --------------------------------------------------------

--
-- Table structure for table `categories`
--

CREATE TABLE `categories` (
  `id_categorie` tinyint(2) unsigned NOT NULL AUTO_INCREMENT,
  `nom_categorie` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id_categorie`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;

--
-- Dumping data for table `categories`
--

INSERT INTO `categories` (`id_categorie`, `nom_categorie`) VALUES
(1, '-16 ans'),
(2, '16-17 ans'),
(3, '+17 ans'),
(4, 'groupe');

-- --------------------------------------------------------

--
-- Table structure for table `ecole`
--

CREATE TABLE `ecole` (
  `id_ecole` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
  `nom_ecole` varchar(100) DEFAULT NULL,
  `nom_referent` varchar(50) DEFAULT NULL,
  `email_ecole` varchar(50) DEFAULT NULL,
  `tel_ecole` varchar(13) DEFAULT NULL,
  PRIMARY KEY (`id_ecole`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=15 ;

--
-- Dumping data for table `ecole`
--

INSERT INTO `ecole` (`id_ecole`, `nom_ecole`, `nom_referent`, `email_ecole`, `tel_ecole`) VALUES
(10, 'E GAND', 'TUTU', 'e.gand@lycee.fr', '003300000000'),
(12, 'r rolan', 'toto', 'toto@free.fr', '0000000'),
(13, 'Voltaire', 'BOBO', 'voltaire@hotmail.fr', '01.56.60.78.8'),
(14, 'Lycée François Mitterand', 'Mme Bernard', 'lycee@lycee.fr', '02 22 23 24 2');

-- --------------------------------------------------------

--
-- Table structure for table `films`
--

CREATE TABLE `films` (
  `id_films` tinyint(1) unsigned NOT NULL AUTO_INCREMENT,
  `nom_films` varchar(25) NOT NULL,
  `class_films` varchar(12) NOT NULL,
  PRIMARY KEY (`id_films`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

--
-- Table structure for table `jurys`
--

CREATE TABLE `jurys` (
  `id_jurys` tinyint(11) unsigned NOT NULL AUTO_INCREMENT,
  `nom_jury` varchar(25) DEFAULT NULL,
  `prenom_jury` varchar(25) NOT NULL,
  `tel_jury` int(11) DEFAULT NULL,
  `email_jury` varchar(55) NOT NULL,
  `presentation` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id_jurys`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=6 ;

--
-- Dumping data for table `jurys`
--

INSERT INTO `jurys` (`id_jurys`, `nom_jury`, `prenom_jury`, `tel_jury`, `email_jury`, `presentation`) VALUES
(3, 'Gratieux', 'Helène', 102030405, '0', 'Jury principal'),
(4, 'Gratieux', 'Helène', 102030405, '0', 'Jury n°1'),
(5, 'Cassidy', 'Kirsten', 123456789, 'mantismamita@hotmail.com', 'Chef et commandante');

-- --------------------------------------------------------

--
-- Table structure for table `prix`
--

CREATE TABLE `prix` (
  `id_prix` int(11) NOT NULL AUTO_INCREMENT,
  `affiches_id_affiche` bigint(20) NOT NULL,
  `nom_prix` varchar(100) DEFAULT NULL,
  `dotation_prix` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id_prix`),
  KEY `prix_FKIndex1` (`affiches_id_affiche`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `candidats`
--
ALTER TABLE `candidats`
  ADD CONSTRAINT `candidats_ibfk_1` FOREIGN KEY (`ecole_id_ecole`) REFERENCES `ecole` (`id_ecole`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  ADD CONSTRAINT `candidats_ibfk_2` FOREIGN KEY (`categories_id_categorie`) REFERENCES `categories` (`id_categorie`) ON DELETE NO ACTION ON UPDATE NO ACTION;
  ADD CONSTRAINT `candidats_ibfk_3` FOREIGN KEY (`film_id_film`) REFERENCES `films` (`id_films`) ON DELETE NO ACTION ON UPDATE NO ACTION;
--

Many thanks!

Hey Mantis,

 

I've never written out constraints etc like this before, but could it be the syntax here:

 

ALTER TABLE `candidats`
  ADD CONSTRAINT `candidats_ibfk_1` FOREIGN KEY (`ecole_id_ecole`) REFERENCES `ecole` (`id_ecole`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  ADD CONSTRAINT `candidats_ibfk_2` FOREIGN KEY (`categories_id_categorie`) REFERENCES `categories` (`id_categorie`) ON DELETE NO ACTION ON UPDATE NO ACTION;
  ADD CONSTRAINT `candidats_ibfk_3` FOREIGN KEY (`film_id_film`) REFERENCES `films` (`id_films`) ON DELETE NO ACTION ON UPDATE NO ACTION;

 

The first constraint ends with a comma but the other two end with a semi-colon. Maybe the second needs to also be a comma instead of a semi colon.

Thanks Lindsey,

 

I think you're onto something. I've tried all sorts of configurations and nothing yet.

 

I used Sequel pro to generate the code and it wouldn't accept my foreign key (neither would php-myadmin) so I'm trying to modify the SQL directly so I can copy the format of the other foreign keys and re-import it.

New error message:

 

#1452 - Cannot add or update a child row: a foreign key constraint
fails (`inspirecinema`.`#sql-8f4_13e`, CONSTRAINT `candidats_ibfk_3`
FOREIGN KEY (`film_id_film`) REFERENCES `films` (`id_films`) ON DELETE
NO ACTION ON UPDATE NO ACTION)

Maybe this will help: http://stackoverflow.com/questions/1253459/mysql-error-1452-cannot-add-or-update-a-child-row-a-foreign-key-constraint-fa

It turns out the problem was that my candidats table already had film ids entered as "0" so it couldn't allow auto incrementation as three objects at 0 were already entered. Anyway its working now

Glad you got it sorted