how to name a primary key?

From: coea <coea77_at_tin.it>
Date: 11 Jun 2003 07:37:41 -0700
Message-ID: <12506ef8.0306110637.22506485_at_posting.google.com>

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 Received on Wed Jun 11 2003 - 16:37:41 CEST