Re: [Phpwebsite-developers] Auto Increment SQL problem

[Richard S. <ri...@ri...>]

I agree identifying the auto incrementing column for MySQL and Postgres 
is easy.

The way Postgres and Oracle create auto incrementing columns are not 
different, they both use sequencing but Postgres has a wrapper for 
creating the sequence which is by setting the column to a SERIAL 
column. When you do that Postgres automatically creates all the code 
you see for creating an auto increment column on Oracle and sets the 
column to primary key if it is not.

I think it is fair to assume when creating a table and you have a 
column that is a integer and setting it to primary key that it should 
be auto incremented, and yes Oracle has a primary key field and used 
when auto incrementing.

So at any rate, I think looking for the primary key is the best idea 
however column types might now be the next issue. Postgres supports 
doing stuff like "create table test(col1 varchar(5) not null);" and 
"create table test(col1 varchar[5] not null);" but it acts a little 
different for integers (I don't know why). If you would like to specify 
a column size for an integer you have to use square brackets, eg: 
"create table test1(col1 int[5] not null);" and it wont accept "()" so 
that is just some extra parsing...

- Regards
Richard Sumilang



On Thursday, August 21, 2003, at 05:55  AM, Matthew McNaney wrote:

> On Wed, 2003-08-20 at 13:37, Richard Sumilang wrote:
>> As I looked for what is the common standard for creating auto 
>> increment
>> columns in a table I found that there isn't one...
>
>> Syntax for MySQL
>> ===========================
>> CREATE  TABLE  test (
>>   id INT NOT  NULL AUTO_INCREMENT,
>>   PRIMARY  KEY ( id)
>> );
>
>>
>> Syntax for PostgreSQL
>> ===========================
>> CREATE TABLE test (
>>   id SERIAL ,
>>   PRIMARY KEY (id)
>> );
>
> These should be easy to identify with the current process: look for an
> integer that is the primary key.
>
>>
>> Syntax for Oracle9i
>> ===========================
>> --
>> --Create sequence called demo
>> --
>> CREATE SEQUENCE "DEMO"."SEQ_INSERT" INCREMENT BY 1 START WITH
>> 1780229 MAXVALUE 1.0E28 MINVALUE 1 NOCYCLE
>> CACHE 20 ORDER
>>
>> --
>> -- Next place a trigger for that column you want to auto increment --
>> --
>> CREATE OR REPLACE TRIGGER "DEMO"."TRG_EMP_INSERT" BEFORE
>> INSERT ON "DEMO"."EMP"
>>     FOR EACH ROW BEGIN
>>
>>     SELECT seq_insert .nextval  -- get the new seq number
>>
>>       INTO :NEW.emp_no -- :NEW missed
>>       FROM DUAL;
>> END;
>>
>> # Found source for Oracle at
>> http://www.experts-exchange.com/Databases/Q_20412293.html
>
> I wouldn't even know where to begin. What are the parameters of the
> column to be auto incremented in Oracle? Is it labeled as the primary
> key?
>
> There are some other possible solutions if getting the column from
> Oracle is too difficult:
>
> 1) If you are using an auto incremented column, it must be called "id".
> Could work although I think a few might be annoyed by this.
>
> 2) A table could track the incremented columns (  TABLE_NAME | 
> ID_COLUMN
> ). It would get loaded when the database is first accessed.
> The cons here are that you would have to indicate what that column is 
> on
> install. And it would be another table accessed when the page is 
> created
> unless we session it.
>
> 3) Everytime the database object is called, the developer would specify
> a column requiring auto_incrementing.
> Another minor pain. You would just have to remember to enter it
> everytime.
>
> These are just a few suggestions, which you are welcome to add to. 
> AFAIK
> Pear does NOT have a function to figure out an incremented column, you
> have to tell it each time insert is called (which make sense).
>
> One quick note, one of the above versions MAY be better than the 
> current
> implementation. Currently, the insert function has to figure out the
> index on its own. Getting told the column right off the bat would save 
> a
> little time, although inserts are certainly less common than say 
> select.
>
>
> -- 
> Matthew McNaney
> Internet Systems Architect
> Electronic Student Services
> Appalachian State University
> Phone: 828-262-6493
> http://phpwebsite.appstate.edu
> http://ess.appstate.edu
>
>
>