RE: [Q] what difference between PK and unique index + not NULL??

If I understand what you're trying to say, I'm going to have to disagree. In Oracle, I can have a FK constraint reference a unique constraint on columns that allow nulls; I can have a FK constraint referencing a PK constraint that's enforced by a non-unique index; I can have a FK constraint reference a unique constraint enforced by a non-unique index. Example (FK constraint referencing a unique constraint on columns that allow nulls, unique constraint enforced by a non-unique index)

SQL> create table parent (id number, name varchar2 (30)) ; Table créée.  

SQL> create index parent_idx1 on parent (id) ; Index créé.  

SQL> alter table parent add (constraint parent_uq1 unique (id)) ; Table modifiée.  

SQL> create table child
  2 (id number, birth_date date,
  3 constraint child_fk1 foreign key (id) references parent (id)) ; Table créée.  

SQL> select index_name, uniqueness
  2 from user_indexes where table_name = 'PARENT' ;

INDEX_NAME                     UNIQUENES
------------------------------ ---------
PARENT_IDX1                    NONUNIQUE
 

SQL> select
  2 a.constraint_type, a.r_constraint_name, b.column_name   3 from user_constraints a, user_cons_columns b   4 where a.constraint_name = 'CHILD_FK1'

  5         and b.constraint_name = a.constraint_name ;
C R_CONSTRAINT_NAME              COLUMN_NAME
- ------------------------------ --------------------
R PARENT_UQ1                     ID
 

SQL> -----Original Message-----
Marquez, Chris
...

You can't have a FK point to a "Unique index + Not NULL" with out a PK constraint! That s a technical difference.
...

Also like this comment too; "Oracle can from version 8 up use non-unique indexes to implement PK constraints."

Tell your consultant he is "technical right, until the customer asks for a child table (FK) on the original table.

--
http://www.freelists.org/webpage/oracle-l