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:
PEOPLE
FIRSTNAME |
LASTNAME |
PERSONNUMBER |
AGE |
In addition to giving names to the data fields (columns),
we need to specify their data type.
Oracle Data Types
char(n) |
A fixed length string. The length of the string is n, where 1<=n<=255.
If n is not specified, the default length is 1. A zero length string is not allowed.
String literals are surrounded by single quotes. Example: 'Hello World!'
|
varchar2(n) |
A variable length string, whose maximum length is n, where 1<=n<=2000. |
date |
Holds a date. The date format is usually 'DD-MMM-YYYY' where DD is the number of the day,
MMM is a text-abreviation of the name of the month, and YYYY is the year. |
number |
Holds a number (real or integer) up to 40 spaces. |
number(n) |
Holds a number (real or integer) up to n spaces, where n > 0. |
number(n, d) |
Holds a real number up to n spaces, with d digits to the right of the decimal point. |
integer |
Holds an integer up to 40 digits. |
integer(n) |
Holds an integer up to n digits, where n > 0. |
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:
PEOPLE
varchar(20) FIRSTNAME |
varchar(20) LASTNAME |
integer PERSONNUMBER |
integer(3) AGE |
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:
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)
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.
not null
The constraint not null requires that a single attribute or set of attributes be not null. In Oracle, a null value (no matter what specific data type you're dealing with) is NULL. For example, say we had a table A with attributes n and i, and we wanted n to be not null. The SQL for this table is:
create table A (
n number not null,
i number
);
or
create table A (
n number,
i number,
not null(n)
);
If we wanted neither attribute to be null, the SQL for A would be:
create table A (
n number not null,
i number not null
);
or
create table A (
n number,
i number,
not null(n),
not null(i)
);
If we wanted the combination of n and i to be not null - that is, where n xor i may be null, but n and i must not be null, the SQL for A would be:
create table A (
n number,
i number,
not null(n, i)
);
The constraint unique works similarly to not null. If you wanted attribute n to be unique, the SQL for A would be:
create table A (
n number unique,
i number
);
or
create table A (
n number,
i number,
unique(n)
);
If we wanted both attributes to be unique, the SQL for A would be:
create table A (
n number unique,
i number unique
);
or
create table A (
n number,
i number,
unique(n),
unique(i)
);
If we wanted the combination of n and i to be unique - that is, where n might not be unique and i might not be unique, but no repeated combinations of n and i occur, the SQL for A would be:
create table A (
n number,
i number,
unique(n, i)
);
The check constraint will test a boolean expression whenever an attribute is created or modified. If the test fails, so will the modification of the attribute. For example, when we defined the people table to be:
create table PERSON(
FIRSTNAME varchar(20),
LASTNAME varchar(20),
PERSONNUMBER number check(PERSONNUMBER >= 0),
AGE number(3) check(AGE >= 0),
primary key(PERSONNUMBER)
);
We used check(AGE >= 0) to make sure that the AGE field in our table has a value that is greater than, or equal to 0. There is also a way to check combinations of fields. As another exmaple, say we have a table named tuple with two numbers, i and n. If we wanted to make sure that the sum of i and n is always less than 100, tuple could be created with:
create table tuple (
i number,
n number,
check (i + n < 100)
);
- http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/datatype....