Flourish PHP Unframework
This is an archived copy of the forum for reference purposes

Problem with autoincrement in no primary key

posted by aris 9 years ago

I have found a problem with the autoincrement (serial) field in the following table:

CREATE TABLE user_group (
    id              serial,
    id_user         integer NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    id_group        integer NOT NULL REFERENCES groups(id) ON DELETE CASCADE,
    role            varchar(32) NOT NULL,
    created         timestamp DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id_user, id_group)
);

I'm using the following code (in Group model) to create a new group and related usergroup:

...
// Define a user-group link
$usergroup = new UserGroup();
$usergroup->setIdUser ($user->getId());
$usergroup->setRole ('owner');
                        
// And save the new group
$this->associateUserGroups($usergroup);
$this->store();
...

But for some reason the store action attempts to fill the serial field as well, so I get the following error:

Fatal error: Uncaught exception 'fSQLException' with message 'PostgreSQL error (ERROR: null value in column "id" violates not-null constraint) in INSERT INTO "user_groups" ("id", "id_user", "id_group", "role", "created") VALUES (NULL, 1, 1, 'myuser@my.net', 'member', '2010-06-08 19:15:29')' in /usr/local/share/php/flourish/classes/fDatabase.php:409

The output of fCore::expose($schema->getColumnInfo('user_groups')) about that 'id' field is as follows:

    [id] => Array
        (
            [type] => integer
            [min_value] => fNumber Object
                (
                    ["scale":"fNumber":private] => 0
                    ["value":"fNumber":private] => -2147483648
                )
        
            [max_value] => fNumber Object
                (
                    ["scale":"fNumber":private] => 0
                    ["value":"fNumber":private] => +2147483647
                )
        
            [auto_increment] => {true}
            [not_null] => {true}
            [placeholder] => %i
            [default] => {null}
            [valid_values] => {null}
            [max_length] => {null}
            [decimal_places] => {null}
        )

By the way, defining the 'id' field as primary key (instead of the id_user, id_group pair), all goes with no problem. Currently using PostgreSQL 8.4.4.

Any help?

A.

Yes, that appears to be a bug. Sometimes it takes me a little while to dig through the backlog of messages on the forum, so if you think you have found a bug, please feel free to open a ticket. Those are all tracked through a nice interface and I try to make sure all defects are fixed as quickly as I have time.

posted by wbond 9 years ago