Re: creating snapshot

From: Frank van Bortel <frank.van.bortel_at_gmail.com>
Date: Thu, 27 Dec 2007 19:50:06 +0100
Message-ID: <2afdc$4773f3dc$524b5c40$23616@cache5.tilbu1.nb.home.nl>

amitabh.mehra_at_gmail.com wrote:
> Consider the table myTable:
> first_name varchar2(25),
> last_name varchar2(25),
> regNum number,
> status varchar(20) -> nullable with default
> value as 'new'
>
> Primary Key is first_name, last_name, status
>

I'd say you're having serious problems knowing Oracle:

  1 create table myTable (
  2 first_name varchar2(25) not null,   3 last_name varchar2(25) not null,

  4  regNum        number,
  5  status        varchar(20) default 'New'

  6* )
SQL> / Table created.
SQL> desc mytable

 Name                      Null?    Type
 ------------------------- -------- ----------------
 FIRST_NAME                NOT NULL VARCHAR2(25)
 LAST_NAME                 NOT NULL VARCHAR2(25)
 REGNUM                             NUMBER
 STATUS                             VARCHAR2(20)

SQL> alter table mytable add constraint mytab_pk primary key (first_name, last_name, status);

Table altered.

SQL> desc mytable

 Name                      Null?    Type
 ------------------------- -------- ----------------
 FIRST_NAME                NOT NULL VARCHAR2(25)
 LAST_NAME                 NOT NULL VARCHAR2(25)
 REGNUM                             NUMBER
 STATUS                    NOT NULL VARCHAR2(20)

Please note the null-ability of the STATUS column: it is NOT NULL! Oracle does NOT support NULLABLE columns in a PRIMARY KEY Ergo: no need for a decode!

-- 
Regards,
Frank van Bortel

Top-posting is one way to shut me up...

Received on Thu Dec 27 2007 - 12:50:06 CST