Re: [Info-ingres] Re: is there an equivavlent to auto_increment in ingres ?

"Paul White" <pwhite@xxxxxxxxxxxxxxxxx> wrote in message
news:mailman.1121821561.25060.info-ingres@xxxxxxxxxxxxxxxxxx
> The advantage of using the unique system maintained key is so you can
> differentiate between otherwise duplicate records.

So you make duplication of facts undetectable by adding a spurious and
trivially unique value. That solves the problem...how?

> Very useful if you have
> thousands of rows being inserted and you dont want to go off to a
secondary
> table to increment a unique number for every row.

Definitely, but a slightly less bad way of doing a bad thing is not a good
solution.

> I've seen some pretty ugly (is that grammatically correct?) attempts to
deal
> with this problem.
>
> -- Timestamp, put a sleep(1)in between each insert. You also need user_id,
> some unique session id in the key.
> -- tid (aaaaaarrrrgggghhhhhh!!!)
> -- Read all the records into an array, assign a number, delete the
records,
> re-insert one by one.
> -- pseudo random generator controlled by the client.

All vile, I agree.

> By the way, here is an example:
>
> create table users (
> userid table_key with system_maintained,
> username varchar(100) ) ;
>
> insert into blah (username) values ('fred');
> insert into blah (username) values ('mary');
> insert into blah (username) values ('paul');

I realize this was probably just a quick-and-dirty example to illustrate the
use of system maintained keys, but it will serve equally well to illustrate
the preferred solution:

create table users (
username varchar(100) unique ) ;

This is better for at least seven reasons: (1) it explicitly represents a
meta-fact about the business model (that is, username is intended to be
unique *and identifying*). (2) It allows the DBMS to enforce a contract on
all applications; it tells the users how the data can legitmately be used,
and it tells the programmers exactly how their applications must behave.
(3) it prevents the correctly-framed query "SELECT * FROM users WHERE
username='fred' from ever returning an incorrect result. (4) it eliminates
the need to design, code, test, debug, and document application functions
for discovering, preventing, and fixing duplications. (5) it avoids using
Ingres' proprietary and badly broken system maintained logical key types.
(6) it liberates the end-users so they can use any application they like,
not just those containing the necessary boiler-plate code mentioned in point
4 above. And (7), it eliminates the need to join referencing tables (that
have userid as a foreign key) with the users table to discover the username.

IMO, point 3 above is the clincher.

System maintained "keys" just reintroduce the pointers that relational DBMSs
were intended to banish. (They weren't left out by accident.)

Roy Hann (rhann at rationalcommerce dot com)
Rational Commerce Ltd.
www.rationalcommerce.com
"Ingres development, tuning, and training experts"


.

Relevant Pages

  • Re: Multiple Criteria and return only one match
    ... If there are no duplicate combinations (of username and the other field) you ... or you can build a distinct query on the combinations and then use that as ... and just pull their name once and show it to me. ...
    (microsoft.public.access.queries)
  • hooking into the validation summary control, adding a custom error message
    ... I have a validation summary tag in my .aspx page, ... clicks on a submit button I check the database for a duplicate ... username that the user entered into a textbox control. ...
    (microsoft.public.dotnet.languages.csharp)
  • Dictionaries
    ... stored using code similiar to the following: ... How would I also retrieve the stored Email value if a UserName is found ... to be a duplicate? ... Prev by Date: ...
    (microsoft.public.scripting.vbscript)
  • Re: PHP Access Script (free guide)
    ... > when john davis log in, he keys in his username and password ... blah blah blah'); ... > not prevent insiders to log in and kiss you security goodbye. ... Is this send as plain text? ...
    (alt.php)
  • Re: PHP Access Script (free guide)
    ... > when john davis log in, he keys in his username and password ... blah blah blah'); ... > not prevent insiders to log in and kiss you security goodbye. ... Is this send as plain text? ...
    (alt.php)