Results 1 to 3 of 3

Thread: constraints

  1. #1
    Join Date
    May 2008
    Posts
    1

    constraints

    hey frens !
    i have a command ....
    ALTER TABLE
    DISABLE CONSTRAINT constraint CASCADE;


    now if i have two table with foreign key relationship den plzz tell me which constraint to disable and when n why to use "cascade".

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    plzz stop da shorthands

    i have the answers, let me show you them

    which constraint to disable? i don't know... which ones have you got?

    when to disable them? when you don't want them any more ever

    why to use cascade? if you only want to disable them temporarily


    which database system are you using?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    The CASCADE option allows you to drop a primary key or unique constraint that is referenced by foreign key constraints: the referencing foreign key constraints are also dropped.

    Example (Oracle):
    Code:
    SQL> create table a (aid integer, constraint a_pk primary key (aid));
    
    Table created.
    
    SQL> create table b (bid integer, aid integer,
      2  constraint b_pk primary key (bid),
      3  constraint b_fk foreign key(aid) references a);
    
    Table created.
    
    SQL> select constraint_name from user_constraints where table_name='B';
    
    CONSTRAINT_NAME
    ------------------------------
    B_FK
    B_PK
    
    SQL> insert into a values (1);
    
    1 row created.
    
    SQL> insert into b values (11,1);
    
    1 row created.
    
    SQL> alter table a drop constraint a_pk;
    alter table a drop constraint a_pk
                                  *
    ERROR at line 1:
    ORA-02273: this unique/primary key is referenced by some foreign keys
    
    
    SQL> alter table a drop constraint a_pk CASCADE;
    
    Table altered.
    
    SQL> select constraint_name from user_constraints where table_name='B';
    
    CONSTRAINT_NAME
    ------------------------------
    B_PK
    As you can see, dropping the primary key of A with cascade caused the foreign key referencing A from B to be dropped also.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •