Re: Renaming PK constraints, indexing existing tables

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 :) Mind you, I never got the create trigger syntax right and I will always stumble.

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

Hth,

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/
Received on Thu Jan 11 2001 - 13:46:04 CST