如何建立一个主从表,当主表删除的时候,从表的记录一期删除
CREATE TABLE STORES (
STORE_ID NUMBER(10) NOT NULL,
SUPPLIER_NAME VARCHAR2(40) NOT NULL,
CONSTRAINT STORES_PK PRIMARY KEY (STORE_ID));
CREATE TABLE PRODUCTS (
PRODUCT_ID NUMBER(10) NOT NULL,
PRODUCT_NAME VARCHAR2(30) NOT NULL,
SUPPLIER_ID NUMBER(10) NOT NULL,
STORE_ID NUMBER(10) NOT NULL,
CONSTRAINT FK_STORES FOREIGN KEY (STORE_ID) REFERENCES STORES(STORE_ID) ON DELETE CASCADE);
删除stores的记录,products也删除.
=================================
-- 创建测试主表. ID 是主键. CREATE TABLE test_main ( id INT, value VARCHAR(10), PRIMARY KEY(id) ); -- 创建测试子表. CREATE TABLE test_sub ( id INT, main_id INT, value VARCHAR(10), PRIMARY KEY(id) ); -- 创建外键(使用 ON DELETE CASCADE 选项,删除主表的时候,同时删除子表) ALTER TABLE test_sub ADD CONSTRAINT main_id_cons FOREIGN KEY (main_id) REFERENCES test_main ON DELETE CASCADE;
========================================================