Oracle约束

主键约束&唯一键约束&外键约束

--主键约束
ALTER TABLE table_name
ADD CONSTRAINTS constraint_name PRIMARY KEY(column_name);

--删除约束
ALTER TABLE table_name
DROP CONSTRAINT constraint_name;

--外键约束
ADD CONSTRAINT constraint_name FOREIGN KEY(column_name)
REFERENCE table_name(column_name)
ON DELETE CASCADE;

CHECK约束

alter table tablename  
add constraint checkname check (condition);

UNIQUE约束

alter table tablename
  add constraint uniquename unique (columnname);

NOT NULL约束

ALTER TABLE table_name
MODIFY column NOT NULL;

默认值约束

alter table tablename modify columnname default 默认值;

索引
oracle索引，索引的建立、修改、删除 - 孤独的猫 - 博客园 (cnblogs.com)
创建索引CREATE [UNIQUE] INDEX index_name ON table_name(column_name[,column_name…])删除索引DROP INDEX  index_name; 修改索引

CREATE UNIQUE INDEX INDX_INSUR_UNIQ ON ZBWY.INSURANCE (CORP_NO,PNL_NO,STOP_YM);
--存在则删除索引
DECLARE
  COUNT_INDEXES INTEGER;
BEGIN
  SELECT COUNT(*) INTO COUNT_INDEXES
    FROM USER_INDEXES
    WHERE INDEX_NAME = 'INDX_INSUR_UNIQ';
 
  IF COUNT_INDEXES > 0 THEN
   EXECUTE  IMMEDIATE  'DROP INDEX  INDX_INSUR_UNIQ' ; 
  END IF;
END;