Robert 的部落格

ALTER TABLE

• ADD

  格式

  ALTER TABLE [schema.]table_name ADD
      (column datatype [DEFAULT value] [constraint]
      [,column datatype [DEFAULT value] [constraint]...]);
  
  schema :結構名稱
  table_name : 表格名稱
  column : 欄位名稱
  datatype : 資料型別
  value : 欄位預設值
  constraint : 條件約束
   

  ALTER TABLE customers
    ADD customer_name varchar2(45) ;

  ALTER TABLE customers
    ADD city varchar2(40) DEFAULT 'Seattle' ;

  ALTER TABLE customers
    ADD (customer_name varchar2(45),
         city varchar2(40) DEFAULT 'Seattle') ;

   
• MODIFY

  ALTER TABLE customers
    MODIFY customer_name varchar2(100) NOT NULL ;

  ALTER TABLE customers
    MODIFY city varchar2(75) DEFAULT 'Seattle' NOT NULL ;

  ALTER TABLE customers
    MODIFY (customer_name varchar2(100) NOT NULL,
            city varchar2(75) DEFAULT 'Seattle' NOT NULL) ;

   
• DROP

  ALTER TABLE customers
    DROP COLUMN customer_name ;

   
• ADD PRIMARY KEY

  ALTER TABLE test2
  ADD CONSTRAINT test_id_pk
  PRIMARY KEY (test_id) ;

   

參考：
● Oracle / PLSQL: ALTER TABLE Statement
● Oracle SQL學習筆記本: ALTER TABLE...ADD介紹
● ORA-01735: invalid ALTER TABLE option | PL/SQL Tutorial Oracle for beginners