Solved thread

This post is marked as solved. If you think the information contained on this thread must be part of the official documentation, please contribute submitting a pull request to its repository.

Insert into sql server database failed

Hello, I'm using phalcon with sql server as database and trying to insert a record into the database failed.I got this error: DEFAULT or NULL are not allowed as explicit identity values. when i debug by displaying the query i notice that phalcon send by default auto-increment field as null to the database :

2 Phalcon\Db\Adapter\Pdo->execute(INSERT INTO "tablea" ("id", "testa") VALUES (null, ?), Array([0] => 86))

Is it possible to modify phalcon model to not send (not to use id ) auto increment field at insertion to the database (eg: INSERT INTO "tablea" ("testa") VALUES ( ?)) .

Thanks



2.9k
Accepted
answer

According to the docs

Some models may have identity columns. These columns usually are the primary key of the mapped table. Phalcon\Mvc\Model can recognize the identity column omitting it in the generated SQL INSERT, so the database system can generate an auto-generated value for it. Always after creating a record, the identity field will be registered with the value generated in the database system for it.

And further on

Phalcon\Mvc\Model is able to recognize the identity column. Depending on the database system, those columns may be serial columns like in PostgreSQL or auto_increment columns in the case of MySQL.

We can then tell the ORM to skip columns in the initialize() method - https://docs.phalconphp.com/en/3.0.2/reference/models-advanced.html#skipping-columns

This would make your model become something like (using skipAttributesOnCreate());

class Robots extends Model
{
    public function initialize()
    {
        // Skips only when inserting
        $this->skipAttributesOnCreate(
            [
                "id",
            ]
        );
    }
}


169

Thanks you, It works



2.9k

Awesome :)

Thanks you, It works

edited Apr '17

That's weird, are you sure that your id column is primary key?



2.9k

I think OP is using MSSQL and the docs just say it's auto-recognised for Postgres and MySQL - would that make a difference?

That's weird, are you sure that your id column is primary key?

Well not sure, mssql is supported in incubator right? Maybe it's issue? If it's indeed primary key then it shouldn't really happen.



2.9k
edited Apr '17

Yep, IIRC it is supported with the incubator.

I agree with "if it's indeed a primary key then it shouldn't really happen", but until OP can confirm, I won't make it a priority to look into - plus I don't like MSSQL so I don't have a test environment setup for it. But, OP has a workaround for now.

Well not sure, mssql is supported in incubator right? Maybe it's issue? If it's indeed primary key then it shouldn't really happen.