SQL Alter Table DROP

Changing Table Definitions with drop

  • Most RDBMS systems allow you to change the structure of a table with the ALTER TABLE command.
  • A DROP statement on a table means to remove columns, constraints etc.
  • To perform a DROP statement can lead to serious consequences since the result is absolute and can not be recovered.
  • It is therefore important to think through the consequences before making such an action.
  • Syntax for ALTER and DROP can varies from RDBMS to RDBMS, so check your system manuals for specifics.
  • Here are some of the most important Drop statement in SQL.
  1. Drop a column from a table:

    MySQL, Oracle, SQL server, PostgreSQL:
    ALTER TABLE table_name DROP COLUMN column_name ;
    Example:
    ALTER TABLE CUSTOMER DROP COLUMN TELEPHONE_NUMBER;
  2. Remove the default value for a column:

    MySQL, SQL server, PostgreSQL:
    ALTER TABLE table_name ALTER column_name DROP DEFAULT ;
    Example:
    ALTER TABLE TELEPHONES ALTER TELEPHONE_NUMBER DROP DEFAULT ;
    Oracle:
    ALTER TABLE table_name MODIFY column_name DEFAULT NULL ;
    Example:
    ALTER TABLE TELEPHONES MODIFY TELEPHONE_NUMBER DROP DEFAULT NULL ;
  3. Drop a primary key for a table:

    MySQL, Oracle:
    ALTER TABLE table_name DROP PRIMARY KEY ;
    Example:
    ALTER TABLE CUSTOMER DROP PRIMARY KEY ;
    Alternative for Oracle:
    ALTER TABLE table_name DROP CONSTRAINT constraint_name;

    If you don't know the constraint_name you need to find it by:

     SELECT constraint_name
     FROM all_constraints 
     where constraint_type = 'P' AND table_name='table_name';
    Example:
     SELECT constraint_name
     FROM all_constraints 
     where constraint_type = 'P' AND table_name='CUSTOMER';
    /* If this gives the constraint name SYS_C007322: */
    ALTER TABLE CUSTOMER DROP CONSTRAINT SYS_C007322;
    PostgrSQL:
    ALTER TABLE table_name DROP CONSTRAINT table_name_pkey;
    Example:
    ALTER TABLE CUSTOMER DROP CONSTRAINT CUSTOMER_pkey;
    SQL server:
    ALTER TABLE table_name DROP CONSTRAINT constraint_name;

    If you don't know the constraint_name you need to find it by:

    SELECT name
    FROM   sys.key_constraints
    WHERE  [type] = 'PK'
           AND [parent_object_id] = Object_id('table_name');
    Example:
    SELECT name
    FROM sys.key_constraints
    WHERE  [type] = 'PK'
           AND [parent_object_id] = Object_id('CUSTOMER');
    /* If this gives the constraint name  PK__customer__1CE12D37FD81EC4A then: */
    ALTER TABLE CUSTOMER DROP CONSTRAINT PK__customer__1CE12D37FD81EC4A;
  4. Drop a foreign key constraint in a table:

    MySQL:
    ALTER TABLE table_name
     DROP FOREIGN KEY constraint_name;
    Example:
    ALTER TABLE TELEPHONES
     DROP FOREIGN KEY FKA_CUSTOMER;
    Oracle, SQL server, PostgreSQL:
    ALTER TABLE table_name
     DROP CONSTRAINT constraint_name;
    Example:
    ALTER TABLE TELEPHONES
     DROP CONSTRAINT FKA_CUSTOMER;
  5. Drop unique index in a table:

    MySQL:
    ALTER TABLE table_name
      DROP INDEX constraint_name;
    Example:
    ALTER TABLE BOOKSTORE
      DROP INDEX SHORTDESC_IND;  
    Oracle, SQL server, PostgreSQL:
    ALTER TABLE table_name
     DROP CONSTRAINT constraint_name;
    Example:
    ALTER TABLE TELEPHONES
     DROP CONSTRAINT FKA_CUSTOMER;
  6. Drop check constraints in a table:

    MySQL:
    MySQL reads, but does not register any check statement, so there will not be any index or constraint for this.
    Oracle, SQL server, PostgreSQL:
    ALTER TABLE table_name
     DROP CONSTRAINT constraint_name;
    Example:
    ALTER TABLE TELEPHONES
     DROP CONSTRAINT FKA_CUSTOMER;
© 2010 by Finnesand Data. All rights reserved.
This site aims to provide FREE programming training and technics.
Finnesand Data as site owner gives no warranty for the correctness in the pages or source codes.
The risk of using this web-site pages or any program codes from this website is entirely at the individual user.