Introduction

Data Types

To create a table, you first must decide what kind of data you will want in your table. As an example, think of a table that holds names, person numbers, and ages of different people. You might want to store the first and last names separately. You will have to decide on a name for the table, as well as names for the data fields (columns) which it holds. So we want to create the following table:

In addition to giving names to the data fields (columns), we need to specify their data type.

Oracle Data Types

In our PEOPLE example table, FIRSTNAME and LASTNAME should be variable length strings, since people's names have different lengths. Since we need to allocate enough space to hold all characters in a person's first and last name, the type should be varchar(20). This allows for up to 20 characters in a name. The PERSONNUMBER field should be an integer since it won't ever be a floating point number. The AGE field should be an integer(3) since we won't keep track of fractions of years. We can limit the number of digits to 3 because there probably won't be anyone in our database that is more than 999 years old. So the table we wish to create looks like this:

Constraints

Now we have to consider how this table will relate to other tables, and vice versa. When other tables try to look up a record in this table, what field(s) will they use to distinguish between entries in the database? The collection of such fields is called the primary key. No two records in the same table can have identical primary keys, because that would make them indistinguishable. Also, none of the fields making up the primary key can be null. In other words, all the fields which are part of the primary key must be defined. The natural primary key in our example would be PERSONNUMBER. Note: A table can be made without any primary key, but this is dangerous and is usually poor database design.

To create the example table in sqlplus, type:

create table PERSON(
FIRSTNAME varchar(20), 
LASTNAME varchar(20), 
PERSONNUMBER number primary key,
AGE number(3)
);

Another way to do this is:

create table PERSON(
FIRSTNAME varchar(20), 
LASTNAME varchar(20), 
PERSONNUMBER number, 
AGE number(3)
   primary key(PERSONNUMBER)
);

It is possible to program your table to make sure that it is given meaningful data. For example, we know that the AGE and PERSONNUMBER fields should never be negative. We can tell the database hold all entries to this constraint with the check keyword. The resulting SQL command would be:

create table PERSON(
   FIRSTNAME varchar(20),
   LASTNAME varchar(20),
   PERSONNUMBER number primary key check(PERSONNUMBER >= 0),
   AGE number(3) check(AGE >= 0)
);

or

create table PERSON(
   FIRSTNAME varchar(20),
   LASTNAME varchar(20),
   PERSONNUMBER number check(PERSONNUMBER >= 0),
   AGE number(3) check(AGE >= 0),
   primary key(PERSONNUMBER)
);

check is just one type of constraint. Here is a list of the constraints available in Oracle:

primary key

The primary key constraint requires the data in the specified column to be not null. Note that there can be only one primary key per row, but a primary key may be made up of multiple columns. Every primary key must be unique, but that does not mean each individual column in the primary key holds unique data. Example:

Say you made a table with two columns (named A and B), and both columns make up the primary key. The following table entries would be valid:

A   B
-----
1   3
1   1
3   1
3   3

But the following would not be valid:

A   B
-----
1   1
1   1

So the individual parts of a primary key can be equal, but the combinations of these parts (the entire primary key) must be unique.

If you need only one column in your primary key, you can put "primary key" immediately after the column declaration:

PERSONNUMBER number primary key,

If you need more than one column in your primary key, you may not do the following:

PERSONNUMBER number primary key, AGE number(3) primary key

That says to create two primary keys for this table, which is not allowed. A table can have no more than one primary key.

Instead, you would have to use the following syntax:

PERSONNUMBER number, AGE number(3), primary key(PERSONNUMBER, AGE)

foreign key

When you want one table to be able to refer to another, you will need a foreign key. Let's say table A needs to refer to a record in table B. Table A will need a foreign key which corresponds to a primary key in table B. That way, when table A refers to table B with a foreign key, it is guaranteed that at most one record in B will match the key. This example in more detail:

create table A (
   x number,
   y number,
   z number foreign key
);

create table B (
   n number primary key,
   i number,
   d varchar(30)
);

Here, z is the foreign key in table A which is mapped to the primary key in table B. We can be even more specific in our declaration of table A, explicitly saying that the foreign key z references table B.

create table A (
   x number,
   y number,
   z number foreign key references B
);

It is possible to take this a step further and specify that the foreign key in A refers to the primary key made up of n.

create table A (
   x number,
   y number,
   z number foreign key references B(n)
);

If we wanted the primary key in B to be both n and i, we would need to make a corresponding change in the foreign key definition in A. Lets make x and z the foreign key which correlate to the primary key in A.

create table A (
   x number,
   y number,
   z number,
   foreign key(x, z) references B
);

create table B (
   n number,
   i number,
   d varchar(30)
   primary key(n, i)
);

Note that it is possible to have more than one foreign key in a table. If we had said:

create table A (
   x number foreign key(x, z) references B,
   y number,
   z number foreign key(x, z) references B,
);

...instead, that would mean to make two foreign keys in A which refer to table B, but this would not work because the primary key of B has two attributes (n and i). The foreign key should match the primary key's attribute number and type.

The application of foreign keys will be described later in this tutorial under the topic of select and in.

create table A (
   n number not null,
   i number
);

create table A (
   n number,
   i number,
   not null(n)
);

create table A (
   n number not null,
   i number not null
);

create table A (
   n number,
   i number,
   not null(n),
   not null(i)
);

create table A (
   n number,  
   i number,
   not null(n, i)
);

create table A (
   n number unique,
   i number
);

create table A (
   n number,
   i number,
   unique(n)
);

create table A (
   n number unique,
   i number unique 
);

create table A (
   n number,
   i number,
   unique(n),
   unique(i)
);

create table A (
   n number,
   i number,
   unique(n, i)
);

create table PERSON(
   FIRSTNAME varchar(20),
   LASTNAME varchar(20),
   PERSONNUMBER number check(PERSONNUMBER >= 0),
   AGE number(3) check(AGE >= 0),
   primary key(PERSONNUMBER)
);

create table tuple (
   i number,
   n number,
   check (i + n < 100)
);