------------------ ALTER TABLE

-- alter the default storage for table
ALTER TABLE ADMIN."TEMP_MASTER1" STORAGE ( NEXT 2M PCTINCREASE 20);

------------------ ALTER TABLE ADD

--
-- add a column
ALTER TABLE ADMIN."DB_BILL" ADD(CONSUMPTION_LARGE_ON_BILL  NUMBER NULL);
ALTER TABLE ADMIN."DB_METER" ADD(METER_LOCATION VARCHAR2(40) NULL);

-- add a column with a default and a constraint
ALTER TABLE ADMIN."DB_ROUTE" 
ADD(supply_code CHAR(1) DEFAULT '1' NOT NULL,  
	CONSTRAINT route_supply_code 
	CHECK ( supply_code in ('1','2') )  
   );

ALTER TABLE PAYMENT ADD (AR_sent CHAR(1) DEFAULT 'N' NULL CONSTRAINT YorN6 CHECK (AR_sent in ('Y','N')));

alter table DB_connection add (CONNECTION_SEQNBR NUMBER(2,0) DEFAULT 1 NOT NULL);

--
-- Adding a CHECK constraint to set a column to NOT NULL
ALTER TABLE DB_ROUTE ADD( CHECK (default_reading_day_number is NOT NULL));

ALTER TABLE ADMIN."DB_COSTED_OR_FLAT_RATE_CONN" 
	ADD( CONSTRAINT CONNECTION$COST_FLAT_RATE FOREIGN KEY (CONNECTION_ID) 
	REFERENCES ADMIN."DB_CONNECTION"(CONNECTION_ID));
	
--	
-- Adding a CHECK constraint to set a column to NOT NULL and don't validate current data
alter table tempdbstar add col2 char CONSTRAINT check_notnull2 CHECK (col2 is not null) DISABLE;
alter table tempdbstar enable novalidate constraint check_notnull2; 

-- add check constraint to validate a date
alter table client_address
add constraint check_effective_date
 check(to_char(effective_date,'HH24:MI:SS') = '00:00:00')
 enable novalidate
/
alter table client_address
add constraint check_inactive_date
 check(inactive_date is null or (to_char(inactive_date,'HH24:MI:SS') = '23:59:59' and inactive_date > effective_date))
 enable novalidate
/

--
-- Adding a FK to a table
alter table worker 
   add constraint fk_office$worker foreign key (office_id) references office (office_id);
-- Adding a FK to a table and set as disabled
ALTER TABLE Attendance
       ADD  ( CONSTRAINT FK_REQ_ITEM$ATTENDANCE
              FOREIGN KEY (Req_id, Request_id, Program_id,
              Requested_date)
                             REFERENCES DBSTAR_REQUESTED_ITEM DISABLE)


-- Adding a PK to a table
alter table table_name
   add constraint xpk_constraint_name primary key (column_name)
	USING INDEX 
	PCTFREE 10
	INITRANS 2
	MAXTRANS 255
	TABLESPACE tablespace_name
	STORAGE (
		INITIAL  20K
		NEXT  20K
		MINEXTENTS 1
		MAXEXTENTS 2147483645
		PCTINCREASE 50)
unrecoverable
;

alter table DBSTAR_dec99 
   add constraint xpk_DBSTAR_dec99  primary key (Cims_client_id);


------------------ ALTER TABLE MODIFY
-- modify column data type and add default
alter table address_type modify sort_order number(5);
alter table DB_PREAUTH_CUSTOMER_RECORD 
   modify (due_day number(2,0) default 0);

-- modify length of a char column
alter table DB_meter_size 
   modify (METER_SIZE_PRINT char(3));

-- modify column to make not null
alter table DB_REGIONAL_CUSTOMER_RECORD 
   modify(TRANSACTION_TYPE_ID NUMBER NOT NULL);

-- modify column to make it null
alter table social_criteria modify (inactive_date null);

-- modify size of data type
alter table address_type modify sort_order number(5);

-- disable a pk of a table
alter table DB_meter_size disable constraint meter_size_pk cascade;

alter table DB_transaction disable primary key cascade;

ALTER TABLE ADMIN."DB_BILL" DISABLE CONSTRAINT BILL_U1 cascade;

ALTER TABLE ADMIN."DB_METER" ADD(METER_LOCATION VARCHAR2(40) NULL);


-- drop a pk from a table
alter table DB_connection drop primary key;


-- Enable a pk of a table
alter table item enable 
CONSTRAINT XPK_ITEM 
	USING INDEX 
	PCTFREE 10
	INITRANS 2
	MAXTRANS 255
	TABLESPACE DBSTAR_IX
	STORAGE (	INITIAL  20K
	NEXT  20K
	MINEXTENTS 1
	MAXEXTENTS 2147483645
	PCTINCREASE 50
	        )
/



-- ALTER TABLE DISABLE CONSTRAINT
alter table owner.table_name disable constraint constraint_name; 


-- ALTER TABLE ENABLE CONSTRAINT
alter table owner.table_name enable constraint constraint_name;