Creating a Table using Oracle 10G XE - Creating a table using the Wizard, continued
(Page 4 of 4 )
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.
Creating table "HTEK_EMPLOYEES" failed.
Failed Creating Table ORA-02329: column of data
type LOB cannot be unique or a primary key ORA-00942:
table or view does not exist
ORA-06510: PL/SQL: unhandled user-defined exception
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.
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
