Search

Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Sunday, 13 May 2012

Alter Table Option in Oracle 9i

IMPORTANT DICTIONARY
DBA ALL USER
dba_cons_columns all_cons_columns user_cons_columns
dba_constraints all_constraints user_constraints
dba_indexes all_indexes user_indexes
dba_ind_partitions all_ind_partitions user_ind_partitions
dba_ind_subpartitions all_ind_subpartitions user_ind_subpartitions
******************************************************************************************************************************************************************
Constraint Types & Codes
Type Code Type Description Acts On Level
C Check on a table Column
O Read Only on a view Object
P Primary Key Object
R Referential and Foreign Key Column
U Unique Key Column
V Check Option on a view Object

****************************************************************************************************************************************************************** TABLE STRUCTURE
Rename existing table name.
ALTER TABLE  Old_Table RENAME TO New_Table;
To add multiple columns to an existing table.
ALTER TABLE table_name
ADD (column_1 column-definition,
column_2 column-definition,
 ...
column_n column_definition );
To modify a column in an existing table.
ALTER TABLE supplier
MODIFY supplier_name   varchar2(100)     not null;
To modify multiple columns in an existing table.
ALTER TABLE table_name
MODIFY (column_1 column_type,
  column_2 column_type,
  ...
  column_n column_type );
To drop a column in an existing table
ALTER TABLE supplier
DROP COLUMN supplier_name;
To rename a column in an existing table.
ALTER TABLE table_name
RENAME COLUMN old_name to new_name;
NOTE: Starting in Oracle 9i Release 2, you can now rename a column.
Add a default value to a column.
ALTER TABLE STAFF_OPTIONS
MODIFY SO_INSURANCE_PROVIDER Varchar2(35) DEFAULT 'ABC Ins';
******************************************************************************************************************************************************************
PRIMARY KEY CONSTRAINTS
Create Single Column Primary Key:
ALTER TABLE <table_name>
ADD CONSTRAINT <primary_key_constraint_name>
PRIMARY KEY (<column_name>)
USING INDEX
PCTFREE <percentage of block available for update>
INITRANS <integer>
MAXTRANS <integer>
STORAGE (FREELISTS <integer>)
TABLESPACE <tablespace_name>;
ALTER TABLE person
ADD CONSTRAINT pk_person
PRIMARY KEY (person_id)
USING INDEX
PCTFREE 0
TABLESPACE uwdata;
Create Composite Primary Key
ALTER TABLE person_uclass_ie
ADD CONSTRAINT pk_person_uclass_ie
PRIMARY KEY (person_id, class_id)
USING INDEX
PCTFREE 0;
Create Deferrable Primary Key
ALTER TABLE uclass
ADD CONSTRAINT pk_uclass
PRIMARY KEY (class_id)
INITIALLY DEFERRED DEFERRABLE
USING INDEX
PCTFREE 0;
NOTE: A deferrable constraint is checked AFTER an AFTER trigger fires.
Disabling and Enabling Primary Key Constraints
ALTER TABLE T1 DISABLE PRIMARY KEY;
ALTER TABLE T1 ENABLE PRIMARY KEY;
Disable Validate to make a table read only
ALTER TABLE SHAAN MODIFY CONSTRAINT SHAAN_PR ENABLE VALIDATE;
ALTER TABLE SHAAN MODIFY CONSTRAINT SHAAN_PR DISABLE VALIDATE;
Enable Primary Key without validation of pre-existing data
ALTER TABLE <table_name> ENABLE NOVALIDATE PRIMARY KEY;
ALTER TABLE <table_name> ENABLE VALIDATE PRIMARY KEY;
******************************************************************************************************************************************************************
UNIQUE CONSTRAINTS
Create unique constraint on a single column
ALTER TABLE <table_name>
ADD CONSTRAINT <constraint_name>
UNIQUE (<column_name>)
USING INDEX
PCTFREE <pct of block available for update>
TABLESPACE <tablespace_name>
ALTER TABLE state_zip
ADD CONSTRAINT uc_state_zip_state
UNIQUE (state);
Create unique constraint with USING INDEX clause
ALTER TABLE <table_name>
ADD CONSTRAINT <primary_key_constraint_name>
UNIQUE <column_name, column_name, ....>
USING INDEX
PCTFREE <pct of block available for update>
TABLESPACE <tablespace_name>;
******************************************************************************************************************************************************************
Referential OR Foreign Key
Create Foreign Key an a single column
ALTER TABLE <table_name>
ADD CONSTRAINT <constraint_name>
FOREIGN KEY (<column_name>)
REFERENCING <table_name> (<column_name>)
DEFERRABLE INITIALLY < IMMEDIATE | DEFERRED >;
Create Composite Foreign Key
ALTER TABLE <table_name>
ADD CONSTRAINT <constraint_name>
FOREIGN KEY (<column_name, column_name, ....>)
REFERENCING <table_name> (<column_name,column_name,....>);
Create deferrable foreign key
ALTER TABLE <table_name>
ADD CONSTRAINT <constraint_name>
FOREIGN KEY (<column_name, column_name, ....>)
REFERENCING <table_name> (<column_name,column_name,....>)
INITIALLY DEFERRED DEFERRABLE;
******************************************************************************************************************************************************************
SET CONSTRAINTS
SET CONSTRAINTS ALL IMMEDIATE;
SET CONSTRAINTS ALL DEFERRED;
******************************************************************************************************************************************************************
ENABLE/DISABLE CONSTRAINTS OPTIONS
Disable constraint
ALTER TABLE <table_name>
DISABLE CONSTRAINT <constraint_name>;
Enable constraint with EXCEPTIONS INTO clause
ALTER TABLE <table_name>
ENABLE CONSTRAINT <constraint_name>;
@C:\oracle1\rdbms\admin\utlexcpt.sql
desc exceptions
ALTER TABLE person
ENABLE PRIMARY KEY
EXCEPTIONS INTO exceptions;
SELECT * FROM exceptions;
On Delete Cascade
ALTER TABLE <table_name>
ADD CONSTRAINT <constraint_name>
FOREIGN KEY (<column_name>)
REFERENCES <table_name> (<column_name>)
ON DELETE CASCADE;
On Delete Set NULL
ALTER TABLE <table_name>
ADD CONSTRAINT <constraint_name>
FOREIGN KEY (<column_name>)
REFERENCES <table_name> (<column_name>)
ON DELETE SET NULL;
Using a virtual column
CREATE TABLE virtual (
zip_code   VARCHAR2(10),
zip_unique VARCHAR2(5) AS (SUBSTR(zip_code,1,5))
REFERENCES postal_code(zip_code));
******************************************************************************************************************************************************************
CHECK CONSTRAINT
Create Check Constraint with EQUALS
ALTER TABLE <table_name>
ADD CONSTRAINT <constraint_name> CHECK (<column_name> = <condition>);
ALTER TABLE person
ADD CONSTRAINT cc_person_status CHECK (status = 'X');
Create Check Constraint With NOT EQUALS
ALTER TABLE
ADD CONSTRAINT <constraint_name> CHECK (<column_name> != <value>);
Create Check Constraint with LIKE
ALTER TABLE <table_name>
ADD CONSTRAINT <constraint_name> CHECK (<column_name> LIKE <condition>);
Create Check Constraint with NOT LIKE
ALTER TABLE uclass
ADD CONSTRAINT cc_uclass_class_name CHECK (class_name NOT LIKE '%O%');
Create Check Constraint with IN
ALTER TABLE person
ADD CONSTRAINT cc_person_status CHECK (status IN ('N', 'Y'));
Create Check Constraint with NOT IN
ALTER TABLE person
ADD CONSTRAINT cc_person_status CHECK (status NOT IN ('A','B','C','D'));
Create Check Constraint with BETWEEN
ALTER TABLE person
ADD CONSTRAINT cc_person_age
CHECK (per_age BETWEEN 18 AND 60);
Create Check Constraint with NOT BETWEEN
ALTER TABLE person
ADD CONSTRAINT cc_person_age CHECK (per_age NOT BETWEEN 18 AND 60);
Create Check Constraint with Boolean Operator
ALTER TABLE person
ADD CONSTRAINT cc_person_age CHECK (per_age > 30);
Functions Can Be Used In A Check Constraint But Not To Make Assignments
ALTER TABLE uclass
ADD CONSTRAINT cc_uclass_reg_fee CHECK (reg_fee =  ROUND(reg_fee,0));
******************************************************************************************************************************************************************
DROP CONSTRAINT OPTION
ALTER TABLE <table_name>
DROP CONSTRAINT <primary_key_constraint_name>;
Specific Primary Key Drop
ALTER TABLE <table_name> DROP PRIMARY KEY
Specific unique constraint Drop
ALTER TABLE state_zip DROP UNIQUE (state);
Drop foreign key
ALTER TABLE <table_name>
DROP CONSTRAINT <constraint_name>;
Drop Primary Key with a Foreign Key dependency
ALTER TABLE state_zip DROP PRIMARY KEY CASCADE;
Table Drop with Foreign Key Constraint
DROP TABLE <table_name> CASCADE CONSTRAINTS [PURGE];
******************************************************************************************************************************************************************
RENAME CONSTRAINT
ALTER TABLE <table_name>
RENAME CONSTRAINT <current_constraint_name>
TO <new_constraint_name>;
******************************************************************************************************************************************************************
Constraint Related Queries
List a child table's referential constraints and their associated parent table.
SELECT t.owner CHILD_OWNER, t.table_name CHILD_TABLE,
t.constraint_name FOREIGN_KEY_NAME, r.owner PARENT_OWNER,
r.table_name PARENT_TABLE, r.constraint_name PARENT_CONSTRAINT
FROM user_constraints t, user_constraints r
WHERE t.r_constraint_name = r.constraint_name
AND t.r_owner = r.owner AND t.constraint_type='R'
AND t.table_name = 'DEPARTMENTS';
List foreign keys and referenced table and columns OF A SCHEMA
SELECT DECODE(c.status,'ENABLED','C','c') t,
SUBSTR(c.constraint_name,1,31) relation,
SUBSTR(cc.column_name,1,24) columnname,
SUBSTR(p.table_name,1,20) tablename
FROM all_cons_columns cc, all_constraints p, all_constraints c
WHERE c.owner = upper('HR')
AND c.table_name = upper('EMPLOYEES') AND c.constraint_type = 'R'
AND p.owner = c.r_owner AND p.constraint_name = c.r_constraint_name
AND cc.owner = c.owner AND cc.constraint_name = c.constraint_name
AND cc.table_name = c.table_name
UNION ALL
SELECT DECODE(c.status,'ENABLED','P','p') t, SUBSTR(c.constraint_name,1,31) relation,
SUBSTR(cc.column_name,1,24) columnname, SUBSTR(c.table_name,1,20) tablename
FROM all_cons_columns cc, all_constraints p, all_constraints c
WHERE p.owner = upper('HR') AND p.table_name = upper('EMPLOYEES')
AND p.constraint_type in ('P','U') AND c.r_owner = p.owner
AND c.r_constraint_name = p.constraint_name AND c.constraint_type = 'R'
AND cc.owner = c.owner AND cc.constraint_name = c.constraint_name
AND cc.table_name = c.table_name
ORDER BY 1, 4, 2, 3;
******************************************************************************************************************************************************************
Procedure to DISABLE all  constraints
CREATE OR REPLACE PROCEDURE disable_fk_constraint IS
CURSOR fke_cur IS
SELECT table_name, constraint_name
FROM user_constraints
WHERE constraint_type = 'R' AND status = 'ENABLED';
ExStr VARCHAR2(4000);
BEGIN
FOR fke_rec IN fke_cur LOOP
ExStr := 'ALTER TABLE ' || fke_rec.table_name ||
             'DISABLE CONSTRAINT ' ||
              fke_rec.constraint_name;
    BEGIN
      EXECUTE IMMEDIATE ExStr;
    EXCEPTION
      WHEN OTHERS THEN   NULL;
    END;
  END LOOP;
END disable_fk_constraint;

0 comments:

Post a Comment