Composite key uniqueless validation

Question

Composite key uniqueless validation

Aleksey Skripka May '17

Created May '17	Last Reply May '17	Replies 2	Views 748	Votes 1

Aleksey Skripka 4.1k

May '17

Hi there

I have the unique composite key in the table, consists of fields ('position', 'controller', 'action')

In the model I registered:

public function validation() {
     $validator = new Phalcon\Validation();
     $validator->add(
         [
             "position",
             "controller",
             "action",
         ],
         new Phalcon\Validation\Validator\Uniqueness()
     );
     return $this->validate($validator);
}

Validation of a composite key does not work. Displays:

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '1-admin_access-' for key 'position_controller_action'

Apparently for each field separately checks and not on all together. How do I validate a composite key in a model?

The solution from this topic is NOT appropriate: https://forum.phalcon.io/discussion/4205/phalcon-model-validate-uniqueness-by-two-fields

public function validation()
{
    $this->validate(new Uniqueness(array(
        'field' => array('email', 'name')
    )));
}

Not suitable, because the Validate method should accept \ Phalcon \ ValidationInterface which in turn takes validation rules via the method:

public function add($field, \Phalcon\Validation\ValidatorInterface $validator) {}

How do I validate a unique composite key in the model?

1

Wojciech Ślawski · Answer 1 · 2017-05-10T00:47:16-07:00

What phalcon version you use? Your exact code you posted should work without problems. I have code like this:

$this->_validation->add(['instrumentId', 'teacherId'], new Uniqueness());

works without any issue.

How exactly you insert your model? With what data?

Here is even unit test for this - https://github.com/phalcon/cphalcon/blob/master/tests/unit/Validation/Validator/UniquenessTest.php#L118

Aleksey Skripka · Answer 2 · 2017-05-10T14:04:23-07:00

What phalcon version you use? Your exact code you posted should work without problems. I have code like this:

Phalcon DevTools Version 3.1.2 Phalcon Version 3.1.2

$this->_validation->add(['instrumentId', 'teacherId'], new Uniqueness());
works without any issue. How exactly you insert your model? With what data?

Model generated with Phalcon Webtools. I use standart method "public function validation()"

Data:

CREATE TABLE `t_account_admin_access` (
  `id` int(11) NOT NULL,
  `position` int(11) NOT NULL,
  `controller` varchar(100) NOT NULL,
  `action` varchar(100) NOT NULL DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `t_account_admin_access`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `position_controller_action` (`position`,`controller`,`action`) USING BTREE;

ALTER TABLE `t_account_admin_access`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=31;

ALTER TABLE `t_account_admin_access`
  ADD CONSTRAINT `t_account_admin_access_ibfk_1` FOREIGN KEY (`position`) REFERENCES `t_account_positions` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

Here is even unit test for this - https://github.com/phalcon/cphalcon/blob/master/tests/unit/Validation/Validator/UniquenessTest.php#L118

Wojciech Ślawski · Answer 3 · 2017-05-11T00:01:39-07:00

By data i mean what you are saving as position, controller and action.

Maybe check sql log if proper query is being made?