1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Drop constrained columns

Discussion in 'SQL PL/SQL' started by Gunay, Feb 17, 2013.

  1. Gunay

    Gunay Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    Baku Azerbaijan
    Hi all,
    Please assist in clarifing one point. Oracle SQL Expert Exam guidelines says that columns which are part of the PRIMARY KEY constraint dropped unless the constraint is first dropped. But Alter table drop column statement succesfully executes with constrainted columns before dropping constraint.
    How it is possible ?
    Thanks in advance
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Apparently you're using a newer release of Oracle. The exam you reference is relying on older information which does not apply to current Oracle releases. In 11.2.0.3 we see:

    Code (SQL):
    SQL> CREATE TABLE yapnor(
      2          fning   NUMBER,
      3          ertop   varchar2(40),
      4          zezquat DATE,
      5          CONSTRAINT yapnor_pk
      6          PRIMARY KEY (fning)
      7  );
     
    TABLE created.
     
    SQL>
    SQL> SELECT co.constraint_name, c.constraint_type, co.column_name
      2  FROM user_cons_columns co JOIN user_constraints c
      3  ON (co.TABLE_NAME = c.TABLE_NAME AND co.constraint_name = c.constraint_name);
     
    CONSTRAINT_NAME                C
    ------------------------------ -
    COLUMN_NAME
    --------------------------------------------------------------------------------
    YAPNOR_PK                      P
    FNING

    SQL>
    SQL> ALTER TABLE yapnor DROP COLUMN fning;
     
    TABLE altered.
     
    SQL>
    SQL> SELECT co.constraint_name, c.constraint_type, co.column_name
      2  FROM user_cons_columns co JOIN user_constraints c
      3  ON (co.TABLE_NAME = c.TABLE_NAME AND co.constraint_name = c.constraint_name);
     
    no ROWS selected
     
    SQL>
    Notice that the ALTER TABLE statement now includes dropping the constraint before dropping the requeted column. Had Oracle not done this the column drop would throw an error.

    This is why such texts and exam preps should be regularly reviewed -- behaviour changes with new releases and what was once true may now no longer be.
     
    Gunay likes this.
  3. Gunay

    Gunay Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    Baku Azerbaijan
    Oracle release installed in my comp is 11.2.0.1.0 Actually there were some confusing points such type. I didnt get that this was connected with the Oracle release.Thank you very much