BigBlog

Now to reproduce Fred's problem with Primary Key Puzzle. First, create the table without the primary key constraint.

CREATE TABLE pvssession (
  msisdn varchar2(20) not null,
  creationdate date not null,
  lastaccess date not null);

Next create a virtual index.

CREATE UNIQUE INDEX pk_pvssession_msisdn
  ON pvssession(msisdn)
  NOSEGMENT;

This has to be a UNIQUE index because it support a primary key constraint. The NOSEGMENT keyword makes this a virtual index.

Now to add the constraint:

ALTER TABLE pvssession
  ADD CONSTRAINT pk_pvssession_msisdn
  PRIMARY KEY (msisdn)
  USING INDEX;

The important thing here is to have the name of the constraint matching the unique index created previously.

USER_CONSTRAINTS shows:

select constraint_name,constraint_type,search_condition,index_name from user_constraints;

Now the index name is not similar to what Fred reported. However, the following query returns no rows:

select * from user_indexes;

Inserted some test data:

insert into pvssession (msisdn,creationdate,lastaccess) values ('HOWDYDOODY', sysdate, sysdate );
commit;
insert into pvssession select * from pvssession;
commit;

What is happening is that Oracle has no way of enforcing a primary key constraint because the index does not physically exist. Since it does not know that the row with the same value of MSISDN already exists, Oracle allows the row to be inserted.

As for the mystery of the different index name, I can suggest that the constraint was created along with the index.

-- Remove the primary key constraint
ALTER TABLE pvssession DROP CONSTRAINT pk_pvssession_msisdn;
DROP INDEX pk_pvssession_pk;
-- Reove all data in the table
TRUNCATE TABLE pvssession;
-- Recreate the constraint
ALTER TABLE pvssession
  ADD CONSTRAINT pk_pvssession_msisdn
  PRIMARY KEY (msisdn)
  USING INDEX NOSEGMENT;

And I ended up at the same place I was before. I give up.