Re: Primary Key ....

On 11 Mar 2005 20:40:15 -0800, "Ariji Chatterjee" <arijitchatterjee123_at_yahoo.co.in> wrote:

>Dear Faculties,
>Here I have created a table in two manner ..
>===================================
>SQL> create table Test
> 2 (
> 3 PK number(10,0) not null,
> 4 Name varchar(10)
> 5 );
>
>Table created.
>
>SQL> Alter table Test add constraint UK_Con Unique(PK);
>
>Table altered.
>
>Here Number of contraints is TWO.

SQL> select table_name, constraint_name, constraint_type   2 from user_constraints;

TABLE_NAME                     CONSTRAINT_NAME                C
------------------------------ ------------------------------ -
TEST                           SYS_C0012699                   C
TEST                           UK_CON                         U

 You've got a system-generated not null check constraint from the "not null" in your create table, and a named unique constraint. OK...

>===================================
>SQL> drop table test
> 2 /
>Table dropped.
>
>SQL> create table Test
> 2 (
> 3 PK number(10,0) primary key,
> 4 Name varchar(10)
> 5 );
>
>Table created.
>
>Here Number of contraints is ONE.

 One primary key constraint with a system generated name. This also enforces a not null constraint on the PK column as part of being a primary key constraint - no additional constraint is created (not that it really matters, though).

SQL> select table_name, constraint_name, constraint_type   2 from user_constraints;

TABLE_NAME                     CONSTRAINT_NAME                C
------------------------------ ------------------------------ -
TEST                           SYS_C0012698                   P



>===================================

 No. You didn't add a primary key, you added one unique key.

 Since it is the only unique key, and is entirely not-null, it is in effect nearly indistinguishable a primary key at the moment (not entirely though)... until you add another unique constraint. If you want a primary key, use a primary key constraint.

 System-generated names are to be avoided where possible, although it's not worth worrying about the "not null" constraint since the system-generated constraint name never appears in error messages - not null violations have their own error message, e.g. ORA-01400. But if you violate a primary key, then having "constraint SYS_C0012698 violated" appearing isn't much help - give the constraint a meaningful name.

 The preferred way would be something more like:

create table test (

    pk number(10,0) not null,
    name varchar(10)
);

alter table test

    add constraint test_pk
    primary key (pk);

 This results in a named primary key constraint enforced by a named unique index. Naming conventions are the source of long arguments, but so long as you make a policy and stick to it then it does help.

 You can break that down further if you want even more control over the steps, either with the "using index" clause of add constraint, or pre-creating the index separately first. The index enforcing a unique or primary key doesn't have to be unique, for example, so you might want to create a non-unique index so you can add a deferrable constraint (or have the option to do so later).

-- 
Andy Hassall / <andy@andyh.co.uk> / <http://www.andyh.co.uk>
<http://www.andyhsoftware.co.uk/space> Space: disk usage analysis tool