ORACLE ALTER TABLE，oraclealtertable

ORACLE ALTER TABLE，oraclealtertable

create table T_CLASS_INFO(
 CLASSNO number(3) primary key, --班級序號
 CLASSNAME varchar2(10),
 CLASSPLACE varchar2(13)
);
create table T_STUDENT_INFO(
 STUNO number(3),--學號
 CLASSNO number(3),
 STUNAME varchar2(10),
 STUSEX char(1),
 STUCONTECT number(11), --連絡方式
 STUADD varchar2(50),
 STUDETAILINFO varchar2(100)
);

--修改列名
ALTER table T_CLASS_INFO RENAME column CLASSPLACE to LOCATION;
--添加主鍵約束
ALTER table T_STUDENT_INFO ADD constraint PK_STU_NO primary key(STUNO);
--添加外鍵約束
ALTER table T_STUDENT_INFO ADD constraint FK_CLASSNO foreign key(CLASSNO) references T_CLASS_INFO(CLASSNO);
--添加check約束
ALTER table T_STUDENT_INFO ADD constraint CK_STU_INFO check (STUSEX in ('F','M'));
--添加not null約束
ALTER table T_STUDENT_INFO MODIFY STUCONTECT constraint NOT_NULL_INFO not null;
--添加unique約束
ALTER table T_STUDENT_INFO ADD constraint UQ_STU_CONTECT unique(STUCONTECT);
--添加default約束
ALTER table T_STUDENT_INFO MODIFY STUSEX char(2) default'M';

--增加列
ALTER table T_STUDENT_INFO ADD STUID varchar2(18);
ALTER table T_STUDENT_INFO ADD STUAGE DATE default sysdate not null;
--刪除列
ALTER table T_STUDENT_INFO DROP column STUDETAILINFO;

--修改列的長度
ALTER table T_CLASS_INFO MODIFY CLASSPLACE varchar2(50);
--修改列的精度
ALTER table T_STUDENT_INFO MODIFY STUNO number(2);
--修改列的資料類型
ALTER table T_STUDENT_INFO MODIFY STUSEX char(2);
--修改列的預設值
ALTER table T_STUDENT_INFO MODIFY STUAGE default sysdate+1;

--禁用約束
ALTER table T_STUDENT_INFO disable FK_CLASSNO;
--啟用約束
ALTER table T_STUDENT_INFO enable FK_CLASSNO;
--刪除約束
ALTER table T_STUDENT_INFO DROP FK_CLASSNO;
--延遲約束
ALTER table T_STUDENT_INFO ADD constraint FK_CLASSNO foreign key(CLASSNO)
references T_CLASS_INFO(CLASSNO)
deferrable initially deferred;

--向表中添加註釋
comment on table T_STUDENT_INFO is '學生表';
--向列添加註釋
comment on column T_STUDENT_INFO.STUNAME is '學生姓名';
comment on column T_CLASS_INFO.CLASSNAME is '班級';
--清除表中所有資料
truncate table T_STUDENT_INFO;
--刪除表
DROP table T_STUDENT_INFO;