Such modifications can be done using the ALTER TABLE command. CLUSTER: for marking a table to be used for carrying out future cluster operations.Ī column may be modified in a number of ways.OWNER: for changing the owner of a table, sequence, index or a view to a certain user.DROP CONSTRAINT: Use for dropping a table constraint.ADD table_constraint: Use for adding a new constraint to a table It uses the same syntax as CREATE TABLE command.RENAME: for changing the table name or a column name.
SET WITHOUT OIDS: Use for removing the old column of the table.This will determine where the column is held, whether inline, or in a supplementary table. SET STORAGE: For setting the mode of storage for a column.SET STATISTICS: For setting the statistics-gathering target for each column for ANALYZE operations.SET/DROP NOT NULL: Changes whether a column will allow nulls or not.However, the change will only apply to the subsequent INSERT statements. SET/DROP DEFAULT: Use for removing the default value for a column.The constraints and indexes imposed on the columns will also be dropped. DROP COLUMN: for dropping a table column.ADD COLUMN: this uses similar syntax as CREATE TABLE command to add a new column to a table.The ALTER TABLE command changes the definition of an existing table. The action parameter is the action that you need to perform, such as changing the name of a column, changing the data type of a column, etc. The table-name parameter is the name of the table that you need to change.
Here is the syntax for the PostgreSQL ALTER TABLE command: ALTER TABLE table-name action It is the command used to change the table columns or the name of the table. | 1 | Ramesh | 32 | Ahmedabad | 2000.The ALTER TABLE command is used to alter the structure of a PostgreSQL table. Now, the CUSTOMERS table is changed and following would be the output from the SELECT statement. | 7 | Muffy | 24 | Indore | 10000.00 | NULL |įollowing is the example to DROP sex column from the existing table. | ID | NAME | AGE | ADDRESS | SALARY | SEX | Now, the CUSTOMERS table is changed and following would be output from the SELECT statement. | 1 | Ramesh | 32 | Ahmedabad | 2000.00 |įollowing is the example to ADD a New Column to an existing table − The basic syntax of an ALTER TABLE command to DROP PRIMARY KEY constraint from a table is as follows.Ĭonsider the CUSTOMERS table having the following records −
#ALTER TABLE ADD COLUMN CODE#
If you're using MySQL, the code is as follows − The basic syntax of an ALTER TABLE command to DROP CONSTRAINT from a table is as follows. The basic syntax of an ALTER TABLE command to ADD PRIMARY KEY constraint to a table is as follows.ĪDD CONSTRAINT MyPrimaryKey PRIMARY KEY (column1, column2.) The basic syntax of an ALTER TABLE command to ADD CHECK CONSTRAINT to a table is as follows.ĪDD CONSTRAINT MyUniqueConstraint CHECK (CONDITION) The basic syntax of ALTER TABLE to ADD UNIQUE CONSTRAINT to a table is as follows.ĪDD CONSTRAINT MyUniqueConstraint UNIQUE(column1, column2.) The basic syntax of an ALTER TABLE command to add a NOT NULL constraint to a column in a table is as follows.ĪLTER TABLE table_name MODIFY column_name datatype NOT NULL The basic syntax of an ALTER TABLE command to change the DATA TYPE of a column in a table is as follows.ĪLTER TABLE table_name MODIFY COLUMN column_name datatype The basic syntax of an ALTER TABLE command to DROP COLUMN in an existing table is as follows.ĪLTER TABLE table_name DROP COLUMN column_name The basic syntax of an ALTER TABLE command to add a New Column in an existing table is as follows.ĪLTER TABLE table_name ADD column_name datatype You should also use the ALTER TABLE command to add and drop various constraints on an existing table. The SQL ALTER TABLE command is used to add, delete or modify columns in an existing table.