Mar 172014
 
create table from_12c_onwards (
  you_can_have_your_pk number 
    generated always as identity,
  so_you_dont_have_to_type_it varchar2(10)
);

insert into from_12c_onwards (
  so_you_dont_have_to_type_it
) values (
  'yes'
);

insert into from_12c_onwards (
  so_you_dont_have_to_type_it
) values (
  'Yes'
);

insert into from_12c_onwards (
  so_you_dont_have_to_type_it
) values (
  'YES!'
);

select * from from_12c_onwards;

YOU_CAN_HAVE_YOUR_PK SO_YOU_DON
-------------------- ----------
                   1 yes
                   2 Yes
                   3 YES!

If you’d like more details, there’s a nice article on oracle-base discussing how identity columns work. If you prefer, you can assign a sequence to be a column default instead, as discussed here.

Mar 142014
 

Somehow I ended up with two rows in my table both with the same primary key value:

select the_pk 
from   dodgy_data;

    THE_PK
----------
         1
         1

The primary key does exist and THE_PK is the only column in it, so we can’t insert another row with THE_PK = 1:

select constraint_name, constraint_type
from   user_constraints
where  table_name = 'DODGY_DATA';

CONSTRAINT_NAME      CONSTRAINT_TYPE
-------------------- ---------------
TAB_PK               P              

select column_name 
from   user_cons_columns
where  constraint_name = 'THE_PK';

COLUMN_NAME
-----------
THE_PK     

insert into dodgy_data ( the_pk )
values ( 1 );

SQL Error: ORA-00001: 
  unique constraint (CHRIS.TAB_PK) violated

How? Answers in the comments!