Defining, Constraining, and Manipulating Your Entities

Part 6 in a second series on the basics of the relational database and SQL

November/December 2016

This article is the sixth in a series that helps you build on the fundamentals you learned in the 12-part SQL 101 series in Oracle Magazine.

The most recent Beyond SQL 101 article, “Changing and Creating Consistently with Integrity,” introduced you to the MERGE statement and demonstrated how you can use it to combine multiple INSERT, UPDATE, and DELETE commands into one statement. You learned when it’s appropriate to use MERGE and when it’s not. You discovered locking mechanisms in Oracle Database and learned how the database guarantees read consistency to its users. You also discovered how Oracle Database enforces automatic referential integrity when it comes to deleting data that’s constrained by a foreign key constraint. Finally, you were introduced to Oracle data definition language (DDL) and saw how to use it to create a table.

In this article, you’ll learn more about using DDL:

• How to create common types of integrity constraints such as primary key, foreign key, not null, unique, and check constraints in Oracle Database
• How to use default column values to ensure that certain columns contain a value even when you don’t supply one
• How to create tables based on existing tables by combining DDL and data manipulation language (DML) into one statement
• How to add, rename, and delete table columns

To try out the examples in this series, you need access to an Oracle Database instance. If necessary, download and install an Oracle Database edition for your operating system. I recommend installing Oracle Database, Enterprise Edition 12c Release 1 (12.1.0.2.0). If you install the Oracle Database software, choose the installation option that enables you to create and configure a database. A new database, including sample user accounts and their associated schemas, will be created for you. (Note that SQL_201 is the user account to use for the examples in this series; it’s also the schema in which you’ll create database tables and other objects.) When the installation process prompts you to specify schema passwords, enter and confirm passwords for the SYS and SYSTEM users and make a note of them.

Finally—whether you installed the database software from scratch or have access to an existing Oracle Database instance—download, unzip, and execute the SQL script to create the tables for the SQL_201 schema used for this article’s examples. (View the script in a text editor for execution instructions.)

Recall that in the most recent Beyond SQL 101 article, you learned how to create a table named EMPLOYEE_EXAMPLE by using the definition shown in Listing 1.

Code Listing 1: Creating the EMPLOYEE_EXAMPLE table

SQL> create table employee_example (
  2     employee_id   number,
  3     first_name    varchar2(30),
  4     last_name     varchar2(30),
  5     hire_date     date,
  6     salary        number(9,2),
  7     manager       number,
  8     department_id number);
Table created.

The definition in Listing 1 exemplifies a basic, common way to create an Oracle Database table. However, the goal in creating application tables is to enable them to join together as efficiently as possible to answer data questions. Before the EMPLOYEE_EXAMPLE table can be used as a meaningful relation, it should be constrained to help enforce the business rules of a database application. For instance, as the database designer of the EMPLOYEE_EXAMPLE table, you might want to enforce application constraints such as these:

• The EMPLOYEE_ID column must never contain a NULL value.
• The MANAGER value must already exist as an employee, via the EMPLOYEE_ID column.
• Any supplied SALARY value must be greater than zero.

To ensure that the EMPLOYEE_ID column can never contain a NULL value, you can create two types of constraints: a not-null constraint or a primary key constraint.

A primary key constraint is the constraint that’s usually chosen to ensure unique and non-null values. The example in Listing 2 demonstrates the addition of a primary key constraint to the EMPLOYEE_EXAMPLE table. To change an existing table’s definition, you use the ALTER TABLE command. And to add a primary key constraint, you use the ADD PRIMARY KEY option of the ALTER TABLE command, enclosing the column name you want to use in parentheses. To view a table’s definition in SQL*Plus, you can use the DESCRIBE command.

Code Listing 2: Adding a primary key to the EMPLOYEE_EXAMPLE table

SQL> alter table employee_example
  2  add primary key (employee_id);
Table altered.
SQL> set lines 100
SQL> describe employee
 Name                        Null?      Type
 ——————————————————————————— —————————— ———————————————
 EMPLOYEE_ID                            NUMBER
 FIRST_NAME                             VARCHAR2(30)
 LAST_NAME                              VARCHAR2(30)
 HIRE_DATE                              DATE
 SALARY                                 NUMBER(9,2)
 MANAGER                                NUMBER
 DEPARTMENT_ID                          NUMBER
SQL> describe employee_example
 Name                        Null?      Type
 ——————————————————————————— —————————— ———————————————
 EMPLOYEE_ID                 NOT NULL   NUMBER
 FIRST_NAME                             VARCHAR2(30)
 LAST_NAME                              VARCHAR2(30)
 HIRE_DATE                              DATE
 SALARY                                 NUMBER(9,2)
 MANAGER                                NUMBER
 DEPARTMENT_ID                          NUMBER 

In Listing 2, note the difference between the results of the DESCRIBE command for the EMPLOYEE table and for the EMPLOYEE_EXAMPLE table. You can see that the EMPLOYEE_EXAMPLE table’s EMPLOYEE_ID column is now being constrained to disallow NULL values.

Listing 3 shows how to use an Oracle Database built-in package, DBMS_METADATA, to verify that the EMPLOYEE_ID column is being constrained as a primary key for the EMPLOYEE_EXAMPLE table. The DBMS_METADATA package helps you gain a more complete picture of your entity and constraint definitions. The results in Listing 3 also verify the foreign key constraint placed on the DEPARTMENT_ID column of the EMPLOYEE table in the most recent Beyond SQL 101 article.

Code Listing 3: Using the DBMS_METADATA package to get more information about entity and constraint definitions

SQL> set long 2000000
SQL> set pagesize 0
SQL> select dbms_metadata.get_ddl('TABLE', 'EMPLOYEE')
  2    from dual;
  CREATE TABLE "SQL_201"."EMPLOYEE"
   ( "EMPLOYEE_ID" NUMBER,
     "FIRST_NAME" VARCHAR2(30),
     "LAST_NAME" VARCHAR2(30),
     "HIRE_DATE" DATE,
     "SALARY" NUMBER(9,2),
     "MANAGER" NUMBER,
     "DEPARTMENT_ID" NUMBER,CONSTRAINT "DEPARTMENT_FK" FOREIGN KEY ("DEPARTMENT_ID")REFERENCES "SQL_201"."DEPARTMENT" ("DEPARTMENT_ID") ENABLE
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"
1 row selected.
SQL> select dbms_metadata.get_ddl('TABLE', 'EMPLOYEE_EXAMPLE')
  2    from dual;
  CREATE TABLE "SQL_201"."EMPLOYEE_EXAMPLE"
   ( "EMPLOYEE_ID" NUMBER,
     "FIRST_NAME" VARCHAR2(30),
     "LAST_NAME" VARCHAR2(30),
     "HIRE_DATE" DATE,
     "SALARY" NUMBER(9,2),
     "MANAGER" NUMBER,
     "DEPARTMENT_ID" NUMBER,PRIMARY KEY ("EMPLOYEE_ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  TABLESPACE "USERS"  ENABLE
   ) SEGMENT CREATION DEFERRED
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  TABLESPACE "USERS"
1 row selected.

Note in Listing 3 that both query results display additional storage characteristics of each table, which is useful when you plan to deploy tables in a multiuser environment. Both the DESCRIBE command (and its shorthand alternative, DESC) and the DBMS_METADATA package can be used to look at your database objects’ metadata—data about data or, as in this case, about definitions. These commands enable you to explore the Oracle Database data dictionary, an internally owned and controlled set of tables and views containing information about your database’s metadata. As you write more DDL statements, you’ll refer often to the data dictionary to compare and confirm your results.

Although you can create a primary key for a table’s column by using the syntax demonstrated in Listing 2, examine the result in Listing 4. Note that the names of the primary key for the DEPARTMENT table (with type P, for primary) and the foreign key for the EMPLOYEE table (with type R, for referential—that is, foreign—key) convey the type and contents of those constraints. However, the primary key for the EMPLOYEE_EXAMPLE table doesn’t have a meaningful name, because the command issued in Listing 2 didn’t specify one. Instead, Oracle Database provided a system-generated name, SYS_C0010551. To change this name to one that’s more appropriate for a business application, you can use the ALTER TABLE command’s RENAME CONSTRAINT option, as shown in the example in Listing 5.

Code Listing 4: A system-generated constraint name was assigned because a name wasn’t explicitly supplied during constraint creation

SQL> select constraint_name, constraint_type, table_name,
r_constraint_name, status
  2    from user_constraints
  3  order by constraint_name;
CONSTRAINT_NAME   C     TABLE_NAME        R_CONSTRAINT_NAME   STATUS
———————————————   —     ————————————————  —————————————————   —————————
DEPARTMENT_FK     R     EMPLOYEE          DEPARTMENT_PK       ENABLED
DEPARTMENT_PK     P     DEPARTMENT                            ENABLED
SYS_C0010551      P     EMPLOYEE_EXAMPLE                      ENABLED
3 rows selected.

Code Listing 5: Assigning appropriate names to system-named constraints

SQL> alter table employee_example rename constraint SYS_C0010551
to EMPLOYEE_EXAMPLE_PK;
Table altered.
SQL> select constraint_name, constraint_type, table_name,
r_constraint_name, status
  2    from user_constraints
  3  order by constraint_name;
CONSTRAINT_NAME     C     TABLE_NAME        R_CONSTRAINT_NAME   STATUS
———————————————     —     ————————————————  —————————————————   —————————
DEPARTMENT_FK       R     EMPLOYEE          DEPARTMENT_PK       ENABLED
DEPARTMENT_PK       P     DEPARTMENT                            ENABLED
EMPLOYEE_EXAMPLE_PK P     EMPLOYEE_EXAMPLE                      ENABLED
3 rows selected.

In the most recent Beyond SQL 101 article, you created a foreign key constraint on the EMPLOYEE table’s DEPARTMENT_ID column that references the primary key constraint of the DEPARTMENT table (see that article’s Listing 4). To enforce the second rule in the business rule list mentioned previously—that the MANAGER value in the EMPLOYEE_EXAMPLE table must already exist as an employee via the EMPLOYEE_ID column, you now need to create a foreign key constraint in EMPLOYEE_EXAMPLE, as shown in Listing 6. In this case, the foreign key is self-referencing; the EMPLOYEE_EXAMPLE table has a recursive relationship, because it needs a foreign key constraint on one column that points to the EMPLOYEE_EXAMPLE table’s primary key constraint.

Code Listing 6: Creating a self-referencing foreign key on EMPLOYEE_EXAMPLE

SQL> alter table employee_example
  2     add constraint employee_manager_fk foreign key (manager)
  3     references employee_example (employee_id);
Table altered.

In Listing 6, the MANAGER column refers to the EMPLOYEE_ID column. The constraint checks whether the values in the MANAGER column are valid EMPLOYEE_ID values. This type of constraint (when enabled) requires that any EMPLOYEE_ID values exist in the EMPLOYEE_ID column before they are referenced in the MANAGER column.

You might choose to disable a constraint in certain exceptional cases—for example, to facilitate a large data load. With the constraint enabled, you’d need to load the data in a specific order. By disabling the constraint temporarily, you can load the data in any order. To disable the constraint created in Listing 6, the command is

alter table employee_example disable constraint employee_manager_fk; 

The command to re-enable the constraint after your data load has completed is

alter table employee_example enable constraint employee_manager_fk;

To enforce the third rule in the business rule list—that any supplied SALARY value must be greater than zero—you can create a check constraint. The example in Listing 7 checks entered values before they are saved, allowing only salary values greater than zero to be successfully entered into the EMPLOYEE_EXAMPLE table. The syntax for this ALTER TABLE … ADD CONSTRAINT … command option is

CHECK (
   has some particular condition) 

The check is performed before a DML action that inserts or changes data in that column is allowed.

Constraints can have three outcomes: TRUE, FALSE, and UNKNOWN. Only if a constraint evaluates to FALSE will the DML action be rejected. So beware: a NULL value for SALARY that’s inserted into the table will be accepted, even though your check constraint in Listing 7 is set to ensure that “SALARY is greater than zero.” Because a NULL value is not known to be greater than zero, it is neither true nor false, so the DML action will complete successfully.

Code Listing 7: Creating a check constraint on EMPLOYEE_EXAMPLE’s SALARY values

SQL> alter table employee_example
  2     add constraint employee_example_salary_ck
  3     check (salary > 0);
Table altered.

The example in Listing 8 demonstrates how to create a unique constraint. The DEPARTMENT table contains information about each department’s name and location. Each location (such as Chicago or New York) has only one accounting department, so the department name for each location must be unique. The ALTER TABLE command in Listing 8 creates a unique constraint to ensure that no more than one instance per name/location combination is allowed to exist in the DEPARTMENT table.

Code Listing 8: Creating a unique constraint on the DEPARTMENT table

SQL> alter table department
  2   add constraint department_name_location_uk
  3   unique (name, location);
Table altered.

It’s important to be aware of some key differences among the types of constraints. Although a primary key constraint and a not-null constraint each enforce disallowing of NULL values in a particular column, a not-null constraint allows for duplicate values; a primary key constraint does not. And although a primary key constraint and a unique constraint each enforce unique values in a particular column, a unique constraint allows NULL values; a primary key constraint does not.

Some table columns can be designed to have default values assigned to them in an insert or update operation if their values are not explicitly provided in the insert or update action. The example in Listing 9 defines such a column for the EMPLOYEE_EXAMPLE table. If anyone inserts a new record into the table, the new column, UPDATED_ON, will be assigned the current SYSDATE value unless the INSERT statement provides an explicit value for that column.

Code Listing 9: Defining a DEFAULT column for EMPLOYEE_EXAMPLE

SQL> alter table employee_example add (updated_on date default sysdate);
Table altered.

Listing 10 demonstrates what happens when a record is inserted into the EMPLOYEE_EXAMPLE table without explicit assignment of a value to the UPDATED_ON column. Though the UPDATED_ON column is not listed as part of the column list or VALUES clause list, it is assigned its default value. The default value will remain in place unless the column value is explicitly updated to another value.

Code Listing 10: Populating a DEFAULT column through a DML INSERT statement

SQL> insert into employee_example (employee_id, first_name, last_name)
  2  values (1, 'Ram', 'Burela');
1 row created.
SQL> commit;
Commit complete.
SQL> select first_name, last_name, to_char(updated_on,
'DD-MON-YYYY HH24:MI:SS') last_updated
  2    from employee_example;
FIRST_NAME               LAST_NAME      LAST_UPDATED
———————————————————————— —————————————— ————————————————————
Ram                      Burela         11-SEP-2016 14:54:34

You don’t ever want a default column value to be NULL, so it’s a good idea to constrain default column values with a NOT NULL constraint. Listing 11 illustrates how to modify the UPDATED_ON column’s definition to constrain the column to be non-nullable. Additionally, both the FIRST_NAME and LAST_NAME columns are similarly constrained, for the sake of business rules.

Code Listing 11: NOT NULL constraints preventing NULL values in columns

SQL> describe employee_example
 Name                        Null?      Type
 ——————————————————————————— —————————— ———————————————
 EMPLOYEE_ID                 NOT NULL   NUMBER
 FIRST_NAME                             VARCHAR2(30)
 LAST_NAME                              VARCHAR2(30)
 HIRE_DATE                              DATE
 SALARY                                 NUMBER(9,2)
 MANAGER                                NUMBER
 DEPARTMENT_ID                          NUMBER
 UPDATED_ON                             DATE
SQL> alter table employee_example modify (first_name not null,
last_name not null, updated_on not null);
Table altered.
QL> desc employee_example
 Name                        Null?      Type
 ——————————————————————————— —————————— ———————————————
 EMPLOYEE_ID                 NOT NULL   NUMBER
 FIRST_NAME                  NOT NULL   VARCHAR2(35)
 LAST_NAME                   NOT NULL   VARCHAR2(35)
 HIRE_DATE                              DATE
 SALARY                                 NUMBER(9,2)
 MANAGER                                NUMBER
 DEPARTMENT_ID                          NUMBER
 UPDATED_ON                  NOT NULL   DATE
SQL> column constraint_name format a30
SQL> column column_name     format a30
SQL> column constraint_type format a1
SQL> select uc.constraint_name, ucc.column_name, uc.constraint_type
  2    from user_constraints uc, user_cons_columns  ucc
  3   where uc.constraint_name = ucc.constraint_name
  4  order by uc.constraint_name;
DEPARTMENT_FK                  DEPARTMENT_ID          R
DEPARTMENT_NAME_LOCATION_UK    NAME                   U
DEPARTMENT_NAME_LOCATION_UK    LOCATION               U
DEPARTMENT_PK                  DEPARTMENT_ID          P
EMPLOYEE_EXAMPLE_PK            EMPLOYEE_ID            P
EMPLOYEE_EXAMPLE_SALARY_CK     SALARY                 C
EMPLOYEE_MANAGER_FK            MANAGER                R
SYS_C0010562                   FIRST_NAME             C
SYS_C0010563                   LAST_NAME              C
SYS_C0010564                   UPDATED_ON             C
10 rows selected.
SQL> alter table employee_example rename constraint SYS_C0010562
to emp_exmpl_first_name_nn;
Table altered.
SQL> alter table employee_example rename constraint SYS_C0010563
to emp_exmpl_last_name_nn;
Table altered.
SQL> alter table employee_example rename constraint SYS_C0010564
to emp_exmpl_last_updated_nn;
Table altered.
SQL> insert into employee_example (employee_id)
  2  values (2);
insert into employee_example (employee_id)
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("SQL_201"."EMPLOYEE_EXAMPLE"."FIRST_NAME")
SQL> insert into employee_example (employee_id, first_name)
  2  values (2, 'Seema');
insert into employee_example (employee_id, first_name)
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("SQL_201"."EMPLOYEE_EXAMPLE"."LAST_NAME")
SQL> insert into employee_example (employee_id, first_name, last_name)
  2  values (2, 'Seema', 'Patel');
1 row created.
SQL> select first_name, last_name, updated_on
  2    from employee_example;
FIRST_NAME           LAST_NAME           UPDATED_O
———————————————————— ——————————————————— —————————
Ram                  Burela              11-SEP-16
Seema                Patel               11-SEP-16
SQL> commit;
Commit complete.

Note also that if you try to create a record that neither explicitly supplies a value nor supplies a default value for the non-nullable columns, you will be prevented from creating the record until all constraint conditions are satisfied. As Listing 11 shows, the moment one constraint is violated, all processing of your statement ends and you receive an error report. The example in Listing 11 demonstrates how to modify a column to be non-nullable and then rename the system-generated constraint afterward.

Alternatively, you could create a NOT NULL constraint in line with the ALTER TABLE command, as in this example:

ALTER TABLE  employee_example
MODIFY       first_name
CONSTRAINT   emp_exmpl_first_name_nn     NOT NULL;

Code Listing 12: Using a CTAS statement to quickly create a new table

SQL> create table employee_ctas as
  2  select *
  3    from employee_example;
Table created.
SQL> select *
  2    from employee_ctas;
EMPLOYEE_ID FIRST_NAME LAST_NAME HIRE_DATE SALARY MANAGER DEPARTMENT_ID UPDATED_O
——————————— —————————— ————————— ————————— —————— ——————— ————————————— —————————
          1 Ram        Burela                                           11-SEP-16
          2 Seema      Patel                                            11-SEP-16

In the most recent Beyond SQL 101 article, you created a table, EMPLOYEE_EXAMPLE, with the same structure as the EMPLOYEE table by defining the column list explicitly. A simpler way of creating a table based on the structure of another table is to use the CREATE TABLE AS SELECT (CTAS) method, which combines DDL and DML into a single statement. Consider the example in Listing 12, which creates the EMPLOYEE_CTAS table by selecting from the EMPLOYEE_EXAMPLE table. The entire structure of the source table, including its data, is created as part of the new table. You can also create a table with the CTAS method without including data, by adding this WHERE clause:

WHERE 1 = 2;

So far in this article, you’ve seen ALTER TABLE statements used to add constraints or add a column to a table. Listing 13 demonstrates the use of ALTER TABLE to change a column in the EMPLOYEE_EXAMPLE table. In that example, the FIRST_NAME and LAST_NAME column lengths are increased via the MODIFY option of the ALTER TABLE command. Listing 14 shows how the ALTER TABLE command can also be used to rename a column and even drop (get rid of) a column.

Code Listing 13: ALTER TABLE used to change a column definition

SQL> alter table employee_example modify (first_name varchar2(35),
last_name varchar2(35));
Table altered.

Code Listing 14: ALTER TABLE used to rename a column and drop a column

SQL> alter table employee_example rename column updated_on to last_updated;
Table altered.
SQL> desc employee_example
 Name                        Null?      Type
 ——————————————————————————— —————————— ———————————————
 EMPLOYEE_ID                 NOT NULL   NUMBER
 FIRST_NAME                             VARCHAR2(35)
 LAST_NAME                              VARCHAR2(35)
 HIRE_DATE                              DATE
 SALARY                                 NUMBER(9,2)
 MANAGER                                NUMBER
 DEPARTMENT_ID                          NUMBER
 LAST_UPDATED                           DATE
SQL> alter table employee_example drop column hire_date;
Table altered.
SQL> desc employee_example
 Name                        Null?      Type
 ——————————————————————————— —————————— ———————————————
 EMPLOYEE_ID                 NOT NULL   NUMBER
 FIRST_NAME                             VARCHAR2(35)
 LAST_NAME                              VARCHAR2(35)
 SALARY                                 NUMBER(9,2)
 MANAGER                                NUMBER
 DEPARTMENT_ID                          NUMBER
 LAST_UPDATED                           DATE

This article has illustrated common constraints such as primary key, foreign key, not-null, check, and unique constraints. You also learned how such constraints can be violated when data is inserted that doesn’t conform to the constraint conditions. You discovered how to create a table with the same structure as another table by using the CTAS method. You also discovered how default values can be used for columns you’d like regularly populated. Last, you were introduced to column definition manipulation via the ALTER TABLE command and several of its options. In the next article in this series, you’ll learn more about DDL and get an introduction to sequences and views.