RESOLVED - Update of table with 2 primary keys doesn't work

CodeCharge Studio

Web Reports

Visually create Web Reports in PHP, ASP, .NET, Java, Perl and ColdFusion.
CodeCharge.com

YesSoftware Forums -> CodeCharge Studio -> PHP

RESOLVED - Update of table with 2 primary keys doesn't work

Author

Message

rado

Posts: 221

Posted: 11/21/2008, 5:49 AM

I had need to change my db structure and add additional primary key in one table. From that time everything works fine (insert, delete) however when I run update I got DB error with message "duplicate entries (<first and second primary key>).
I tried to run custom update where I found that WHERE clause has just my first primary key. I added my second primary key and when I run update I got message that my "update and delete has been missconfigured"

Any idea?

Thanks Rado

rado

Posts: 221

Posted: 11/21/2008, 8:42 AM

Anybody willng to help??

Gena

Posts: 591

Posted: 11/21/2008, 8:52 AM

Something wrong with your database! A table can have only one PRIMARY KEY!

so check it.
_________________
Gena

rado

Posts: 221

Posted: 11/21/2008, 9:06 AM

Soryy, One is primary and second is foreign key .

Thanks, Rado

Gena

Posts: 591

Posted: 11/21/2008, 9:27 AM

Are you sure that your primary fields are primed well?

could you post your sql structure here?
_________________
Gena

rado

Posts: 221

Posted: 11/21/2008, 1:52 PM

CREATE  TABLE IF NOT EXISTS `rr_db`.`units` (     
`unit_number` INT(10) UNSIGNED NOT NULL ,     
`Properties_prop_id` INT(10) UNSIGNED NOT NULL ,     
`unit_sqf` INT(10) UNSIGNED NULL ,     
`unit_floor_number` INT(10) UNSIGNED NULL ,     
`unit_number_of_rooms` INT(10) UNSIGNED NULL ,     
`unit_key_number` INT(10) UNSIGNED NULL ,     
`unit_number_of_bathrooms` INT(10) UNSIGNED NULL ,     
`unit_service_loundry` BOOLEAN NULL ,     
`unit_service_cable` BOOLEAN NULL ,     
`unit_service_phone` BOOLEAN NULL ,     
`unit_service_camera` BOOLEAN NULL ,     
`unit_service_ac` BOOLEAN NULL ,     
`unit_service_dishwasher` BOOLEAN NULL ,     
`unit_service_parking` BOOLEAN NULL ,     
`UnitTypes_unit_type_id` INT(10) UNSIGNED NOT NULL ,     
PRIMARY KEY (`unit_number`, `Properties_prop_id`) ,     
INDEX `fk_units_properties` (`Properties_prop_id` ASC) ,     
INDEX `fk_units_unit_types` (`UnitTypes_unit_type_id` ASC) ,     
CONSTRAINT `fk_units_properties`     
  FOREIGN KEY (`Properties_prop_id` )    
  REFERENCES `rr_db`.`properties` (`prop_id` )    
  ON DELETE NO ACTION     
  ON UPDATE NO ACTION,     
CONSTRAINT `fk_units_unit_types`    
   FOREIGN KEY (`UnitTypes_unit_type_id` )     
  REFERENCES `rr_db`.`unit_types` (`unit_type_id` )     
  ON DELETE RESTRICT     
  ON UPDATE CASCADE)   
ENGINE = InnoDB

Gena

Posts: 591

Posted: 11/21/2008, 1:59 PM

I think your problem caused from relation in your table definition. I have never used relations and had never had this kind of problem. Try to delete all relations.
_________________
Gena

andy

Posts: 183

Posted: 11/22/2008, 11:46 AM

I had a similar problem and finally decided to remove foreign keys and replicate the foreign key actions using code.
Not ideal but the foreign keys seemed to impose constraints.
_________________
Andy

RAD tools for rich UI controls:
http://www.koolphptools.com

rado

Posts: 221

Posted: 11/23/2008, 10:28 AM

I tried so many different combination (one of them is removing relationships between tables) and it didn't work until I found that main grid which serving data to record doesn't include (query doesn't select) my second primary key. (It looks like that grid and record builder is able to manage just one primary key by default). Once I added additional link parameter (my second primary key ) to Href Source link, finally I got all correct data in my record and update now works just fine.

Thanks to everybody who tried to help me.

Rado

These are Community Forums for users to exchange information.
If you would like to obtain technical product help please visit http://support.yessoftware.com.

Internet Database

Visually create Web enabled database applications in minutes.
CodeCharge.com

Home | Search | Members | Register | Login