Re: Sequences?

I'm not concerned with the number of a keystrokes but rather the fact that IDENTITY column implemented in the following way CREATE SEQUENCE "GB"."ANSWERS_TB_SEQUENCE"; CREATE TRIGGER "GB"."ANSWERS_TB_TRIGGER" before insert on "GB"."ANSWERS_TB"
for each row
when (new.ID is NULL)
begin

        select "GB"."ANSWERS_TB_SEQUENCE".nextval into :new.ID from dual; end;

can not be NOT NULL and therefore can not be PRIMARY KEY. What a nonsence !!!

If I remove when (new.ID is NULL) as somebody suggested I get ERROR at line 1:
ORA-00001: unique constraint (GB.SYS_C001584) violated meaning that it also can't be UNIQUE.

All in all inmplementing idintity on Oracle is terrible pain in the ass !

BTW, any suggestions how to solve the above problems ?

"Heinz Kiosk" <no.spam_at_ntlworld.com> wrote in message news:<mkMc8.91770$as2.14795589_at_news6-win.server.ntlworld.com>...
> Steady Tony, that keystroke list just gets longer and longer <G>
>
> "Tony Berrington" <tony.berrington_at_bt.com> wrote in message
> news:3C73821C.DC0633DF_at_bt.com...
> > What happens when someone has the bright idea of saving time by performing
> direct loads?
> >
> > damorgan wrote:
> >
> > > I disagree with your premise. What most developers do when there are
> multiple sources of
> > > import is write a before-insert trigger. That way there is no way for
> the sequence to
> > > not be used appropriately. Now I'll grant it is a few more keystrokes
> than 29 ... but
> > > then again it looks a lot better on your resume too so you get a
> payback.
> > >
> > > PS: There is no perfect database just as there is no other perfect tool.
> I don't care
> > > how wonderful a hammer is ... you can't use it to unscrew a bolt.
> > >
> > > Daniel Morgan
> > >
> > > Tony Berrington wrote:
> > >
> > > > Daniel,
> > > > I don't think the point is the difficulty of creating the
> sequence in the
> > > > first place, or in using it.
> > > >
> > > > The big problem arises when you have several potential sources of
> inserts/updates to
> > > > your data. You cannot guarantee that they will all use the sequence
> (and some may
> > > > actually not be able to).
> > > >
> > > > If you are using it for something as important as key generation, then
> you have a
> > > > very significant issue.
> > > >
> > > > By the way, I am an Oracle fan myself. It's a very good database, but
> not a perfect
> > > > one.
> > > >
> > > > Tony Berrington
> > > >
> > > > damorgan wrote:
> > > >
> > > > > That you haven't needed the capabilities provided by Oracle's
> sequences is fine.
> > > > > Some people, many people perhaps, don't. But some of us do. And for
> us ...
> > > > > autonumbering would be useless. So are you saying you'd rather have
> something
> > > > > that limits you to only one possible usage ... or would you rather
> have
> > > > > something robust, flexible, and scalable?
> > > > >
> > > > > But I must state that I find the amount of whining over this to be
> amazing. Is
> > > > > creating and using a SEQUENCE more difficult than creating an
> autonumbered
> > > > > column? Lets see:
> > > > >
> > > > > SQL> CREATE SEQUENCE s;
> > > > >
> > > > > The total characters typed was 18 including spaces and the
> semicolon. that
> > > > > created the sequence. I'm a bit sore but I'll continue.
> > > > >
> > > > > Now I'll add the sequence number to the insert statement for the
> table:
> > > > >
> > > > > s.NEXTVAL,
> > > > >
> > > > > Well that took a total of 10 keystrokes including the comma at the
> end. Probably
> > > > > 11 if you hit the space bar before continuing with the rest of the
> insert
> > > > > statement.
> > > > >
> > > > > A total of 29 keystrokes.
> > > > >
> > > > > Damn I guess you are correct ... my fingers are hurting and I'm
> developing some
> > > > > kind of syndrome that is going to require extensive use of pain
> medication,
> > > > > antiinflammatory steroids, and physical therapy.
> > > > >
> > > > > Daniel Morgan
> > > > >
> > > > > Heinz Kiosk wrote:
> > > > >
> > > > > > > There is no relationship between MS Access's autonumbering and a
> SEQUENCE.
> The
> > > > > > > autonumering is part of the table and is only capable of
> sequential
> numbering.
> > > > > > > It has no flexibility, no programmability, and is strictly tied
> to a
> single
> > > > > > > table. All it is is the following code built in and hidden from
> the end
> user.
> > > > > > >
> > > > > > > SELECT MAX(numbering_field)
> > > > > > > INTO next_number
> > > > > > > FROM xyz;
> > > > > > >
> > > > > > > INSERT INTO xyz
> > > > > > > (numbering_field + 1, other_field1, other_field2)
> > > > > > > VALUES
> > > > > > > (next_number, someval1, someval2);
> > > > > > >
> > > > > > > Daniel Morgan
> > > > > >
> > > > > > Methinks Daniel doth protest too much ;). "All Autonumber/Identity
> is" is
> > > > > > something exteremely useful that answers 99% of needs for this
> kind of
> > > > > > thing. I've never wanted numbers that cut across tables and I've
> never
> > > > > > wanted anything other than incremental numbering in 20 years of db
> schema
> > > > > > design. I agree "create sequence" is more flexible than MS SQL
> identity or
> > > > > > MS Access autonumber or DB2 identity or Sybase....(long boring
> list of rival
> > > > > > technologies snipped); but sequences are also a pain in the arse
> when all
> > > > > > you want is a system generated identity (as supplied in easier
> form by every
> > > > > > other db platform I've ever seen). Also are you seriously
> suggesting that
> > > > > > the above is the algorithm that any db actually uses? I think not.
> > > > > > (particularly as you wrote it wrong, SQL syntax error. Also
> potential
> > > > > > problems with above algorithm with transactions and synchronicity.
> Never
> > > > > > mind)
> > > > > >
> > > > > > Regards
> >
Received on Sun Feb 24 2002 - 09:54:46 CST