в 13:45, 26 мая 2010

2010-05-26T13:45:19Z

Admin: 1 версия

2010-05-26T10:01:56Z

1 версия

Новая страница

==Disable foreign key==



<source lang="sql">

SQL>
SQL> create table it
2 (c1 number primary key,
3 c2 number,
4 c3 number);

SQL>
SQL> alter table it add constraint it_fk foreign key (c2) references emp disable;

SQL>
SQL>
SQL> select object_name as name, object_id
2 from user_objects
3 where object_name in ("T", "IT");
IT 14971
1 row selected.

</source>

==Drop the foreign key==



<source lang="sql">

SQL> CREATE TABLE supplier
2 ( supplier_id numeric(10) not null,
3 supplier_name varchar2(50) not null,
4 contact_name varchar2(50),
5 CONSTRAINT supplier_pk PRIMARY KEY (supplier_id)
6 );
Table created.
SQL>
SQL>
SQL> CREATE TABLE products
2 ( product_id numeric(10) not null,
3 supplier_id numeric(10) not null,
4 CONSTRAINT fk_supplier
5 FOREIGN KEY (supplier_id)
6 REFERENCES supplier(supplier_id)
7 );
Table created.
SQL>
SQL>
SQL> -- If we then wanted to drop the foreign key called fk_supplier, we could execute the following command:
SQL>
SQL> ALTER TABLE products
2 drop CONSTRAINT fk_supplier;
Table altered.
SQL>
SQL> drop table supplier cascade constraint;
Table dropped.
SQL> drop table products cascade constraint;
Table dropped.
SQL>

</source>

==Foreign key with cascade delete==



<source lang="sql">

SQL> -- foreign key with cascade delete
SQL>
SQL> CREATE TABLE supplier
2 ( supplier_id numeric(10) not null,
3 supplier_name varchar2(50) not null,
4 contact_name varchar2(50),
5 CONSTRAINT supplier_pk PRIMARY KEY (supplier_id)
6 );
Table created.
SQL>
SQL>
SQL> CREATE TABLE products
2 ( product_id numeric(10) not null,
3 supplier_id numeric(10) not null,
4 CONSTRAINT fk_supplier
5 FOREIGN KEY (supplier_id)
6 REFERENCES supplier(supplier_id)
7 ON DELETE CASCADE
8 );
Table created.
SQL>
SQL> desc products;
Name Null? Type
----------------------------------------------------------------------------------------------------- -------- --------------------------------------------------------------------
PRODUCT_ID NOT NULL NUMBER(10)
SUPPLIER_ID NOT NULL NUMBER(10)
SQL> desc supplier;
Name Null? Type
----------------------------------------------------------------------------------------------------- -------- --------------------------------------------------------------------
SUPPLIER_ID NOT NULL NUMBER(10)
SUPPLIER_NAME NOT NULL VARCHAR2(50)
CONTACT_NAME VARCHAR2(50)
SQL>
SQL> drop table products cascade constraints;
Table dropped.
SQL>
SQL> drop table supplier cascade constraints;
Table dropped.
SQL>
SQL>

</source>

← Предыдущая	Версия 13:45, 26 мая 2010
(нет различий)

Oracle PL/SQL/Constraints/Drop Foreign Key - История изменений

в 13:45, 26 мая 2010

Admin: 1 версия