Symfony Insert SQL Error - a foreign key constraint fails

4
votes

I am using fixtures to generate data for my symfony project, but for some reason the following error keeps getting thrown:

Unable to execute INSERT statement. [wrapped: SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (meeting.meeting_attendance, CONSTRAINT meeting_attendance_FK_1 FOREIGN KEY (meeting_id) REFERENCES meeting_meetings (id))]

I am looking for why the error is occuring, I am using Symfony 1.4, with propel and a MySQL database.

The meetings schema and attendance schema is as below, full copy at http://pastebin.com/HZhaqWSN

  meeting_meetings:
    id: ~
    owner_id: { type: integer, foreignTable: sf_guard_user_profile, foreignReference: user_id, required: true }
    group_id: { type: integer, foreignTable: meeting_groups, foreignReference: id }
    name: { type: varchar, required: true, default: Meeting } 
    completed: { type: boolean, required: true, default: 0 } 
    location: { type: varchar, required: true,  default: Unknown }
    start: { type: integer, required: true }
    length: { type: integer, required: true, default: 60 }
    created_at: ~
    updated_at: ~

  meeting_attendance:
    id: ~
    meeting_id: { type: integer, foreignTable: meeting_meetings, foreignReference: id, required: true }
    user_id: { type: integer, foreignTable: sf_guard_user_profile, foreignReference: user_id, required: true }
    invited: { type: boolean, required: true, default: 0 }
    attending: { type: boolean, required: true, default: 0 }
    apolgies: { type: boolean, required: true, default: 0 }
    attended: { type: boolean, required: true, default: 0 }
    apolgies_comment: { type: varchar(255) }

03_meetings.yml is as follows

MeetingMeetings:
  PezMeeting:
    owner_id: Pezmc
    completed: 0
    location: Awesome Room
    start: 1310059022
    length: 60

and 09_attendance.yml is as follows:

MeetingAttendance:
  MeetingAttendance1:
    meeting_id: PezMeeting
    user_id: Pezmc
    invited: 1
    attending: 1
    apolgies: 0
    attended: 0
    apolgies_comment: None

Both my fixtures were using PHP to generate randomly but I have changed them to the above to try and locate this error!

I assume I must have overlooked something simple, but I have been trying to debug this for over an hour and am at my wits end!

Does anyone know what is causing this error or how to resolve it?

Many thanks for your time,

EDIT: Someone suggested putting everything in one file, I have done this and run the file with php (to see exactly what propel is reading). It still gets the same error:

MeetingMeetings:
  PezMeeting:
    owner_id: Pezmc
    completed: 0
    location: Awesome Room
    start: 1310059022
    length: 60

MeetingItems:
  Item1:
    Value: VfH0qXxGV4Ylb ZtRm DKkDE9dTzlWR z Nm TnNhxVPvZO eOn IM5 v ETOl v 4 xsA7HexNwzB YDvz I uay Sjm3rbAu iaiZIPGv l0oNSFCG To

MeetingAgendas:
  Agenda1:
    MeetingId: PezMeeting
    ItemId: Item1

MeetingActions:
  Action1:
    ItemId: Item1
    MeetingId: PezMeeting
    Due: 1310705295
    Start: 1310358321
    Completed: 1

MeetingAttendance:
  MeetingAttendance1:
    meeting_id: PezMeeting
    user_id: Pezmc
    invited: 1
    attending: 1
    apolgies: 0
    attended: 0

Still getting:

Unable to execute INSERT statement. [wrapped: SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`meeting`.`meeting_agendas`, CONSTRAINT `meeting_agendas_FK_1` FOREIGN KEY (`meeting_id`) REFERENCES `meeting_meetings` (`id`))]

EDIT 2: The generated SQL for the tables is here: http://pastebin.com/XQmM3k7S (some tables below)DROP TABLE IF EXISTS meeting_meetings;

CREATE TABLE `meeting_meetings`
(
    `id` INTEGER  NOT NULL AUTO_INCREMENT,
    `owner_id` INTEGER  NOT NULL,
    `group_id` INTEGER,
    `name` VARCHAR(255) default 'Meeting' NOT NULL,
    `completed` TINYINT default 0 NOT NULL,
    `location` VARCHAR(255) default 'Unknown' NOT NULL,
    `start` INTEGER  NOT NULL,
    `length` INTEGER default 60 NOT NULL,
    `created_at` DATETIME,
    `updated_at` DATETIME,
    PRIMARY KEY (`id`),
    INDEX `meeting_meetings_FI_1` (`owner_id`),
    CONSTRAINT `meeting_meetings_FK_1`
        FOREIGN KEY (`owner_id`)
        REFERENCES `sf_guard_user_profile` (`user_id`),
    INDEX `meeting_meetings_FI_2` (`group_id`),
    CONSTRAINT `meeting_meetings_FK_2`
        FOREIGN KEY (`group_id`)
        REFERENCES `meeting_groups` (`id`)
)Type=InnoDB;
3
phpmysqlsymfony1symfony-1.4propel
Could it be that the space in Awesome Room is causing the INSERT in meetings table to fail? – ypercubeᵀᴹ
I am pretty sure YML doesn't use quotes or anything, it is usually just plain text and propel figures out what type it should be from the schema – Pez Cuckow
Note that the error you are now getting is not the same error as your original one; this time it's for Agendas, not for Attendance. I'm guessing it's tripping over the first insert it does with the relation to PezMeeting, and the order's changed? – Matt Gibson
Well observed, I hadn't really noticed, it's the same error though, possibly I have changed the order of the files around so a different one errors first! – Pez Cuckow
Yup, I guessed that's what might have happened. I'm mostly out of suggestions, though, as I use Doctrine, not Propel... – Matt Gibson

3 Answers

1
votes

This fixtures must be in the same text document to help with saving!

0
votes

This fixtures must be in the same file. I mean fixtures from 03_meetings.yml and 09_attendance.yml

I hope this will be usefull.

0
votes

Try this in config/ProjectConfiguration.class.php. Then comment it out after data-load.

class ProjectConfiguration extends sfProjectConfiguration
{
    public function setup()
    {
        // ...
    }

    public function configureDoctrine(Doctrine_Manager $manager)
    {
        $manager->setAttribute(Doctrine_Core::ATTR_QUOTE_IDENTIFIER, true);
    } 
}