Login

Creating a Table using Oracle 10G XE

Oracle 10G XE provides a wizard to help you create a table. The wizard walks you through several steps for creating a table, taking you to the next step after you have entered valid entries for the current step. While scripts can be used to create a table, creating a table using the visual, form-based creation process is a lot simpler and less prone to errors. This tutorial describes a table to be created in the HR database with all the necessary ingredients for a typical database.

Table is crucial

A table is an essential element of any relational database management system. Data stored in the columns and rows of a table, and the relationship that may exist between the various tables, is at the core of RDBMS. The creator of the table becomes the owner of the table. An owner cannot have two tables with the same name. Some of the other dos and don’ts for creating a table are:

The table name cannot use any of the keywords used in the system
It cannot be a combination of two names separated by a white space
The maximum length of a table is 28 characters.
The table name's first character should be alphabetic
The table name's other characters better be confined to numbers and
underscores

In any case, if and when you violate the rules you do get a error message telling you what was wrong. In that sense, you are covered. Since the Wizard tells you immediately what is wrong in some cases, you know what you have to do to correct it.

We will start with a very simple example of creating just one table called Htek_Employees for the Htek company. The Employee table will consist solely of the Employee information such as:

In addition to this, to associate this information uniquely to an individual an Employee_ID is also added. This table will have 16 columns. The 16 columns are the attributes of the Employee physically stored in a table. A row in the table may contain values for each of these columns. For example, for an employee whose Employee_ID is 1, the details are:

If the Employee_ID is known, everything about this employee should be available from this table.

Column Data Types

For each column in the above table, the type of information it contains is different. While names are represented by ‘text‘, hire_date and birth_date consists of ‘date‘ type information. The salary contains ‘money‘ type information. Telephone numbers and the Zip code consists of numerals, but they are not number type; who will ever think of adding two zip codes, or subtracting one telephone number from another? So these are no different than names. So the different columns may be stored as different data types. The Oracle table designer gives you options from which you can choose the data type that you may want to assign.

{mospagebreak title=Creating a table}

The data in a table is stored in rows and columns. In order to create a table, you need to create the columns in which rows and rows of data for each employee will be stored. This is an organized structure and must follow certain rules. The Oracle 10g XE table creation wizard takes you through the logical steps in which you define the column, its data type and any constraints on what may or may not be stored in that column.

Primary Key

After the columns you will then need to provide information as to the Primary Key. This is a column which is unique. Every row must have a value for this column different from every other row. A combination of two columns together can also be used as a primary key. In this case, as a combination, each row in these columns should be unique. In the present example we will consider a single column (the Employee_ID) as the primary key.

Foreign Key

Then comes the foreign key. For now you may visualize this as the Primary Key of another, yet unnamed table. The foreign key is not unique in this table, but in the other table. Since we are creating a single table we need not worry about this. But when you have a number of tables and they are related, you will need a foreign key.

Integrity Constraints

Integrity constraints refers to whether data must exist in a column, or need not, and if it exists, how it must exist. It is easy to understand how this constraint represents the underlying reality and logic. When a person is employed, he is immediately given an employee identification number, assuming that a database has been established to hold employee information. Also, for any employee there must be at least a last name. He/she may or may not have middle name but most of them have a first name as well.

Also, at the time the employee is hired, there is definitely a date specified (joining date), otherwise he /she may not get his /her pay check, so there must be a hire_date for all employees. Probably his salary is known as well; still, you may assume that it is not known, but will be known later. So some of these columns must contain values and the others may be empty now but will be filled if they exist. This is called a NOT NULL Constraint. The Employee_ID, his last name, and the date he was hired cannot contain null values; the other columns may be null.

There are different kinds of constraints. Oracle admits of six different kinds of constraints, out of which three of them will be used for this present example. These integrity constraints are listed in the next paragraph.

Not Null Constraint prohibits columns from having a NULL.
Unique Constraint: does not allow multiple rows to have the same value.
Primary Key Constraint: combines the nature of Not NULL and Unique in one.
Foreign Key Constraint
Check Constraint
REF Constraint

{mospagebreak title=Creating a table using the Wizard}

From Start –>AllPrograms–>Oracle 10g XE Express Edition, open up the login page by clicking on Go to database Home Page. This opens up the login web page shown in the next picture.

Click on the arrow on the Object Browser and from the drop down click on Create.

This opens the create table window as shown. You are also provided a listing of all existing tables which should help you to not use an existing table name. To see all the tables, you need to click on the indicated place, which will reveal two frames as shown (a list of tables will be shown when you click in the indicated place).

On the left hand side of the right hand frame you have the various Wizard steps. First and foremost is a name for the table. The table will be called Htek_Employees. By concatenating Htek_ to Employees you are avoiding two pitfalls: you are not giving a duplicate name (an Employees table already exists) and you are not using a keyword. The table name will be typed with all characters capitalized as shown. Clicking on the red star to the left opens up a little pop-up, giving you a hint as to what constitutes a good name for the table.

{mospagebreak title=Creating a table using the Wizard, continued}

The next item is the column, which you enter. Let’s start with the entry EMPLOYEE_ID. Click on the related Select Datatype drop-down which reveals all the available data types as shown.

Among the data types, some of them are not allowed by Oracle to be the data type for a column designated as a Primary Key column. Let’s say I use CLOB (Character Large Object) as the data-type; also assume only one column to be present and further designate that one column as the primary key. At the time the table is created I would get an error message as shown in the next paragraph. You may test and verify the other data types, or simply look up Oracle reference.

In the present case, a NUMBER type will be used for the EMPLOYEE_ID. The syntax for the NUMBER data type is as shown here:

NUMBER(
Total number of digits,
number of digits after the decimal point.
)

For the column with NUMBER(4,2) data type you can store 2, 22, 2.22, 22.2, 22.22 etc. You may try to save 25.345, but the value that will be stored will be 25.35. It rounds up to the two digits after the decimal point. The range of values you can store is from -99.99 to 99.99, including 0 as shown for a table called “test.”

The EMPLOYEE_ID will be of data type NUMBER(4,2), and since we want to make sure that every Employee will have an ID, we also check “Not NULL.”

The next two columns will be assigned to FIRST_NAME and LAST_NAME attributes. In these columns alphabetic characters will be saved. There are two possible data types, CHAR and VARCHAR2. While CHAR stores a fixed length string, VARCHAR@ stores a variable length string up to the maximum specified. For strings like names where you do not know the number of characters, and which may vary from row to row, VarChar2 is the best. Besides alphabets it can store numerals, and some special characters as well. If you need more info you should consult the Oracle reference area.

As soon as you choose VARCHAR2, the Scale box shows up with 4000. This means you can put in no more than 4000 characters. For names, even for the ones like mine (Jayaram Krishnaswamy), a number like 20 should suffice. The same holds true for the LAST_NAME. The company insists the employee provide a last name, if not a full name, so LAST_NAME is Not NULL.

At the same time the other columns which take ‘strings‘ are added, column after column. While the Column ‘Notes’ may hold some larger sized text, 500 characters were specified. ‘State’ uses a two character notation, so the fixed length of two characters was specified. The IDE comes up with an initial 8 columns to which you may add more columns by clicking Add Column each time you want to add a column. At this point the table design appears as shown.

Now we fill in the column names for the two dates in our list, birth date and hire dates. These have to be of data type DATE, a data type that supports Julian Dates. Operations on the date can be performed in the database to add, subtract, or otherwise format dates. Of course Hire_Date must be known for all employees and therefore NOT NULL is checked.

Regarding the Health Insurance column, a desirable type would have been BOOLEAN, but this is not offered in Oracle 10G XE. The next best thing to use is Yes or NO with a CHAR(3) specified. As for salary, how about a salary of 50000.00 per month? A NUMBER(7,2) should take care of this. You could use the move up/down arrows at the left end to rearrange the column orders. The final column allotment is shown in this picture.

Now click on the Next button which takes you to Step 2, designating the Primary KEY column as shown. While you can create a table without a Primary Key, for relational data management reasons you better set up a primary key. In the next paragraph I quote from the IDE about the explanation of the Primary Key. In other database products there is what is called an auto-increment item which increments automatically as rows are added to a new value. This is ideal for a Primary Key Column, but it does not exist in Oracle 10G XE. However it is handled equally efficiently by what is know as the ‘Sequence,‘ an existing sequence or a new sequence.

A primary key allows each row in
a table to be uniquely identified.
If you select to populate your
primary key from a
new sequence, you will be prompted
to enter the new sequence's
name. If you select to populate
your primary key from an existing
sequence, you will be prompted
to select the sequence. Both
these methods result in the
generation of a trigger against your
table. You can also select to
not populate your primary. This is
the only method that allows you to
define a composite primary key
which is a primary key made up
of more than one column.

Here the default naming for the Primary Key Constraint Name and the Sequence Name are accepted. The Primary Key designated is Employee_ID as shown in the next picture.

Click on the Next button which takes you to the third step, where you designate the foreign key columns. Since we will be using only one table, this step will be skipped. The design can be altered later and we may choose to add a column if necessary. The next picture is from Oracle 10G XE. It is an explanation for the foreign key and how it is handled.

In case you do need to designate a column as a foreign key of another table, you need to make proper entries to this window. As the window heading implies, there could be more than one foreign key.

Click on the Next Button which opens up the next window (actually the web page is not changed but the ‘page’ moves to next) as in Visual Studio’s WIZARD control as shown.

We are not imposing any other constraint for now, and this step too can be skipped. However clicking on the Available Columns and Example Check Constraints lets you impose any type of appropriate condition, like Hire_Date cannot begin before such-and such a date, and so on. This is a useful feature for a novice designer.

Now finally click on the button Finish to display the following window. In this window you need to confirm your request. When you click on the SQL button, it opens a frame showing the SQL statement to create a table. Using this script you could have created this table by executing the statement, another way to create a table.

When you click on the create button, the table will be created if there are no errors in the definition. The table now makes its way to the object browser as shown. Presently the table is empty, but may be populated using the IDE or using script.

Summary

The create table wizard provided with Oracle 10G XE is an extremely useful interface for creating tables with all the necessary details required by a table or tables in a RDBM system. Oracle XE also provides the SQL interface which may also be used for creating tables, however, the Wizard gives the necessary step-by-step guidance to beginners getting used to the database. The absence of a binary type and the auto-increment data type are characteristics which are different from some of the other SQL Server products. It may be interesting to review the data type mapping between SQL Server and Oracle XE discussed in a previous article.