PL/SQL Working with Columns

Drop & Modify Constraints

alter table table_name drop constraint PK_XXX_XXX_ID;
alter table table_name modify constraint FK_XXX_XXX_XXX_ID enable;
alter table table_name modify constraint FK_XXX_XXX_YYY_ID enable;
alter table table_name modify constraint FK_XXX_XXX_ZZZ_ID enable;
--validation disabled
alter table table_name modify constraint CHK_XXX_XXX_TTT_NAME enable novalidate;

Add Constraints

ALTER TABLE table_name
add CONSTRAINT FK_XXX_XXX_XXX_ID
   FOREIGN KEY (column_name)
   REFERENCES reference_table (ID);

Rename Table

alter table table_name
rename to new_table_name;


Rename Columns

alter table table_name rename column column_name_x to new_column_name_x;
alter table table_name rename column column_name_y to new_column_name_y;
alter table table_name rename column column_name_z to new_column_name_z;

Add Columns

alter table
   table_name
add
   (
    USER_ID NUMBER(18,0) DEFAULT 1 NOT NULL ENABLE,
    ENTRY_DATE DATE DEFAULT sysdate NOT NULL ENABLE,
    UPDATE_USER_ID NUMBER(18,0),
    UPDATE_DATE DATE
   );

Drop Columns

alter table table_name drop column column_name;

Modify Columns

alter table table_name modify
(
   PHONE     VARCHAR2(20 BYTE),
   MOBILE_PHONE  VARCHAR2(20 BYTE),
   E_MAIL      VARCHAR2(40 BYTE),
   CITY VARCHAR2(30 BYTE),
   START_DATE DATE NULL
);

Modify Columns to Not Null

alter table  table_name
modify
(
   column_name               VARCHAR2(25 BYTE) default null null
);

Add Comment to Columns
comment on column table_name.column_name is '1-One, 2-Two, 3-Three, 4-Four';


Create Table with standart Columns
CREATE TABLE XYZ_KULLANIM_TURLERI
(
    ID                 NUMBER(18,0) NOT NULL ENABLE,
    ADI                VARCHAR2(50 BYTE) NOT NULL ENABLE,
    CTV_ID             NUMBER(18,0),
    KAYIT_KULLANICI_ID NUMBER(18,0) NOT NULL ENABLE,
    KAYIT_TARIHI DATE DEFAULT sysdate NOT NULL ENABLE,
    GUNCELLEME_KULLANICI_ID NUMBER(18,0),
    GUNCELLEME_TARIHI DATE,
    CONSTRAINT PK_XYZ_KULLANIM_TURLERI_ID PRIMARY KEY (ID),
    CONSTRAINT FK_XYZ_KULL_TUR_GNC_KULL_ID FOREIGN KEY (GUNCELLEME_KULLANICI_ID) REFERENCES XYZ_KULLANICI_BILGILERI (ID) ENABLE,
    CONSTRAINT FK_XYZ_KULL_TUR_KYT_KULL_ID FOREIGN KEY (KAYIT_KULLANICI_ID) REFERENCES XYZ_KULLANICI_BILGILERI (ID) ENABLE
);