All about table and constraints in Oracle

All about table and constraints in Oracle

CREATING ORACLE DATABASE OBJECTS

CREATING TEMPORARY TABLES

 

SQL>CREATE GLOBAL TEMPORARY TABLE TEMP_EMP (EMPNO NUMBER,ENAME VARCHAR2(10))

 

CREATING ONE TABLE WITH DATA FROM ANOTHER

 

SQL> create table emp_copy(empno,sal) as select empno,sal from emp;

 

SQL> CREATE TABLE employee_new   2 AS SELECT * FROM employees   3 PARALLEL DEGREE 4   4*NOLOGGING; Table created.SQL>

 

 

TEXT DATATYPE EXPLAINED

 

SQL> select vsize(sal) from emp;

 

ADDING AND MODIFYING AND DROPPING COLUMNS

 

SQL> alter table employee add (hire_date date);

SQL> alter table products modify(lastname varchar2(30));

SQL> alter table employee set unused column salary;

SQL> alter table employee drop unused columns;

SQL> alter table employee drop column salary;

 

If you think that the large number of rows in a table could potentially exhaust the undo space, you can drop a column with the optional CHECKPOINT clause. This will reduce the generation of undo data while dropping the column by applying checkpoints after a certain number of rows. Here’s an example that makes the database apply a checkpoint each time it removes 10,000 rows in the emp table:

SQL> ALTER TABLE emp DROP UNUSED COLUMNS CHECKPOINT 10000;

 

 

DROPPING,RENAMING AND TRUNCATING TABLES

 

SQL> drop table emp;

SQL>drop table emp cascade constraints;

SQL> truncate table emp;

 

RENAMING TABLE

 

SQL> rename emp to emp_bak;

SQL>alter table emp rename to emp_bak;

 

COMMENTING OBJECTS

 

SQL> comment on table emp is

‘This is a table containing employees’;

 

CREATING TABLE

 

SQL>CREATE TABLE EMP

(EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,

ENAME VARCHAR2(10),

JOB VARCHAR2(9),

MGR NUMBER(4),

HIREDATE DATE,

SAL NUMBER(7,2),

COMM NUMBER(7,2),

DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT);

 

SQL>CREATE TABLE EMP (

EMPNO     NUMBER(4)     NOT NULL,

ENAME     VARCHAR2(10),

JOB       VARCHAR2(9),

MGR       NUMBER(4),

HIREDATE  DATE,

SAL       NUMBER(7,2) CHECK (SAL <=10000),

COMM      NUMBER(7,2),

DEPTNO    NUMBER(2),

CONSTRAINT PK_EMP

PRIMARY KEY ( EMPNO )

USING INDEX  PCTFREE 10

STORAGE(INITIAL 12288 NEXT 12288 PCTINCREASE 50 )

TABLESPACE SYSTEM)

TABLESPACE SYSTEM PCTUSED 40 PCTFREE 10

STORAGE(INITIAL 12288 NEXT 12288 PCTINCREASE 50 )

PARALLEL (DEGREE 1 INSTANCES 1) NOCACHE;

 

SQL>CREATE TABLE EMP (

ENAME VARCHAR2(90);

EMPNO NUMBER(20);

CONSTRAINT PK_NAMES PRIMARY KEY (ENAME,EMPNO));

 

Placing a Table in Read-Only Mode

You can make any table in an Oracle database a read-only table, which means that the database will not permit you to add, remove, or alter the data in any way. For example, if you have a configuration table that you want to keep safe from any changes by any users, you can change the status of the table to read-only.

Use the ALTER TABLE statement to place a table in the read-only mode. Here’s an example:

;SQL> ALTER TABLE test READ ONLY;

Once you place a table in read-only mode, the database won’t permit the following operations on that table:

• TRUNCATE TABLE
• SELECT FOR UPDATE
• Any DML operations
• ALTER TABLE ADD/MODIFY/RENAME/DROP COLUMN
• ALTER TABLE SET COLUMN UNUSED
• ALTER TABLE DROP/TRUNCATE/EXCHANGE (SUB)PARTITION
• ALTER TABLE UPGRADE INCLUDING DATA or ALTER TYPE CASCADE INCLUDING TABLE DATA for a type with read-only table dependents
• Online redefinition
• FLASHBACK TABLE

You can perform the following operations on a read-only table:

• SELECT
• CREATE/ALTER/DROP INDEX
• ALTER TABLE ADD/MODIFY/DROP/ENABLE/DISABLE CONSTRAINT
• ALTER TABLE for physical property changes
• ALTER TABLE MOVE
• RENAME TABLE and ALTER TABLE RENAME TO
• DROP TABLE

You can return a table to the normal read-write status by specifying the READ WRITE clause in the ALTER TABLE statement, as shown here:

SQL> ALTER TABLE test READ WRITE;

Examples of Table Compression

The following example shows how to enable compression for all operations on a table, which is what you’d want to do in an OLTP setting:

SQL> CREATE TABLE test     name varchar2(20)     address varchar2(50))     COMPRESS FOR ALL OPERATIONS;

You can use either of the following statements to enable compression for direct-path inserts only on a table:

SQL> CREATE TABLE test     name varchar2(20)      address varchar2(50))     COMPRESS

 

 

 

ADDING INTEGRITY CONSTRAINTS TO EXISTING TABLES

 

SQL> alter table emp add constraints pk_emp_01 primary key(empno);

SQL> alter table emp add constraints fk_emp_01 foreign key (deptno) references dept(deptno);

SQL> alter table emp add constraints uk_emp_o1 unique(empno);

SQL> alter table emp modify (ename not null);

 

DISABLING CONSTRAINTS

 

SQL> alter table emp disable primary key ;

SQL> alter table emp disable constraint pk_emp_01;

SQL> alter table emp disable primary key cascade;

 

ENABLING A DISABLED CONSTRAINTS

 

SQL> alter table department enable primary key;

SQL> alter table emp enable uk_emp_01;

 

WHEN EXISTING DATA IN A COLUMN VIOLATES A DISABLED CONSTRAINT

 

SQL> @@$ORACLE_HOME/rdbms/admin/utlexcpt;

Let us assume that there is a table where primary key constraint is enabled and we have inserted two rows with different values.

SQL>create table example_1(col1 number);

SQL>insert into example_1 values(10);

SQL> insert into example_1 values(1);

SQL>alter table example_1 add(constraint pk_01 primary key(col1));

 

Now we have disabled the constraint

SQL>alter table example_1 disable constraint pk_01;

SQL>insert into example_1 values(1);

Now the rows that causes  unique constraint violation will be inserted into exception table

SQL> alter table exp_1 enable constraint pk_01 exceptions into exceptions;

SQL>select e.row_id,a.col1 from exceptions e,example_1 a where e.row_id=a.rowid;

 

USING DEFFERED CONSTRAINT

 

DROP TABLE emp;   DROP TABLE dept;    CREATE TABLE dept (       deptno             NUMBER(2) NOT NULL     , dname              CHAR(14)     , loc                CHAR(13)     , CONSTRAINT dept_pk PRIMARY KEY (deptno)   );    INSERT INTO dept VALUES (10,’FINANCE’,’PITTSBURGH’);   INSERT INTO dept VALUES (20,’SALES’,’NEW YORK’);   INSERT INTO dept VALUES (30,’OPERATIONS’,’BOSTON’);    COMMIT;      CREATE TABLE emp (       empno               NUMBER(4) NOT NULL     , ename               CHAR(10)     , job                 CHAR(9)     , deptno              NUMBER(2) NOT NULL     , CONSTRAINT emp_fk1  FOREIGN KEY (deptno)                  REFERENCES dept (deptno)                  DEFERRABLE                   INITIALLY IMMEDIATE     , CONSTRAINT emp_pk PRIMARY KEY (empno)   );    INSERT INTO emp VALUES (1001, ‘JEFF’, ‘PRESIDENT’, 10);   INSERT INTO emp VALUES (1002, ‘MELODY’, ‘MANAGER’, 30);   INSERT INTO emp VALUES (1003, ‘MARK’, ‘MANAGER’, 10);   INSERT INTO emp VALUES (1004, ‘MARTIN’, ‘MANAGER’, 20);    COMMIT;    REM — ——————————-   REM — Try to delete from parent table   REM — ——————————-    DELETE FROM dept WHERE deptno = 10;    ERROR at line 1:   ORA-02292: integrity constraint (OPS$ORACLE.EMP_FK1) violated – child record found    REM — ——————————————————–   REM — Set this transaction to defer all deferrable constraints   REM — ——————————————————–    SET CONSTRAINTS ALL DEFERRED;    DELETE FROM dept WHERE deptno = 10;    1 row deleted.      COMMIT;    ERROR at line 1:   ORA-02091: transaction rolled back   ORA-02292: integrity constraint (OPS$ORACLE.EMP_FK1) violated – child record found

 

 

REMOVING CONSTRAINTS

SQL> alter table employee drop unique (govt_id);

SQL> alter table employee drop primary key cascade;

SQL> alter table employee drop constraint ck_emp_01;

 

Creating a Virtual Column

You use the clause GENERATED ALWAYS AS when you specify a virtual column as part of a CREATE TABLE statement, as the following example illustrates:

 

SQL>  CREATE TABLE emp (  2   empno      NUMBER(5) PRIMARY KEY,  3   ename      VARCHAR2(15) NOT NULL,  4   ssn        NUMBER(9),  5   sal        NUMBER(7,2),  6*  hrly_rate  NUMBER(7,2) generated always as (sal/2080)); Table created.SQL>

The last line in the previous example shows the specification of the virtual column. If you want, you can also specify the optional keyword VIRTUAL, as shown here:

SQL> CREATE TABLE emp3  2  (sal number (7,2),  3  hrly_rate number (7,2) generated always as (sal/2080)  4  VIRTUAL  5* CONSTRAINT HourlyRate CHECK (hrly_rate > 8.00)); Table created

Limitations of Virtual Columns

Some limitations exist on the use of virtual columns in a table, which I summarize here:

• You can’t create virtual columns on an index-organized table, external table, temporary table, object, or a cluster.
• You can’t create a virtual column as a user-defined type, large object (LOB), or RAW.
• All columns in the column expression must belong to the same table.
• The column expression must result in a scalar value.
• The column expression in the AS clause can’t refer to another virtual column.
• You can’t update a virtual column by using it in the SET clause of an update statement.
• You can’t perform a delete or insert operation on a virtual column

Temporary Tables:-

Temporary tables are created in the user’s temporary tablespace and are assigned temporary segments only after the first INSERT statement is issued for the temporary table. They are deallocated after the completion of the transaction or the end of the session, depending on how the temporary tables were defined.

Here are some attractive features of temporary tables from the Oracle DBA’s point of view:

• Temporary tables drastically reduce the amount of redo activity generated by transactions. Redo logs don’t fill up as quickly if temporary tables are used extensively during complex transactions.
• Temporary tables can be indexed to improve performance.
• Sessions can update, insert, and delete data in temporary tables just as in normal permanent tables.
• The data is automatically removed from the temporary table after a session or a transaction.
• You can define table constraints on temporary tables.
• Different users can access the same temporary table, with each user seeing only his or her session data.
• Temporary tables provide efficient data access because complex queries need not be executed repeatedly.
• The minimal amount of locking of temporary tables means more efficient query processing.
• The structure of the table persists after the data is removed, so future use is facilitated.

 

Creating a Session Temporary Table

Here is an example of a temporary table that lasts for an entire session. You use the ON COMMIT DELETE ROWS option to ensure that the data remains in the table only for the duration of the session.

SQL> CREATE GLOBAL TEMPORARY TABLE flight_status(     destination VARCHAR2(30),     startdate DATE,     return_date DATE,     ticket_price NUMBER)     ON COMMIT PRESERVE ROWS;

The ON COMMIT PRESERVE ROWS option in the preceding example indicates that the table data is saved for the entire session, not just for the length of the transaction.

Creating a Transaction Temporary Table

Unlike session temporary tables, transaction temporary tables are specific to a single transaction. As soon as the transaction is committed or rolled back, the data is deleted from the temporary table. Here’s how you create a transaction temporary table:

SQL> CREATE GLOBAL TEMPORARY TABLE sales_info     (customer_name VARCHAR2(30),     transaction_no NUMBER,     transaction_date DATE)     ON COMMIT DELETE ROWS; The ON COMMIT DELETE ROWS option makes it clear that the data in this table should be retained only for the duration of the transaction that used this temporary table.