Re: auto increment? 'enum' data type?

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.

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

> b) Oracle does support this type of functionality using CHECK
> constraints on a table.

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?

> My SQL PlusPlus (freeware) generated all the code required for both of
> above.
>
> BLDSEQ command will generate code to create sequence and the required
> pre-insert trigger.
>
> BLDCON command generates an example of CHECK constraint which you can
> modify to suit your requirement.

Aha!
I'll get it.
Thanks,

Otis

> in article <86lv65$jfu$1_at_nnrp1.deja.com>,
> Otis Gospodnetic <otis_at_my-deja.com> wrote:
> > Hi,
> >
> > I'm new to Oracle and am wondering what's the best/easiest way to
> make a
> > column 'auto increment' (e.g. an ID column used as a primary key)?
> > Do I have to make a sequence in my schema for that particular
> > column/table/database? Or can I specify something like 'AUTO
> INCREMENT'
> > when I do CREATE TABLE .... (doesn't seem to be the case).
> >
> > I am also wondering if there is a data type that will let me
specify a
> > list of all possible values that a column can have, so that no other
> > value can be inserted in that column?
> > In MySQL I would do somehting like this, for example:
> > gender enum('Male','Female')
> > How about under Oracle?
> >
> > Thanks,
> >
> > Otis
> > P.S.
> > I ordered an Oracle book, it's just hasn't been delivered yet...
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Jan 26 2000 - 16:05:06 CST