HomeOracle Page 2 - Creating a Table using Oracle 10G XE
Creating a table - Oracle
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.
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.
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.
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 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