oracle级联删除

最新推荐文章于 2024-05-05 03:02:17 发布

原创最新推荐文章于 2024-05-05 03:02:17 发布 · 97 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#Oracle

Oracle SQL 专栏收录该内容

68 篇文章

订阅专栏

建立Foreign   key   constraint的之后指定on   delete   cascade 

alter   table   <table_name>   add   constraint   <constraint_name>   foreign   key   on   ( <columns> )   references   <parent_table> ( <columns> )   
on   delete   cascade

CREATE TABLE "CBILL"."CT_CUSELLRATEDETAIL"
  (
    "NUMCUSELLRATEDMSGUID" NUMBER(10,0) NOT NULL ENABLE,
    "NUMCUSELLRATEGUID"    NUMBER(10,0),
    "VC2SELLDMSNAME"       VARCHAR2(100 BYTE),
    "NUMVALIDFLAG"         NUMBER(1,0),
    "DATBEGINDATE" DATE,
    "NUMCURATE"     NUMBER(10,5),
    "NUMCUSELLRATE" NUMBER(10,5),
    "VC2DESC"       VARCHAR2(100 BYTE),
    "VC2REMARK"     VARCHAR2(100 BYTE),
    "DATCREATEDATE" DATE,
    "NUMCREATECODE" NUMBER(10,0),
    "DATOPERDATE" DATE,
    "NUMOPERCODE" NUMBER(10,0),
    CONSTRAINT "PK_CT_CUSELLRATEDETAIL" PRIMARY KEY ("NUMCUSELLRATEDMSGUID") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "CTBS_STAT" ENABLE,
    CONSTRAINT "FK_CT_CUSEL_REFERENCE_CT_CUSEL" FOREIGN KEY ("NUMCUSELLRATEGUID") REFERENCES "CBILL"."CT_CUSELLRATEINFO" ("NUMCUSELLRATEGUID") ON
  DELETE CASCADE ENABLE
  )
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE
  (
    INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT
  )
  TABLESPACE "CTBS_STAT" ;

ALTER TABLE CT_CUSELLRATEDETAIL 
DROP CONSTRAINT FK_CT_CUSEL_REFERENCE_CT_CUSEL;

ALTER TABLE CT_CUSELLRATEDETAIL
ADD CONSTRAINT FK_CT_CUSEL_REFERENCE_CT_CUSEL FOREIGN KEY
(
  NUMCUSELLRATEDMSGUID 
)
REFERENCES CT_CUSELLRATEINFO
(
  NUMCUSELLRATEGUID 
)
ON DELETE CASCADE ENABLE;