SQL Alter Table ADD

Changing Table Definitions with alter and add

  • Most RDBMS systems allow you to change the structure of a table with the ALTER TABLE command.
  • Syntax for ALTER with ADD can varies from RDBMS to RDBMS, so check your system manuals for specifics.
  • But in the most RDBMS you can:
  1. Add a column definition to a table:

    MySQL, Oracle, SQL server, PostgreSQL:
    ALTER TABLE table_name ADD column_name column_type
    Example:
    ALTER TABLE CUSTOMER ADD TELEPHONE_NUMBER VARCHAR(20);
  2. Add a PRIMARY KEY in a table:

    MySQL, Oracle, SQL server, PostgreSQL:
    ALTER TABLE table_name ADD PRIMARY KEY(column_name, ...) ;

    You have to remove (DROP) the previous primary key in the table before this.

    Example:
    ALTER TABLE CUSTOMER ADD PRIMARY KEY (CUSTOMER_ID,FIRST_NAME);
  3. Add UNIQUE columns in a table:

    MySQL, Oracle, SQL server, PostgreSQL:
    ALTER TABLE table_name ADD UNIQUE(column_name, ...) ;
    Example:
    ALTER TABLE CUSTOMER ADD UNIQUE (FIRST_NAME,LAST_NAME);
  4. Add a FOREIGN KEY CONSTRAINT in a table:

    MySQL, Oracle, SQL server, PostgreSQL:
    ALTER TABLE table_name
     ADD CONSTRAINT constraint_name1 FOREIGN KEY (column_name, ....)
            REFERENCES other_table_name (column_name, ....);
    Example:
    ALTER TABLE ADDRESSES
     ADD CONSTRAINT FKA_CUSTOMER FOREIGN KEY (CUSTOMER_ID)
            REFERENCES CUSTOMER(CUSTOMER_ID);
    ALTER TABLE ADDRESSES
     ADD CONSTRAINT FKA_US_STATES FOREIGN KEY (US_STATE_CD)
            REFERENCES US_STATES(US_STATE_CD); 

    Oracle, SQL server and PostgreSQL require existence of CUSTOMER and US_STATES and possible code connection in those tables.

  5. Add a UNIQUE CONSTRAINT in a table:

    MySQL, Oracle, SQL server, PostgreSQL:
    ALTER TABLE table_name
      ADD CONSTRAINT constraint_name  UNIQUE (column_name, ....);
    Example:
    ALTER TABLE BOOKSTORE
      ADD CONSTRAINT SHORTDESC_IND  UNIQUE (SHORT_DESC);
    ALTER TABLE BOOKSTORE
      ADD CONSTRAINT AUTHOR_IND  UNIQUE (AUTHOR);
  6. Add a CHECK CONSTRAINT in a table:

    MySQL, Oracle, SQL server, PostgreSQL:
    ALTER TABLE table_name
      ADD CONSTRAINT constraint_name
        CHECK (condition ), ....;

    MySQL will ignore CHECK. Use Trigger instead.

    Example:
    ALTER TABLE US_STATES
      ADD CONSTRAINT US_CHECH
        CHECK (US_STATE_CD BETWEEN 'AA' AND 'ZZ' )
© 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.