Re: [Phpwebsite-developers] Auto Increment SQL problem

[Matthew M. <ma...@tu...>]

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