Skip to main content ↑ ↓
Topic: Cannot update record for table with 2 primary key fields (Read 2671 times) previous topic - next topic

Cannot update record for table with 2 primary key fields

I am using PHPRad Classic 2.6.9.

One of my tables is "orderdetails" has two fields for the primary key (OrderID and ProductID), which has table schema as follows:

Code: [Select]
CREATE TABLE `orderdetails` (
  `OrderID` int(11) NOT NULL DEFAULT '0',
  `ProductID` int(11) NOT NULL DEFAULT '0',
  `UnitPrice` double DEFAULT NULL,
  `Quantity` smallint(6) DEFAULT NULL,
  `Discount` double DEFAULT NULL,
  PRIMARY KEY (`OrderID`,`ProductID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of orderdetails
-- ----------------------------
INSERT INTO `orderdetails` VALUES (10248, 41, 9.65, 13, 0);
INSERT INTO `orderdetails` VALUES (10248, 42, 9.8, 10, 0);
INSERT INTO `orderdetails` VALUES (10248, 72, 34.8, 12, 0.11);
INSERT INTO `orderdetails` VALUES (10249, 14, 18.6, 12, 0.25);
INSERT INTO `orderdetails` VALUES (10249, 51, 42.4, 40, 0);
INSERT INTO `orderdetails` VALUES (10250, 41, 7.7, 10, 0);
INSERT INTO `orderdetails` VALUES (10250, 51, 42.4, 35, 0.15);
INSERT INTO `orderdetails` VALUES (10250, 65, 16.8, 15, 0.15);
INSERT INTO `orderdetails` VALUES (10251, 22, 16.8, 6, 0.05);
INSERT INTO `orderdetails` VALUES (10251, 57, 15.6, 15, 0.05);


When I edit one of the records with primary key OrderID 10248 and ProductID 41 with the following URL:
http://localhost/rad_test/orderdetails/edit/10248/?csrf_token=a657dd233d546b205e8eea53a48cec3d

I got this error:

Quote
Error 500
Server Error

Exception Traces
This will only be displayed in DEVELOPMENT_MODE.
Error Message   SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '10248-41' for key 'PRIMARY'
File   D:\wamp\www\rad_test\app\models\PDODb.php On Line 1864
Stack Trace   1 D:\wamp\www\rad_test\app\models\PDODb.php(1864): PDOStatement->execute()
2 D:\wamp\www\rad_test\app\controllers\OrderdetailsController.php(187): PDODb->update('orderdetails', Array)
3 D:\wamp\www\rad_test\system\Router.php(205): OrderdetailsController->edit('10248')
4 D:\wamp\www\rad_test\system\Router.php(94): Router->run('orderdetails/edit/10248/')
5 D:\wamp\www\rad_test\index.php(100): Router->init()
Please contact system administrator

It seems PHPRad has not handled if the primary key fields are more than one field. Am I right?

Thoughts?

Re: Cannot update record for table with 2 primary key fields

Reply #1 –
@Masino Sinaga,‍ it is currently not supported, but you can do a combination of both columns and store it in a separate column and make it your primary key. if you notice from the link(.../10248/...) phprad uses only one of the columns, which is OrderID and when an update is done, it checks for a record where OrderID is equal to 10248 and it finds more than one record.

Re: Cannot update record for table with 2 primary key fields

Reply #2 –
So, it is currently NOT supported now.

I don't think combining both fields into another field and set it up as Primary Key is a good idea, because in a real world, there are many tables that have multiple fields as Primary Key, not only two fields, sometimes the tables have three, four, or five fields as Primary Keys. Combining all those fields into one field is neither impractical nor effective.

Do you have a plan to support multiple fields for the Primary Key? Because PHPMaker can do this nicely. PHPRad should support this feature, too.