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

 
 Home
 E-mail Us
 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 


 

 

 


 

 

 

 
 

Oracle Constraints types

Oracle Tips by Burleson Consulting

Oracle Constraints tips

Oracle constraints are critical to the scalability, flexibility and integrity of your database data. Constraints apply specific rules to data, ensuring the data conforms to the requirements defined. There are a number of different kinds of constraints that you will be concerned with as a DBA. These are:

  • Check

  • Not NULL

  • Primary key

  • Unique

  • Foreign Key

Let's look at each of these in a little more detail.

Check Constraints

Check constraints validate that values in a given column meet a specific criteria. For example, you could create a check constraint on a varchar2 column so it only can contain the values T or F as in this example:

Create table my_status
( status_id     NUMBER      PRIMARY KEY,
  person_id     NUMBER      NOT NULL,
  active_record VARCHAR2(1) NOT NULL
  CHECK (UPPER(active_record)='T' or
         UPPER(active_record)='F'),
  person_ssn   VARCHAR2(20) CONSTRAINT un_person_ssn UNIQUE
);

In this example we created a table called MY_STATUS using the create table command. Notice the constraint keyword, this indicates that we are getting ready to define a constraint. Recall that earlier in this chapter we discussed in-line and out of line constraints. This particular example is known as an in-line constraint because the constraint is being defined in the same line as the column being defined.

If you need to add a check constraint to a table after the fact, simply use the alter table command. Here is an example:

ALTER TABLE my_status ADD (CONSTRAINT ck_stats_01
  CHECK (UPPER(active_record)='T' or
         UPPER(active_record)='F') );

Boolean Expressions can be used with Check Constraints.


 

NOT NULL Constraints

NOT NULL constraints are in-line constraints that indicate that a column can not contain NULL values. The previous example of the creation of the MY_STATUS table contained two examples of NOT NULL constraints being defined. For example, the PERSON_ID column is defined as NOT NULL in that example.

If you need to add a NOT NULL constraint to a table after the fact, simply use the alter table command as in this example:

ALTER TABLE my_status MODIFY ( person_id NOT NULL);

 

NOT NULL Constraint Tips


Primary Key Constraints

Primary key constraints 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.

If you need to primary key constraint to a table after the fact, simply use the alter table command.

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

 

Primary Key Constraint Tips


Unique Constraints

Unique constraints are like alternative primary key constraints. A unique constraint defines a column, or series of columns, that must be unique in value. You can have a number of unique constraints defined and the columns can have NULL values in them, unlike a column that belongs to a primary key constraint.   If you need to add unique key constraints to a table after the fact, simply use the alter table command.

ALTER TABLE
   my_status
ADD CONSTRAINT
    uk_my_status
UNIQUE
   (status_id,
    person_id);

 

Unique Constraint Tips


Foreign Key Constraints

A foreign key constraint is used to enforce a relationship between two tables. As an example, take the case of two tables, ITEM and PART. These tables have a relationship (an item can have none, one or many parts). Foreign key constraints help to enforce that relationship.  In the DBA world, we use diagrams called Entity Relationship Diagrams (ERD) to show visually how tables relate. Here is an ERD that shows how the ITEM and PART tables relate.

Notice we have the ITEM and PART tables diagramed in two boxes. There is a line between the two. Now, notice the two lines from the PART table to the main line between the ITEM and PART tables. We call these 'crows feet'. When you see crows feet, this means that there is a one to many relationship between the tables.

In the diagram above, there is a one-to-many relationship between the ITEM and PART tables. An item may have no parts assigned to build it (perhaps it's new and in development), or it may have one or many parts that go into building the part. Notice the circle at the top of the crow's feet. Since it is not filled in, that indicates that there may not be any parts in the part table. If the circle was darkened, this would indicate that there must be at least one part associated with each item.

So, how does this relate to foreign key constraints? Well, foreign key constraints help to enforce the types of relationships between tables we have just demonstrated. In this example we will create the ITEM and PART table. In the process of doing so, we will create a foreign key relationship between the two:

CREATE TABLE part(
Part_no    NUMBER PRIMARY KEY,
Part_desc VARCHAR2(200) NOT NULL );
 
CREATE TABLE item (
Item_no   NUMBER,
Part_no   NUMBER,
Item_desc varchar2(200) NOT NULL,
CONSTRAINT fk_item_part FOREIGN KEY (part_no) REFERENCES PART (part_no),
CONSTRAINT pk_item PRIMARY  KEY (item_no, part_no) );

In this example, what we are really interested in is the creation of the ITEM table. First, note that we defined the primary key as an out of line primary key. This is because it is a composite primary key and composite primary keys have to be defined out of line.

Now, we are interested in the foreign key definition. You must define foreign key constraints as out of line constraints, as we have done in our example. Here is a snippet of the command that we used:

CONSTRAINT fk_item_part FOREIGN KEY (part_no) REFERENCES part (part_no);

Note that we start out using the constraint keyword. This tells Oracle that we are about to define a constraint. Then, we name the constraint. In this case we named the constraint FK_ITEM_PART. Constraint names have to be unique for each table and can be no more than 30 characters in length. We then use the foreign key keyword to indicate that we are defining a foreign key constraint. Next we define the column in the table being created that this foreign key belongs to.

Next we use the references keyword to indicate that we are going to define the table and column that this foreign key references. The referenced table name is next listed. In this case, it's the PART table. Finally the column in the PART table that is being referenced is listed, PART_NO.

The bottom line is that now, a part cannot be added to the ITEM table, unless it's listed in the PART table. This ensures data integrity is maintained. If you need to add foreign key constraints to a table after the fact, simply use the alter table command as seen here:

ALTER TABLE my_status ADD CONSTRAINT fk_my_status
FOREIGN KEY (part_no) REFERENCES part (part_no);

 

Primary Key Constraint Tips

 

This is an excerpt from the bestselling "Easy Oracle Jumpstart" by Robert Freeman and Steve Karam (Oracle ACE and Oracle Certified Master).  It's only $19.95 when you buy it directly from the publisher here.

If you like Oracle tuning, you may enjoy the new book "Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & scripts. 

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.


 

 

��  
 
 

 
 
 
 
oracle dba poster
 

 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

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 -  2014

All rights reserved by Burleson

Oracle ? is the registered trademark of Oracle Corporation.