Re: Renaming PK constraints, indexing existing tables

The code fragment below tries to help out retrieving the column definition of a primary key constraint for a specific table, not knowing the constraintrs name.
I was more or less assuming you could add the rest. If you need further assistance let me know, I probably have to deal with exactly the same issue in the next few days, but as I am running from one disaster into another, I can't predict when that *exactly* is going to be.

Regards,

Sybrand Bakker, Oracle DBA

<handy_solo_at_my-deja.com> wrote in message news:93l5ij$oc9$1_at_nnrp1.deja.com...
> 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 - 17:08:14 CST