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;