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:
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.
|
