Re: Preferred method in creating primary key

Svend Jensen wrote:
> There is also a third method of inline primary key, that creates the
> unique index in the same tablespace as the table, and the constraint and
> index is named something like sys_cxxxxxx (not that nice).
>
> CREATE TABLE T3
> ( X NUMBER NOT NULL
> primary key,
> Y NUMBER ).
>
> This version is often used by application installers, and I find that
> very unlucky.
> You have a hard time getting the naming to be meaningful, constraint
> naming is impossible to change without dropping and recreating including
> index (on the wish list to Oracle: the alter table bbb modify constraint
> xxx rename to yyy).
>
> A nonsupport way out of this is to update the sys.con$ table!
> No flaming - please. It is on Your own risk - but it works.
>

(1) not null is redundant with primary key constraint; (2) naming: your statement is only true, if you do not follow the general rule to name ALL constraints, in which case oracle chooses a sys* name;
create table t3
(x number

        constraint t3_pk primary key [optional USING INDEX clause], y......
);

especially when using the "USING INDEX ....." clause, there is no need to use a separate index-statement, which IMHO is most valuable, because this way there is an obvious connection between the create-table-statement and the index used to enforce that constraint; Received on Wed Aug 13 2003 - 08:07:19 CDT