Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 

Primary Key Constraint Tips

Oracle Database Tips by Donald BurlesonDecember 16, 2015

Question:  I need to understand how to add a primary key constraint statement to make sure that all rows are unique.  How is a primary key different than a unique index?

Primary Key Constraints

Primary key constraints perform the same thing as a unique index in the sense that a primary key constraint makes a unique index to enforce the unique values within the data column.

A primary key constraint define a column or series of columns that uniquely identify a given row in a table. Defining a primary key on a table is optional and you can only define a single primary key on a table. A primary key constraint can consist of one or many columns (up to 32). Any column that is defined as a primary key column is automatically set with a NOT NULL status.

The previous example of the creation of the MY_STATUS table included the definition of the STATUS_ID column as the primary key of that table by using the primary key keyword.

Here we make an un-named primary key on the status_id column.  The primary key will appear in the dba_constraints view of type = 'PRIMARY KEY' but with a system-generated name like SYS027276r2:

create table
   my_status
   (status_id not null primary key);

If you need to primary key constraint to a table after the fact, simply use the alter table command.  Here the primary key column gets a meaningful name (pk_my_status) instead of a system generated primary key name.  That makes the alter table add constraint primary key a better option than the create table example above.

ALTER TABLE
   my_status
ADD CONSTRAINT
   pk_my_status
PRIMARY KEY (status_id);

 

Oracle in-line primary key constraint

Let's look at the create constraint commands in-action.  Below we create a constraint for a primary key:

Connect scott/tiger
 
CREATE TABLE books
( book_id            NUMBER PRIMARY KEY,
  book_name          VARCHAR2(30),
  author_name        VARCHAR2(40),
  book_isbn          VARCHAR2(20) )
TABLESPACE users;

In this example, we did a create table for a table called BOOKS which has 4 columns. The first column is BOOK_ID which is a NUMBER datatype. This means we will be storing numbers in this column. Did you notice on the line where we define the column BOOK_ID, that we included the word primary key?

This is known as an in-line constraint because we are defining the constraint on the same line as the column associated with the constraint. In this case the keyword primary key means we are defining a primary key constraint on the BOOKS table called PK_BOOKS.

What is a primary key constraint? A constraint is a rule that is applied to the table. In this case, the primary key constraint is a rule is says can't have a duplicate entry in the BOOK_ID column, and the BOOK_ID column can never be empty or ?null.? In this example, each book has a unique BOOK_ID assigned. In other words, War and Peace might have a BOOK_ID of 12345, and no other book will ever have the same BOOK_ID column value.

You might say, why not just make the title of the book the primary key. The answer is that you might have a number of different books called War and Peace. One might be paperback and one might be hardcover. One might be out of print, one might be an easy to read child's version. Hence, the title of the book isn't a good candidate to uniquely identify the book. In this case then, we have a column called BOOK_ID that will be unique for each book. It then becomes the primary key. Since this isn't a book about designing databases, that's enough on primary keys for now.

You should know that in Oracle each primary key column must be unique. Thus, we can't have two books with a BOOK_ID of 12345, Oracle would reject the second attempt to use that BOOK_ID and return an error to the user.

You can also define a combination of columns to be the primary key. This is known as a concatenated primary key. These kinds of constraints are defined as out-of-line constraints because they are defined on their own line in the create table statement.

Here is an example of the creation of an out-of-line primary key constraint. In this case our books table might well have multiple BOOK_ID's. Hence, we will add a sequence number column (BOOK_ID_SEQ) to act as a 'tie breaker?. Both columns in this example

CREATE TABLE books
( book_id            NUMBER,
  book_id_seq        NUMBER,
  book_name          VARCHAR2(30),
  author_name        VARCHAR2(40),
  book_isbn          VARCHAR2(20),
  CONSTRAINT
     pk_books
  PRIMARY KEY (book_id, book_id_seq) )
TABLESPACE users;

Once you create a table, you can use the SQL*Plus desc command to see its structure as seen in this example:

SQL>desc books
 
Name                                      Null?    Type
 ----------------------------------------- -------- ------------
 BOOK_ID                                   NOT NULL NUMBER
 BOOK_ID_SEQ                               NOT NULL NUMBER
 BOOK_NAME                                          VARCHAR2(30)
 AUTHOR_NAME                                        VARCHAR2(40)
 BOOK_ISBN                                          VARCHAR2(20)

Note that the BOOK_ID and BOOK_ID_SEQ columns have a NOT NULL constraint assigned to them. Any time you assign a column to a table's primary key, it will be given a NOT NULL constraint. As a result, no primary key column can be NULL.

 

Choosing the "best" primary key

When choosing a primary key for an Oracle entity we must consider the possibility of duplicate values and attempt to find a persistent unique natural key.  Using a sequence as a primary key is not ideal, and the obvious best primary keys include:

  • Places - The longitude/latitude coordinates make a perfect primary key
     

  • People - Fingerprint biometrics make a great primary key

For more on non-traditional Oracle primary keys, see this article on Oracle fingerprints as tables primary keys and primary key management with biometric primary keys.


 
Get the Complete
Oracle SQL Tuning Information 

The landmark book "Advanced Oracle SQL Tuning  The Definitive Reference"  is filled with valuable information on Oracle SQL Tuning. This book includes scripts and tools to hypercharge Oracle 11g performance and you can buy it for 30% off directly from the publisher.

 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster