Creating A Database Table
Naming Database Objects
Data names in Oracle must conform to the following rules:
- the name must begin with a letter
- maximum length is 30 characters
- names may contain letters, numbers, $, # and _
- can not be an SQL reserved word
- names are not case sensitive
Data names that do not follow the above rules must be enclosed in
double
quotes ("). For example, "STOCK HOLDING" would be a valid table
name.
Data Types
The basic data types available in Oracle are:
NUMBER(m,n): number containing a total number of m digits with n digits after the decimal.
CHAR(n): a character string of length n. n is a positive integer not exceeding 255. Default is 1.
VARCHAR2(n): a varying-length character string of maximum length n. Maximum length is 4000 bytes.
DATE: a variable containing the date and time; Oracle stores the century, year, month, day, hour, minute and second.
LONG: variable length
character data up to 2 gigabytes
CLOB: character data
up to
4 gigabytes
RAW or LONG RAW: binary data
BLOB:
binary data up to 4 gigabytes
BFILE: binary data
stored
in external file up to 4 gigabytes
ROWID:
a 64 base number system unique address of
table row
TIMESTAMP: date with
fractional seconds
INTERVAL YEAR TO
MONTH:
interval of years and months
INTERVAL DAY TO
SECOND:
interval of days to hours/min/sec.
TIMESTAMP WITH TIME
ZONE
TIMESTAMP WITH LOCAL TIME
ZONE
The CREATE TABLE Command
A table is defined using the CREATE TABLE command:
Basic Format:
CREATE TABLE table-name
(column-name1 data-type1 [DEFAULT default-value]
[,column-name2 data-type2 [DEFAULT default-value]]....)
DEFAULT: gives a default value for a column if no value is entered
Example:
To create the STOCK table:
create table stock
(stocksymbol
char(3),
stockname char(20),
currentprice number(6,2));
Specifying Constraints
Constraints can be specified either for a column or for a table. Column constraints are coded after the column entry; for example:
currentprice number(7,2) not null
Table constraints are coded after all the column entries, separated by commas.
Specifying Column Constraints
A constraint can be coded as a column constraint only if it refers to a single column. For example, a CHECK constraint that does a cross check on two or more columns must be coded as a table constraint.
Format for column constraints:
[CONSTRAINT constraint-name] column-constraint
The constraint-name is used to reference the
constraint.
This clause is optional; if omitted, the system will generate a name (SYS_Cn
format).
One naming convention that could be used to name constraints is
“Table short name_Column name_constraint type” (constraint type nn,
Column Constraints:
NULL, NOT NULL: use to
specify
whether a column may contain null values or not; default is NULL
UNIQUE: use to specify that a column must contain unique values
PRIMARY KEY: use to specify a primary key for the table
REFERENCES: use to specify a foreign key
Format:
REFERENCES table-name [ON DELETE CASCADE]
CHECK (condition): use to specify a validation rule
Foreign keys and check conditions are discussed in the section: Specifying Table Constraints.
The following could be used to define the STOCK table:
create table stock
(stocksymbol
char(3) primary key, stockname char(20),
currentprice number(6,2) not null);
Specifying Table Constraints
Table constraints are specified after the last column entry.
Specifying the Primary Key
Multi-column primary keys must be specified as a table constraint; for single column keys, either a column or table constraint can be used.
Oracle creates a unique index on the column or columns specified. Only one primary key can be specified for a table and the corresponding column(s) must have unique data values.
Format:
CONSTRAINT constraint-name PRIMARY KEY (col1 [,col2 ...])
col1, col2...: specify the column(s) that make up the primary key.
If there
is more than one column, separate the entries by commas.
Specifying Foreign Keys
If the foreign key consists of a single column, a column or table constraint can be used.
Format:
CONSTRAINT constraint-name FOREIGN KEY (col1
[,col2...])
REFERENCES table-name [ON DELETE CASCADE]
This clause specifies one or more columns in the child table that must have a corresponding value in the parent table. The foreign key is related to the primary key of the parent table; a primary key must be specified for that table.
col1, col2..: specifies the column(s) that make up the foreign key. If there is more than one column, separate the column names by commas.
REFERENCES table-name: specifies the name of the parent table on which the constraint is being constructed.
ON DELETE CASCADE: specifies that if a row in a parent table is
deleted,
related rows in the child table are automatically deleted as well.
Specifying Check Constraints
CONSTRAINT constraint-name CHECK (check-condition)
check-condition: a condition involving a column in the table;
it
cannot contain subqueries or column functions
Examples:
The following CREATE TABLE commands could be used to create the remaining three tables in the INVESTMENTS database. Some examples of constraints are included.
For the MANAGER table, the manager number column is specified as the primary key; a check constraint is coded for the SALARY column:
create table manager
(managerno
char(2) primary key, managername char(15) not null,
salary number(8,2) check (salary between 0 and 100000));
For the PORTFOLIO table, the portfolio number is specified as the primary key and a referential constraint is specified for the manager number column.
create table portfolio
(portfoliono char(4) primary key,
managerno char(2) not null,
cash number(8,2),
constraint fkeymgr foreign key (managerno) references manager,
constraint checkcash check (cash > 0));
The CREATE TABLE command for the HOLDING table specifies a primary key consisting of the portfolio number, stock symbol and purchase date columns. Two referential restraint clauses are included since it has two parent tables - the PORTFOLIO table and the STOCK table. The foreign key clause for the portfolio number column specifies if a parent row is deleted, corresponding rows in the HOLDING table will also be deleted. The foreign key clause for the stock number specifies that a row in the STOCK table may not be deleted if there are corresponding child rows in the HOLDING table.
create table holding
(portfoliono char(4), stocksymbol char(3), purchasedate date,
numbershares number(6,0) not null,
purchaseprice number(6,2) not null,
primary key (portfoliono, stocksymbol, purchasedate),
constraint fkeypfno foreign key (portfoliono) references portfolio,
constraint fkeystock foreign key (stocksymbol) references stock);
The CREATE TABLE
command could also
contain a subquery; to generate the structure and content immediately.
create
table mgr2 as
(select portfoliono, cash from
portfolio where
managerno = 'M2');
Altering a Table
After a table has been defined with CREATE TABLE, changes can be made to the structure of the table using the ALTER TABLE command.
The user can add a column to the table, delete a column or modify the data type of a column; constraints can be added or dropped.
To add a column:
ALTER TABLE tablename ADD column-definition
For example, to add a column called HIREDATE to the MANAGER table, the following command could be used:
alter table manager add hiredate date not null;
The new column is placed logically at the right of the table. Values
for the
column are initialized to NULL.
To delete a column:
ALTER TABLE tablename DROP COLUMN column-name
or
ALTER TABLE tablename DROP (column-name)
For example, to delete the hiredate column in the manager table:
alter table manager drop column hiredate;
To modify the description of an existing column, user the following:
ALTER TABLE tablename MODIFY column-definition
For example, to change the width of the manager name column to 20 and allow nulls, the following command could be used:
alter table manager modify managername char(20)
null;
To remove the ability to
use an existing
column, and chose a more performance-appropriate time to delete it, use
the
following:
ALTER TABLE
tablename SET UNUSED
(column-definition)
ALTER
TABLE tablename DROP UNUSED COLUMNS
Dropping Constraints:
To remove a constraint, use the following format of the ALTER command:
ALTER TABLE tablename DROP {PRIMARY KEY | CONSTRAINT constraint-name}
Examples:
To drop the primary key on the MANAGER table:
alter table manager drop primary key;
To drop the foreign key on the PORTFOLIO table:
alter table portfolio drop constraint fkeymgr;
Adding Constraints:
To add a constraint to a table, use the following format of the ALTER command:
ALTER TABLE tablename ADD constraint-definition
Use the same syntax for the constraint as used in the CREATE TABLE command.
For example, suppose the manager number was not specified as a foreign key when the PORTFOLIO table was created. The following command would add the foreign key:
alter table portfolio add constraint fkeymgr foreign key (managerno) references manager;
Disabling Constraints:
To deactivate a constraint:
ALTER TABLE
tablename DISABLE CONSTRAINT
constraint-name
Enable Constraints:
To activate a previously disabled
constraint:
ALTER TABLE tablename ENABLE
CONSTRAINT constraint-name
Deleting a Table
To delete a table from the database, the DROP TABLE command is used. This command deletes the table entry and any dependent entries - for example, views, indexes etc. Both the table description and table data are deleted.
DROP TABLE table-name
Renaming a Table
The RENAME statement can be used to rename a table (this command can also be used for views and synonyms):
RENAME old-name TO new-name
Truncating
a Table
The TRUNCATE statement can be used to
truncate the
rows in a table (Unlike the DML “Delete * from tablename” this DDL
command (Cannot use rollback) resets the
high water mark while removing all rows from a table; thus resetting a
limit
used for Full Table Scans.):
TRUNCATE TABLE
table-name
Adding
Comments to a Table
The Comment command can be used to save
a comment
with a table:
COMMENT ON TABLE table-name is
'This is a comment';