Primary Keys Guarantee Uniqueness? Think Again.

When you create a table with a primary key or a unique constraint, Oracle automatically creates a unique index, to ensure that the column does not contain a duplicate value on that column; or so you have been told. It must be true because that is the fundamental tenet of an Oracle database, or for that matter, any database.

Well, the other day I was checking a table. There is a primary key on the column PriKey. Here are the rows:

Select PriKey from TableName;

PriKey
------
1
1

I got two rows with the same value. The table does have a primary key on this column and it is enforced. I can test it by inserting another record with the same value - “1”:

SQL> insert into TableName values (1,…)

It errors with ORA-00001: unique constraint violated error. The question is: why there are two rows with duplicate values in a column that has an enforced primary key, and it refuses to accept the very value that is already violated the primary key? It could be a great interview question, test your mettle; or just entertaining. Read on for the answer.

Application Design is the only Reason for Deadlocks? Think Again

[Updated on 4/20/2013 after feedback from Charles Hooper, Jonathan Lewis, Laurent Schneider and Mohamed Houri and with some minor cosmetic enhancements of outputs]

Have you ever seen a message “ORA-00060: Deadlock detected” and automatically assumed that it was an application coding issue? Well, it may not be. There are DBA-related issues and you may be surprised to find out that INSERTs may cause deadlock. Learn all the conditions that precipitate this error, how to read the "deadlock graph" to determine the cause, and most important: how to avoid it.