Re: auto increment? 'enum' data type?

From: Remco Blaakmeer <remco_at_rd1936.quicknet.nl>
Date: 26 Jan 2000 22:55:25 GMT
Message-ID: <86nu0t$8di$2@rd1936.quicknet.nl>

In article <86nr2e$jd$1_at_nnrp1.deja.com>,

        Otis Gospodnetic <otis_at_my-deja.com> writes:
> In article <86m9ot$q99$1_at_nnrp1.deja.com>,
> M. Armaghan Saqib <armaghan_at_yahoo.com> wrote:

>> I just had a detailed answer to a similar question.
>>
>> a) Oracle does not support access/sqlserver like "Autonumber" columns
>> but supports a sequence object which you can "bind" to a table column
>> (number type) using a pre-insert trigger.

>
> I think I understand how this would be done...
> 1) create sequence
> 2) create trigger....
> Now I'm not sure about the syntax for 2)
> I'd like something like this:
> CREATE TRIGGER trigger_foo
> BEFORE INSERT
> ON mytablename
> CURRVAL + 1
>
> So that when I make 1st insert the PK of 'mytablename' gets value 1,
> and when the second row is inserted the PK gets value of 2, and so on.

See this Sql*Plus transcript for an explanation:

SQL> create table test_enum
  2 (id number(5)
  3 ,text varchar2(10)
  4 ,constraint test_enum_pk primary key(id)   5 )
  6 /

Table created.

SQL> insert into test_enum(text)
  2 values('TEST')
  3 /
insert into test_enum(text)

            *
ERROR at line 1:
ORA-01400: cannot insert NULL into ("REMCO"."TEST_ENUM"."ID")

SQL> create sequence test_enum_seq;

Sequence created.

SQL> create trigger test_enum_trigger
  2 before insert
  3 on test_enum
  4 for each row
  5 begin

  6     select test_enum_seq.nextval
  7     into :new.id
  8     from dual;

  9 end;
 10 /

Trigger created.

SQL> insert into test_enum(text)
  2 values('TEST')
  3 /

1 row created.

SQL> select *
  2 from test_enum
  3 /

        ID TEXT
---------- ----------

         1 TEST Look at your Oracle SQL Reference for a detailed explanation of the commands. You can view the SQL Reference (and other documentation) for various versions of de database server online at http://technet.oracle.com/

> I'm surprised there is no simpler, one-step thing/keyword to use for
> this, since it is such a common requirement.

I think it has something to with multi-user issues. If you fetch a value from a sequence, you are guaranteed to be the only one that gets this value from this particular sequence. If you would want to create a mechanism that calculates max(id)+1 in a multi-user environment, you could easily get duplicate values and it would be very difficult to prevent them.

> Got it.
> Synax question.
> What does specifying 'CONSTRAINT constraint_name CHECK ...' buy me over
> just saying 'CHECK ...'
>
> e.g.
> CONTSTRAINT check_gender CHECK (loc IN ('Male','Female'))
> vs.
> CHECK (loc IN ('Male','Female'))
>
> What does the first way of writing get me?

You get a constraint with a meaningful name. This is very useful when you get an error about a constraint being violated. For example, the phrase "unique constraint (REMCO.TEST_ENUM_PK) violated" is much easier to understand at first glance than "unique constraint (SYS_C00266) violated". The former immediately tells me that it involves creating a duplicate value in the primary key on test_enum, the latter tells me nothing except that I made some mistake and I still have to find out what went wrong.

Just like it is wise to give tables a meaningful name, it is wise to give indexes, sequences, constraints, etc. meaningful names.

Remco
--
rd1936: 11:35pm up 18 days, 4:15, 10 users, load average: 2.69, 2.71, 2.39 Received on Wed Jan 26 2000 - 16:55:25 CST