symfony framework forum: symfony 1.2 => Problem with insertion in table with two foreigns

This forum is in READ-ONLY mode.
You can look around, but if you want to ask a new question, please use the new forum.

Members

FAQ

Home

Home » legacy support » symfony 1.2 » Problem with insertion in table with two foreigns

Show: Today's Messages :: Show Polls :: Message Navigator

Problem with insertion in table with two foreigns [message #87942]

Fri, 06 November 2009 22:01

masterix
Messages: 41
Registered: September 2008

Member

Hi,

I have a table:

  order:
    id: ~
    user_id: { type: integer, foreignTable: sf_guard_user, foreignReference: id, required: true }
    company_id: { type: integer, required: true, foreignTable: sf_guard_user, foreignReference: id }

Unfortunately when I try to add a new record in module with admin-generator ( generated form ) I get such error:

Quote:

Unable to execute INSERT statement. [wrapped: SQLSTATE[42000]: Syntax error or access violation: 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 'order (`ID`,`USER_ID`,`COMPANY_ID`) VALUES (NULL,6,9)' at line 1]

I suppose it is connected with two foreign keys to the same table. I use propel and there are some users in DB. How can I solve this problem?

Regards,
masterix

[Updated on: Fri, 06 November 2009 22:04]

Report message to a moderator

Re: Problem with insertion in table with two foreigns [message #87944 is a reply to message #87942 ]

Fri, 06 November 2009 22:22

xplo
Messages: 428
Registered: September 2008

Faithful Member

your yml is wrong

search on this forum for composed or combined foreign key

Report message to a moderator

Re: Problem with insertion in table with two foreigns [message #87945 is a reply to message #87944 ]

Fri, 06 November 2009 22:31

masterix
Messages: 41
Registered: September 2008

Member

I made a fast search but found nothing what would give me a hint. Could you explain what's wrong with my yml file?

Regards,
masterix

Report message to a moderator

Re: Problem with insertion in table with two foreigns [message #87955 is a reply to message #87942 ]

Sat, 07 November 2009 11:06

mugen
Messages: 52
Registered: October 2009

Member

hello,

LOL xplo wan't say on your yml you have two foreignTable: sf_guard_user

Report message to a moderator

Re: Problem with insertion in table with two foreigns [message #87958 is a reply to message #87955 ]

Sat, 07 November 2009 11:12

masterix
Messages: 41
Registered: September 2008

Member

Yes. And I need them both. Two foreign keys to the same table.

Report message to a moderator

Re: Problem with insertion in table with two foreigns [message #87960 is a reply to message #87958 ]

Sat, 07 November 2009 12:56

mugen
Messages: 52
Registered: October 2009

Member

on your request sql the autoincrement does not work. you have null value show you sql data/sql/model.schema.sql and look table order if you have autoincrement on id filed

Report message to a moderator

Re: Problem with insertion in table with two foreigns [message #87961 is a reply to message #87960 ]

Sat, 07 November 2009 13:00

masterix
Messages: 41
Registered: September 2008

Member

It is.

Quote:

#----------------------------------------------------------- ------------------
#-- order
#----------------------------------------------------------- ------------------

DROP TABLE IF EXISTS `order`;

CREATE TABLE `order`
(
`id` INTEGER NOT NULL AUTO_INCREMENT,
`user_id` INTEGER NOT NULL,
`company_id` INTEGER NOT NULL,
PRIMARY KEY (`id`),
INDEX `order_FI_1` (`user_id`),
CONSTRAINT `order_FK_1`
FOREIGN KEY (`user_id`)
REFERENCES `sf_guard_user` (`id`),
INDEX `order_FI_2` (`company_id`),
CONSTRAINT `order_FK_2`
FOREIGN KEY (`company_id`)
REFERENCES `sf_guard_user` (`id`)
)Type=InnoDB;

Report message to a moderator

Re: Problem with insertion in table with two foreigns [message #87968 is a reply to message #87955 ]

Sat, 07 November 2009 15:16

mugen
Messages: 52
Registered: October 2009

Member

debug propel or doctrine orm and show why you have null value.

your request must be like this :

'order (`USER_ID`,`COMPANY_ID`) VALUES (6,9)'

Report message to a moderator

Re: Problem with insertion in table with two foreigns [message #87981 is a reply to message #87968 ]

Sat, 07 November 2009 19:56

xplo
Messages: 428
Registered: September 2008

Faithful Member

this forum search engine is not good Sad

there you go :

_foreignKeys: { FK_sf_guard_user_composite: { foreignTable: sf_guard_user, references: [{ local: user_id, foreign: id}, { local: company_id, foreign: id }] } }

I didnt see the 2 column goes on the same column in the foreign table. I never had this case, i hope it works ~

Edit : it goes just after your column definition and you need to remove the foreignTable: sf_guard_user, foreignReference: id from your column description

[Updated on: Sat, 07 November 2009 19:58]

Report message to a moderator

Re: Problem with insertion in table with two foreigns [message #87984 is a reply to message #87942 ]

Sat, 07 November 2009 20:13

masterix
Messages: 41
Registered: September 2008

Member

Unfortunately I'm getting such error:

Quote:

CREATE TABLE `order`
(
`id` INTEGER NOT NULL AUTO_INCREMENT,
`user_id` INTEGER NOT NULL,
`company_id` INTEGER NOT NULL,
PRIMARY KEY (`id`),
INDEX `FI_sf_guard_user_composite` (`user_id`,`company_id`),
CONSTRAINT `FK_sf_guard_user_composite`
FOREIGN KEY (`user_id`,`company_id`)
REFERENCES `sf_guard_user` (`id`,`id`)
)Type=InnoDB
[propel-sql-exec] SQLSTATE[HY000]: General error: 1005 Can't create table './jedz/order.frm' (errno: 150)

I tried to find something in google but it didn't help. SHOW ENGINE INNODB STATUS command tells:

Quote:

LATEST FOREIGN KEY ERROR
------------------------
091107 20:02:20 Error in foreign key constraint of table jedz/order:

FOREIGN KEY (`user_id`,`company_id`)
REFERENCES `sf_guard_user` (`id`,`id`)
)Type=InnoDB:
Cannot find an index in the referenced table where the
referenced columns appear as the first columns, or column types
in the table and the referenced table do not match for constraint.
Note that the internal storage type of ENUM and SET changed in
tables created with >= InnoDB-4.1.12, and such columns in old tables
cannot be referenced by such columns in new tables.
See http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-co nstraints.html
for correct foreign key definition.

But ID in sf_user_guard is the primary key so it's indexed...

No idea what to do now, any hints?

In general it created one foreign key but I need two of them actually. Because they are separate.

@mugen: I have new order form with following input:

<input type="hidden" name="order[id]" id="order_id" />

Should it be there?

Regards,
masterix

[Updated on: Sat, 07 November 2009 20:19]

Report message to a moderator

Re: Problem with insertion in table with two foreigns [message #88845 is a reply to message #87942 ]

Sun, 22 November 2009 17:18

masterix
Messages: 41
Registered: September 2008

Member

I still can't solve the problem. I've noticed that if I use phpMyAdmin do add a record to order table there are quotes added to integer values whereas propel has no quotes in sql query. Why?

INSERT INTO `order` (`id`, `user_id`, `company_id`) VALUES (NULL, '4', '2');

I think there shouldn't be any quotes.

Regards,
masterix

Report message to a moderator

Previous Topic:	sfValidatorPropelUnique doesn't permit NULL
Next Topic:	Propel outer join

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ]