Oracle TABLE

Tables are the basic unit of data storage in an Oracle Database. Data is stored in rows and columns. You define a table with a table name, such as employees, and a set of columns. You give each column a column name, such as employee_id, last_name, and job_id; a datatype, such as VARCHAR2, DATE, or NUMBER; and a width. The width can be predetermined by the datatype, as in DATE. If columns are of the NUMBER datatype, define precision and scale instead of width. A row is a collection of column information corresponding to a single record.

You can specify rules for each column of a table. These rules are called integrity constraints. One example is a NOT NULL integrity constraint. This constraint forces the column to contain a value in every row.

You can also invoke transparent data encryption to encrypt data before storing it in the datafile. Then, if users attempt to circumvent the database access control mechanisms by looking inside datafiles directly with operating system tools, encryption prevents these users from viewing sensitive data.

After you create a table, insert rows of data using SQL statements. Table data can then be queried, deleted, or updated using SQL.

Creating Tables

Before tables can be used, they have to be created and this is generally the job of the DBA. Creating tables is done with the create table command. The create table command does the following:

1. Defines the table name.
2. Defines the columns in the table and the datatypes of those columns.
3. Defines what tablespace the table resides in (optional).
4. Defines other characteristics of the table (optional).

1. Create table with data type: VARCHAR2, Date, Number(8,2)

SQL> create table Employee(
  2    ID                 VARCHAR2(4 BYTE)         NOT NULL,
  3    First_Name         VARCHAR2(20 BYTE),
  4    Last_Name          VARCHAR2(20 BYTE),
  5    Start_Date         DATE,
  6    End_Date           DATE,
  7    Salary             Number(8,2),
  8    City               VARCHAR2(20 BYTE),
  9    Description        VARCHAR2(80 BYTE)
 10  )
 11  /

Table created.

2. Creating Table with combined primary key

SQL> create table employee_history
  2  (employee_id       number(6) not null,
  3   salary            number(8,2),
  4   hire_date         date default sysdate,
  5   termination_date  date,
  6   termination_desc varchar2(4000),
  7   constraint emphistory_pk
  8    primary key (employee_id, hire_date)
  9  );

Table created.

3. Create table with foreign key

SQL>     CREATE TABLE products
  2      (      product_id      numeric(10)     not null,
  3             supplier_id     numeric(10)     not null,
  4             CONSTRAINT fk_supplier
  5               FOREIGN KEY (supplier_id)
  6               REFERENCES supplier(supplier_id)
  7      );

Table created.

4. Creating Table and indicate tablespace

SQL> create table subjects (
  2  subject_id    number not null,
  3  subject_name  varchar2(30) not null,
  4  description   varchar2(4000)
  5  )
  6  tablespace users;

Table created.