DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Simple Newbie Questions about SQL and Oracle 10g

  1. #1
    Join Date
    Jul 2009
    Posts
    9

    Simple Newbie Questions about SQL and Oracle 10g

    First of all, i'm pleased to be a member of your forum. I got some newbie questions, i will be glad to hear back from you. Excuse me if you see any writing mistakes.

    create table test1 (
    pk NUMBER PRIMARY KEY,
    fk NUMBER,
    col1 NUMBER,
    col2 NUMBER,
    CONSTRAINT fk_constraint FOREIGN KEY (fk) REFERENCES test1,
    CONSTRAINT ck1 CHECK (pk > 0 and col1 > 0),
    CONSTRAINT ck2 CHECK (col2 > 0) );

    An error is returned for the following statements:

    ALTER TABLE test1 DROP (pk); -- pk is a parent key.

    We can not drop it because we did not mention ON DELETE CASCADE. Am i right?

    ALTER TABLE test1 DROP (col1) -- col1 is referenced by multicolumn constraint ck1.

    I do not get it, can you explain please. col1 is not referenced, i see CHECK constraint is applied but no references made. Secondly, is ck1 considered multicolumn because it check two columns? Or multicolumn here represents something else?

  2. #2
    Join Date
    Jul 2009
    Posts
    9
    Questions are bolded

  3. #3
    Join Date
    Jul 2009
    Posts
    9
    I just would like to add 2 questions relating to the thread:

    ALTER TABLE emp2
    DROP COLUMN employee_id CASCADE CONSTRAINTS;

    This drop employee_id column and all its child. Correct?

    ALTER TABLE test1
    DROP (pk, fk, col1) CASCADE CONSTRAINTS;

    This drops three columns and all its child if there are any. Correct?


    Then what's the difference between ON DELETE CASCADE and CASCADE CONSTRAINTS?
    For example, What if employee_id in emp2 table definition does not have ON DELETE CASCADE, will CASCADE CONSTRAINTS work? Please explain...

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    alter mytable drop column mycolumn cascade constraints; would 1) drop fks pointing to mytable.mycolumn then 2) drop mytable.mycolumn column. That's it.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  5. #5
    Join Date
    Jul 2009
    Posts
    9
    Alright, so CASCADE CONSTRAINTS is defined in the parent side.
    ON DELETE CASCADE is defined in the foreign key side.

    Thanks

Tags for this Thread

Posting Permissions

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


Click Here to Expand Forum to Full Width