unable to add new entries on form with auto_increment option

[bsafarian]

Background:

Package: Windows Installer 111M - downloaded early on Jan 2011from the Download page.

I have created a simple form called "usr_form" which is bounded to a "users" table. This table has a primary key with auto_increment option on it:

use dcr;

drop table if exists users;

CREATE TABLE if not exists users
(userid int not null auto_increment,
primary key (userid),
usr_firstname varchar(30) not null,
usr_lastname varchar(30) not null,
usr_wrk_number varchar(15),
usr_wrk_no_extn varchar(10),
usr_cell_number varchar(15),
usr_email varchar(35),
usr_group varchar(30),
usr_created datetime,
status varchar(30),
mgr_firstname varchar(30) not null,
mgr_lastname varchar(30) not null,
mgr_wrk_number varchar(15),
mgr_wrk_no_extn varchar(10),
mgr_cell_number varchar(15),
mgr_email varchar(35)
) engine=innodb;

I have created two objects on the usr_form just to test it. Object1 is bounded to firstname and object2 to lastname. I was able to add the first entry successfully however when I tried to add the 2nd one I got the following exception:

SQL
INSERT INTO users (userid, usr_firstname, usr_lastname) VALUES ("14d3a093bbddd0", 'c', 'c')

Error
1062: Duplicate entry '14' for key 'PRIMARY'

Just an FYI, my first entry was:

userid=14
firstname=Bob
lastname=Safarian

So it appears nuBuilder is trying to insert an alphanumeric value into the userid with INT data type. To me nuBuilder should have generated an insert statement as the following:

INSERT INTO users (usr_firstname, usr_lastname) VALUES ('c', 'c');

and let MySQL automatically generate and fill in the userid.

Could you please explain this behavior and how to workaround it?


Thanks,
Bob Safarian

[admin]

Bob,
Thanks for asking your questions here in the forum.

The way we have designed nuBuilder is to only use varchar primary keys and not autoincremented keys.
To explain some of the reasons for this you could have a look at this blog
http://nubuilder.blogspot.com/2010/09/n ... -keys.html

The reason you are getting the duplicate 14 is - as you guessed - because nuBuilder is trying to put "14d3a093bbddd0" (which is created by php's uniqid() function) in a number field and so only holds the left most number (14)d3a093bbddd0.


I noticed you have chosen to use innodb as a table type, at this point, we haven't tested innodb. We use MyISAM so I'll be interested to how you go with it.

regards

Steven

[bsafarian]

Steven,

I am not very familiar with MySQL and not sure if auto_increment can be started from any starting point as Sequences do in Oracle: e.g.

employee_id: start with 10000 and increment by 1 onwards
dept_id: start with 10 and increment by 1 onwards
company_id: start with 100 and increment by 1 onwards

Now assuming these recordsets wont grow enormously then each id is pretty much distinguishable from others. I like the idea of being able to predicate PKs incrementation as opposed to a cryptic number generated by uniquid() which does not provide any sort of characteristics of the table it belongs to. But I guess I can live with uniquid() as well. Do you happen to know if a PK can be defaulted to uniquid() value in the table definition as well?

I used innodb table type to enforce Referential Integrity which apparently not available in MyISAM. I like the speed of MyISAM but data integrity is more important for me.

Thanks,
Bob

[murz]

I use nuBuilder to do actions on already created database, so at now it have int and auto_increment primary keys and it is too hard to change this, because other applications work with this database too.
And in tables with large amount of rows, with uniqid() may be collisions with same id, but auto_increment don't have this problem.

And another positive side of integer primary keys is speed: sort and compare integer values costs much less cpu time instead of varchar(15) vaules, so I think that nuBuilder must operate correctly with integer auto_increment primary keys.

[admin]

Bob,

auto_increment can be started from a certain number (but not within nuBuilder).

uniqid() is a php function that creates unique strings that are alphabetically sequential.
nuBuilder uses it to create ids, I dont believe mysql can do it without nuBuilder.

At this point, as its not made for innodb, there is no commit or rollback built into nuBuilder, so commits will be automatic and rollbacks are not currently available.


Steve

[admin]

murz,

We still believe, given the choice, a unique string is better than autoIDs, but I understand your position and it will be eventually be something we will add. (its in our list)

Steve