Re: Renaming PK constraints, indexing existing tables

In article <93m7ac$amgpb$5_at_ID-62141.news.dfncis.de>,   "Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote:
> 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.

Yeah, figured it out and got things going shortly after I posted my "Huh?" message.

> 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.

Huh - do we work for the same company? :-) Thanks again.

>
> 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/
>
>

Sent via Deja.com
http://www.deja.com/ Received on Fri Jan 12 2001 - 10:18:09 CST