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.
Home » legacy support » symfony 1.2 » Problem with insertion in table with two foreigns
Problem with insertion in table with two foreigns [message #87942] Fri, 06 November 2009 22:01 Go to next message
masterix  is currently offline 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]

Re: Problem with insertion in table with two foreigns [message #87944 is a reply to message #87942 ] Fri, 06 November 2009 22:22 Go to previous messageGo to next message
xplo  is currently offline xplo
Messages: 428
Registered: September 2008
Faithful Member
your yml is wrong

search on this forum for composed or combined foreign key
Re: Problem with insertion in table with two foreigns [message #87945 is a reply to message #87944 ] Fri, 06 November 2009 22:31 Go to previous messageGo to next message
masterix  is currently offline 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
Re: Problem with insertion in table with two foreigns [message #87955 is a reply to message #87942 ] Sat, 07 November 2009 11:06 Go to previous messageGo to next message
mugen  is currently offline mugen
Messages: 52
Registered: October 2009
Member
hello,


LOL xplo wan't say on your yml you have two foreignTable: sf_guard_user
Re: Problem with insertion in table with two foreigns [message #87958 is a reply to message #87955 ] Sat, 07 November 2009 11:12 Go to previous messageGo to next message
masterix  is currently offline masterix
Messages: 41
Registered: September 2008
Member
Yes. And I need them both. Two foreign keys to the same table.
Re: Problem with insertion in table with two foreigns [message #87960 is a reply to message #87958 ] Sat, 07 November 2009 12:56 Go to previous messageGo to next message
mugen  is currently offline 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
Re: Problem with insertion in table with two foreigns [message #87961 is a reply to message #87960 ] Sat, 07 November 2009 13:00 Go to previous messageGo to next message
masterix  is currently offline 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;
Re: Problem with insertion in table with two foreigns [message #87968 is a reply to message #87955 ] Sat, 07 November 2009 15:16 Go to previous messageGo to next message
mugen  is currently offline 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)'
Re: Problem with insertion in table with two foreigns [message #87981 is a reply to message #87968 ] Sat, 07 November 2009 19:56 Go to previous messageGo to next message
xplo  is currently offline 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]

Re: Problem with insertion in table with two foreigns [message #87984 is a reply to message #87942 ] Sat, 07 November 2009 20:13 Go to previous messageGo to next message
masterix  is currently offline 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]

Re: Problem with insertion in table with two foreigns [message #88845 is a reply to message #87942 ] Sun, 22 November 2009 17:18 Go to previous message
masterix  is currently offline 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
Previous Topic:sfValidatorPropelUnique doesn't permit NULL
Next Topic:Propel outer join
Goto Forum:
  

powered by FUDforum - copyright ©2001-2004 FUD Forum Bulletin Board Software