Managing tables.

Oracle datatypes.

Date arithmetic

• subtract two DATE values and get the difference in days. For example,

  SELECT sysdate - to_date('27-07-2003')
    FROM dual;

• add or subtract a numeric value to DATE datatypes. In this case the numeric value is to be treated as number of days to add/subtract from the date represented by the DATE value. For example,

  SELECT localtimestamp - 2.25
    FROM dual;

• add or subtract an INTERVAL value to a DATE value. For example,

  SELECT sysdate + to_yminterval('1-6')
    FROM dual;

• multiply or divide an INTERVAL datatype by a number.
• add or subtract two INTERVAL values. Check if we can operate on two different INTERVAL datatypes.

Creating tables

CREATE TABLE table_name (
  column_name datatype [[CONSTRAINT constraint_name] column_constraint] [default_value],
  column_name datatype [[CONSTRAINT constraint_name] column_constraint] [default_value],
  ...
  column_name datatype [[CONSTRAINT constraint_name] column_constraint] [default_value]
  [, list_of_table_constraints]
);

CREATE TABLE products (
  pid       CHAR(4),
  name      VARCHAR2(32),
  color     VARCHAR2(12),
  price     NUMBER(8,2),
  min_price NUMBER(8,2)
);

DESC products;

Column-level constraints and default values

CREATE TABLE products (
  pid       CHAR(4),
  name      VARCHAR2(32),
  color     VARCHAR2(12) DEFAULT 'Red',
  price     NUMBER(8,2) DEFAULT 19.99,
  min_price NUMBER(8,2)
);

For the default values we can use functions SYSDATE, SYSTIMESTAMP, USER, USERENV, and UID. For example

CREATE TABLE records (
  sid   CHAR(8),
  login CHAR(16) DEFAULT USER
);

Defining a column in a table we can also specify some column-level constraints; that is, requirements for the values this column stores. The list of constraints supported by Oracle is given in the table.

Oracle column-level constraints.

Constraint	Description
NOT NULL	This constraint does not allow (or allow) to insert a NULL value into this field. The general syntax for the constraint is [CONSTRAINT constraint_name] [NOT] NULL If there is no constraint name specified, a system-generated name will be given to the constraint. Example: CREATE TABLE products ( ... name VARCHAR2(32) CONSTRAINT products_notnull_name NOT NULL, ... );
UNIQUE	This constraint ensures that no two rows in a table duplicate data in the protected column. Oracle creates a unique index to enforce the uniqueness. This constraint allows NULL values in the constraint column. The general syntax for the constraint is [CONSTRAINT constraint_name] UNIQUE For example CREATE TABLE users ( user_id NUMBER(6), user_name CHAR(16) CONSTRAINT users_unique_name UNIQUE, ... );
CHECK	This constraint specifies some condition that values of the protected column should satisfy. The condition cannot use queries; environment functions (such as SYSDATE, USER, etc) and pseudo-columns (such as ROWID, CURVAL, etc) cannot be used to evaluate in the check condition. The general syntax for this constraint is [CONSTRAINT constraint_name] CHECK (condition) For example, CREATE TABLE products ( ... price NUMBER(9,2) CHECK (price > 0), ... );
PRIMARY KEY	This constraint is more restricted version of the UNIQUE constraint. The column with the PRIMARY KEY constraint may not include NULL values. A table may contain only one PRIMARY KEY. The general syntax for this constraint is [CONSTRAINT constraint_name] PRIMARY KEY For example CREATE TABLE products ( pid CHAR(4) CONSTRAINT products_pk PRIMARY KEY, ... );
FOREIGN KEY	A foreign key constraint protects one or more column in a table by ensuring that for each non-NULL value there is data available elsewhere in the database with a primary key or unique key. The foreign key is the column (or columns) in the table (child table) where the constraint is created. The referenced key is the primary key or unique key column (or columns) in the table (parent table) that is referenced by the constraint. The column datatypes in the parent table and in child table should match. The general syntax for this constraint is [CONSTRAINT constraint_name] REFERENCES [schema.]table [(colunm)] [ON DELETE {CASCADE | SET NULL}] We can omit the column name in the parent table if we reference to the primary key. The ON DELETE clause specifies the action to be taken when a row in the parent table is deleted and child rows exist for the deleted parent primary key. We can either delete all child rows referencing the deleted parent row (CASCADE) or set their value to NULL (SET NULL). If we omit this clause, Oracle will not allow us to delete rows from the parent table if there are rows in the child table referencing these rows. Example CREATE TABLE orders ( prod_id CHAR(4) CONSTRAINT orders_prod_fk FOREING KEY REFERENCES products ON DELETE CASCADE, pfj_id CHAR(4) CONSTRAINT orders_proj_fk FOREING KEY REFERENCES projects ON DELETE CASCADE, supp_id CHAR(4) CONSTRAINT orders_supp_fk FOREING KEY REFERENCES supplier.id ON DELETE CASCADE, qty NUMBER(6) );

Table-level constraints

[CONSTRAINT constraint_name] UNIQUE (column, column, ... )

CREATE TABLE registration (
  ...
  CONSTRAINT registration_uq_reg UNIQUE (sid, cid)
);  

[CONSTRAINT constraint_name] CHECK (condition)

CREATE TABLE products (
  ...
  CONSTRAINT products_min_price CHECK ( min_price < price )
);  

[CONSTRAINT constraint_name] PRIMARY KEY (column, column, ...)

CREATE TABLE orders (
  ...
  CONSTRAINT orders_pk PRIMARY KEY (prod_id, prj_id, supp_id)
);

[CONSTRAINT constraint_name] (column, column, ...)
   REFERENCES [schema.]table [(colunm, column, ...)]
   [ON DELETE {CASCADE | SET NULL}]

CREATE TABLE atlas (
  ...
  CONSTRAINT atlas_fk (year, term) REFERENCES semester (year, term)
);

Creating a table from another table

CREATE TABLE table_name [(column_names)] AS
 SELECT query;

-- create a new table with exactly the same structure, but no data
CREATE TABLE employees_archive AS
 SELECT *
   FROM employees
  WHERE 1 = 2;

Adding comments

COMMENT ON {TABLE | COLUMN} fullname IS 'comments';

COMMENT ON TABLE orders
 IS 'Order information includes: which project ordered what parts from which supplier and how many';
COMMENT ON COLUMN supplier.status
 IS 'It is a numeric coefficient between 0 and 100 that indicates how reliable this supplier is. 100 is the best.';

• USER_TABLES
• ALL_TABLES
• USER_TAB_COLUMNS
• ALL_TAB_COLUMNS

Creating a temporary table

CREATE GLOBAL TEMPORARY TABLE table_name (
  table definition
)
ON COMMIT {PRESERVE | DELETE} ROWS;

CREATE GLOBAL TEMPORARY TABLE emp_bonus_temp (
  ssn   NUMBER(9),
  bonus NUMBER(12, 2)
)
ON COMMIT PRESERVE ROWS;

Modifying tables

Adding columns

ALTER TABLE [schema.]table_name
  ADD { column definition | ( list_column_definitions )};

ALTER TABLE projects
  ADD manager_id NUMBER(9) CONSTRAINT projects_fk REFERENCES employees (ssn) ON DELETE SET NULL;

• if we add more than one column, we need to put the column definitions into parentheses and separate by comma;
• if we specify a DEFAULT value for a new column, all the rows in the table will have the default value automatically assigned;
• if the table already contains rows, we cannot specify the NOT NULL constraint. Although, we can use this constraint together with a DEFAULT value set.

Modifying columns

ALTER TABLE [schema.]table_name
  MODIFY { column_name new_column_attributes | ( list_of_column_names_and_attributes ) } ;

ALTER TABLE orders
     MODIFY ( quantity NUMBER(8), status DEFAULT 'in process' );

There are some rules for modifying column definitions:

• We can increase the length of a character column and precision of a numeric column. Although, for big tables increasing length of a CHAR column will require a lot of resources, because the column data for all the rows needs to be blank-padded.
• We may decrease the length of a VARCHAR2 column and reduce the precision or the scale of a numeric column, if all of the data in the column fits the new length.
• We may decrease the length of a nonempty CHAR column, if the parameter BLANK_TRIMMING is set to TRUE.
• All the column values must be NULL to change its datatype. Although, we may change the datatype from CHAR to VARCHAR2 (and vice versa) even for nonempty if we do not decrease the length.

Dropping columns

ALTER TABLE [schema.]table_name
 DROP { COLUMN column_name | ( list_of_column_names ) }
 [ CASCADE CONSTRAINTS ];

The syntax for marking a column as unused is

ALTER TABLE [schema.]table_name
  SET UNUSED { COLUMN column_name | ( list_of_column_names ) }
  [ CASCADE CONSTRAINTS ];

ALTER TABLE orders
  SET UNUSED COLUMN status;

To drop the columns marked as unused, we use the command

ALTER TABLE [schema.]table_name
 DROP { UNUSED COLUMNS | COLUMNS CONTINUE };

Renaming tables

ALTER TABLE [schema.]table_name
    RENAME TO new_table_name;

ALTER TABLE orders RENAME TO spj;

We can also use the RENAME command to change a name of a table, but this command allows to change table names only for the current schema. The general syntax is

RENAME old_table_name TO new_table_name;

• Oracle automatically transfers integrity constraints, indexes, and all privileges on the table;
• but Oracle invalidates all objects that depend on the renamed table (such as views, synonyms, stored procedures, etc).

Adding table-level constraints

ALTER TABLE [schema.]table_name
  ADD CONSTRAINT constraint_name constraint_definition;

ALTER TABLE records
  ADD CONSTRAINT records_semester_fk
  FOREIGN KEY (year, term) REFERENCES semester (year, term) ON DELETE CASCADE;

Enabling and disabling constraints

ALTER TABLE [schema.]table_name
  DISABLE { CONSTRAINT constraint_name | UNIQUE column_name | PRIMARY KEY }
  [ CASCADE ];

ALTER TABLE projects
  DISABLE CONSTRAINT projects_fk;

Using the ALTER TABLE statement we can also enable previously disabled constraint by using the syntax

ALTER TABLE [schema.]table_name
  ENABLE { CONSTRAINT constraint_name | UNIQUE column_name | PRIMARY KEY };

Dropping constraints

ALTER TABLE [schema.]table_name
  DROP { CONSTRAINT constraint_name | UNIQUE column_name | PRIMARY KEY }
  [ CASCADE ];

ALTER TABLE bonus
  DROP PRIMARY KEY CASCADE;

ALTER TABLE [schema.]table_name
  MODIFY CONSTRAINT constraint_name status;

ALTER TABLE departments MODIFY CONSTRAINT departments_fk DISABLE VALIDATE;

ALTER TABLE customer
  ADD CONSTRAINT customer_pk PRIMARY KEY (cust_id) DEFERRABLE;

SET CONSTRAINT[S] { ALL | constraint_list }
  { IMMEDIATE | DEFERRED };

ALTER SESSION SET CONSTRAINT[S] { ALL | constraint_list }
  { IMMEDIATE | DEFERRED };

SET CONSTRAINT customer_pk DEFERRED;

INITIALLY { IMMEDIATE | DEFERRED }

ALTER TABLE customer
  ADD CONSTRAINT customer_pk PRIMARY KEY (cust_id)
  DEFERRABLE INITIALLY DEFFERED;

ALTER TABLE customer
  MODIFY CONSTRAINT customer_pk INITIALLY IMMEDIATE;

DROP TABLE [schema.]table_name [ CASCADE CONSTRAINTS ];