SQL Alter Table Column DEFAULT

Changing Table columns default value

  • Most RDBMS systems allow you to change the structure of a table with the ALTER TABLE command.
  • The syntax for changing or adding a DEFAULT value for a column is somewhat different for each RDBMS
  • Below are guidelines for how to change the DEFAULT value for a column using some selected RDBMS;

Change the default value for a column:

MySQL, PostgreSQL:
ALTER TABLE table_name ALTER column_name SET DEFAULT value ;
Example:
ALTER TABLE TELEPHONES ALTER TELEPHONE_NUMBER SET DEFAULT 'NO PHONE';
Oracle:
ALTER TABLE table_name MODIFY column_name DEFAULT value ;
Example:
ALTER TABLE TELEPHONES MODIFY TELEPHONE_NUMBER DEFAULT 'NO PHONE';
SQL server:
ALTER TABLE table_name ADD CONSTRAINT constraint_name 
  DEFAULT value FOR column_name;
  • This is not a modify type, but ADDs a DEFAULT value to a column.
  • We need to remove the constraint if it exists before this.
Example:
ALTER TABLE  TELEPHONES DROP CONSTRAINT DF_TELEPHONE_NUMBER;
/* we need to remove the constraint if it exists*/
ALTER TABLE TELEPHONES ADD CONSTRAINT DF_TELEPHONE_NUMBER 
  DEFAULT 'NO PHONE' FOR TELEPHONE_NUMBER;
© 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.