Re: how to name a primary key?

Originally posted by Coea
> Simple question : how to name a primary key ?
>
> i tried the following
>
> create table prova2(
> id integer not null,
> nome varchar(10) not null,
> primary key(id)
> );
>
> the result is that the primary key is name as SYS_xxyyy .... the dbms
> interpret this as :
>
> CREATE TABLE PROVA2
> (
> ID INTEGER NOT NULL,
> NOME VARCHAR2(10) NOT NULL
> )
> TABLESPACE USERS
> PCTUSED 40
> PCTFREE 10
> INITRANS 1
> MAXTRANS 255
> STORAGE (
> INITIAL 128K
> NEXT 128K
> MINEXTENTS 1
> MAXEXTENTS 4096
> PCTINCREASE 0
> FREELISTS 1
> FREELIST GROUPS 1
> BUFFER_POOL DEFAULT
> )
> LOGGING
> NOCACHE
> NOPARALLEL;
>
> ALTER TABLE PROVA2 ADD (
> PRIMARY KEY (ID)
> USING INDEX
> TABLESPACE USERS
> PCTFREE 10
> INITRANS 2
> MAXTRANS 255
> STORAGE (
> INITIAL 128K
> NEXT 128K
> MINEXTENTS 1
> MAXEXTENTS 4096
> PCTINCREASE 0
> FREELISTS 1
> FREELIST GROUPS 1
> ));
>
> creating the primary key.
>
> Someone suggest to do the following instead :
>
> create table prova2(
> id integer not null constraint pk_a_name_for_primary_key primary key,
> nome varchar(10) not null
> );
>
> so, well, the primary key has a name but it seems it's not the same as
> before :
>
> CREATE TABLE PROVA2
> (
> ID INTEGER NOT NULL,
> NOME VARCHAR2(10) NOT NULL
> )
> TABLESPACE USERS
> PCTUSED 40
> PCTFREE 10
> INITRANS 1
> MAXTRANS 255
> STORAGE (
> INITIAL 128K
> NEXT 128K
> MINEXTENTS 1
> MAXEXTENTS 4096
> PCTINCREASE 0
> FREELISTS 1
> FREELIST GROUPS 1
> BUFFER_POOL DEFAULT
> )
> LOGGING
> NOCACHE
> NOPARALLEL;
>
> CREATE UNIQUE INDEX PK_PROVA2 ON PROVA2
> (ID)
> LOGGING
> TABLESPACE USERS
> PCTFREE 10
> INITRANS 2
> MAXTRANS 255
> STORAGE (
> INITIAL 128K
> NEXT 128K
> MINEXTENTS 1
> MAXEXTENTS 4096
> PCTINCREASE 0
> FREELISTS 1
> FREELIST GROUPS 1
> BUFFER_POOL DEFAULT
> )
> NOPARALLEL;
>
> ALTER TABLE PROVA2 ADD (
> CONSTRAINT PK_PROVA2 PRIMARY KEY (ID)
> USING INDEX
> TABLESPACE USERS
> PCTFREE 10
> INITRANS 2
> MAXTRANS 255
> STORAGE (
> INITIAL 128K
> NEXT 128K
> MINEXTENTS 1
> MAXEXTENTS 4096
> PCTINCREASE 0
> FREELISTS 1
> FREELIST GROUPS 1
> ));
>
> ... an index and a constraint on a index.
>
> Maybe the second is the same thing as the first. I'm searching for
> other ways to declare named primary keys.
>
> Thank you.
>
> coea
Another way:

create table prova2(
id integer not null,
nome varchar(10) not null,
constraint name_for_primary_key primary key(id) );

It makes no difference to the final outcome: a primary key ALWAYS has an associated index. If no index exists, one is created automatically with same name and columns as the constraint.

--
Posted via http://dbforums.com