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

Other related posts: