Re: Renaming PK constraints, indexing existing tables

In article <93l2hj$ag33t$4_at_ID-62141.news.dfncis.de>,   "Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote:
> Hasn't changed much since the early days of version 7. Some medical
 firms
> try to sell your ginseng tablets in order to stop this kind of
 beginning
> amnesia :)
 

::wince:: Ouch! Maybe I'll hit google next and see if I can find some to order.

> Mind you, I never got the create trigger syntax right and I will
> always stumble.

Ah, so you *are* human?!? Always wondered. ;-)

>
> So here goes:
> 1 You are creating a primary or an unique constraint. The index will
 simply
> inherit the unique attribute
> 2 the proper syntax is
> alter <tablename>
> add constraint <constraint name>
> primary key (<key column> ...)
> using index
> tablespace <tablespace_name> etc.
> You can create a primary key on column level at table creation only,
 you
> can't modify a constraint on column level.

Uh oh. So... hmm... to rename these suckers I take it I'm going to have to rebuild the tables with the appropriate constraint clause with a readable name, eh?
Rats.

> you could do something smart with user_cons_columns along the lines of
 

> coldef varchar2(1000);
> begin
> coldef := '(';
> for r in
> (select column_name
> from user_cons_columns cc, user_constraints c
> where c.table_name = '<your table_name>'
> and c.constraint_type = 'P'
> and cc.constraint_name = c.constraint_name
> order by column_position
> )
> loop
> coldef := coldef || r.column_name ||',';
> end loop;

  Zoom - there it goes right over my head! Must be missing something obvious. Looks like a pretty way to see where my constraints are? Or does it 'do' anything? Guess I'll C&P and see what happens.

>
> Hth,

Aye - it sure did. Thanks for the response.

>
> Sybrand Bakker, Oracle DBA
>
> <handy_solo_at_my-deja.com> wrote in message
> news:93kppo$cmp$1_at_nnrp1.deja.com...
> > Here's my challenge - hopefully someone can point me in the correct
> > direction.
> >
> > We have built an application for a client that uses Oracle tables.
 The
> > tool we use for development handles creating tables and views.
> > However, constraints get 'Oracle generated' names - such as
> > SYS_C001769560. The DBAs at the install site are saying no way!
 They
> > want these constraints to have real names and to be indexed. Ok,
 can't
> > say that I blame them much...
> >
> > However, I'm a bit stumped as to how to most logically handle this.
> > I can't create the index with the constraint on. So, I drop the
> > constraint first. Simple enough. But then do I create the index
> > with 'UNIQUE' or do I leave that alone knowing that I'm going to
 create
> > a constraint?
> >
> > Secondly, what exactly is the syntax to modify the table? I'm
 puzzling
> > through the alter table modify (modify_colum_options) tree but quite
> > frankly am confused. It would appear that I can create the index on
> > the fly if I puzzle out the column_constraint piece(?). Oh, and
> > needless to say, if I got that working I must be sure to place the
> > index in the appropriate tablespace.
> >
> > Any suggestions or ideas here? Embarassingly enough, I was an
 Oracle
> > DBA back in the Oracle 6 and early 7 days, but I've forgotten
 entirely
> > too much. *sigh*
> >
> > Any thoughts or sql snippets would be greatly appreciated.
> >
> > Thanks in advance,
> >
> > Handy (or not so handy as the case may appear to be...)
> >
> >
> > Sent via Deja.com
> > http://www.deja.com/
>
>

Sent via Deja.com
http://www.deja.com/ Received on Thu Jan 11 2001 - 14:37:41 CST