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:
-
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);
-
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);
-
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);
-
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.
-
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);
-
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.