Oracle contraints

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') );

 can be used with 

---

 

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);

 

---

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);