Which of the following statements will modify the data type of an already existing column?
In this tutorial you will learn how to alter or modify an existing table using SQL. Show
Modifying Existing TablesIt is quite possible that after creating a table, as you start using it, you may discover you've forgot to mention any column or constraint or specified a wrong name for the column. In such situation you can use the Consider we've a shippers table in our database, whose structure is as follows: +--------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------+-------------+------+-----+---------+----------------+ | shipper_id | int | NO | PRI | NULL | auto_increment | | shipper_name | varchar(60) | NO | | NULL | | | phone | varchar(60) | NO | | NULL | | +--------------+-------------+------+-----+---------+----------------+ We'll use this shippers table for all of our Now suppose that we want to expand the existing shippers table by adding one more column. But, the question is how we can do this using SQL commands? Well let's find out. Adding a New ColumnThe basic syntax for adding a new column to an existing table can be given with: ALTER TABLE table_name ADD column_name data_type constraints; The following statement adds a new column fax to the shippers table.
Now, after executing the above statement if you see the table structure using the command +--------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------+-------------+------+-----+---------+----------------+ | shipper_id | int | NO | PRI | NULL | auto_increment | | shipper_name | varchar(60) | NO | | NULL | | | phone | varchar(60) | NO | | NULL | | | fax | varchar(20) | YES | | NULL | | +--------------+-------------+------+-----+---------+----------------+ Note: If you want to add a
Tip: When adding a new column to the table, if neither MySQL add
new columns at the end by default. However, if you want to add a new column after a specific column you can use the mysql> ALTER TABLE shippers ADD fax VARCHAR(20) AFTER shipper_name; MySQL provide another clause Changing Column PositionIn MySQL, if you've already created a table but unhappy with the existing column position within the table, you can change it any time using the following syntax: ALTER TABLE table_name The following statement place the column fax after shipper_name column in shippers table. mysql> ALTER TABLE shippers MODIFY fax VARCHAR(20) AFTER shipper_name; Adding ConstraintsOur current shippers table has one major problem. If you insert records with duplicate phone numbers it wouldn't stop you from doing that, which is not good, it should be unique. You can fix this by adding a constraint ALTER TABLE table_name ADD UNIQUE (column_name,...); The following statement adds a constraint mysql> ALTER TABLE shippers ADD UNIQUE (phone); After executing this statement if you try to insert a duplicate phone number, you'll get an error. Similarly, if you've created a table without a ALTER TABLE table_name ADD PRIMARY KEY (column_name,...); The following statement adds a
constraint mysql> ALTER TABLE shippers ADD PRIMARY KEY (shipper_id); Removing ColumnsThe basic syntax for removing a column from an existing table can be given with: ALTER TABLE table_name DROP COLUMN column_name; The following statement removes our newly added column fax from the shippers table. mysql> ALTER TABLE shippers DROP COLUMN fax; Now, after executing the above statement if you see the table structure, it looks as follow: +--------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------+-------------+------+-----+---------+----------------+ | shipper_id | int | NO | PRI | NULL | auto_increment | | shipper_name | varchar(60) | NO | | NULL | | | phone | varchar(20) | NO | UNI | NULL | | +--------------+-------------+------+-----+---------+----------------+ Changing Data Type of a ColumnYou can modify the data type of a column in SQL Server by
using the ALTER TABLE table_name ALTER COLUMN column_name new_data_type; The MySQL database server however does not support the ALTER TABLE table_name MODIFY column_name new_data_type; The following statement changes the current data type of the phone column in our shippers table from mysql> ALTER TABLE shippers MODIFY phone CHAR(15); Similarly, you can use the mysql> ALTER TABLE shippers MODIFY shipper_name CHAR(15) NOT NULL; Renaming TablesThe basic syntax for renaming an existing table in MySQL can be given with: ALTER TABLE current_table_name RENAME new_column_name; The following statement renames our shippers table shipper. mysql> ALTER TABLE shippers RENAME shipper; You can also achieve the same thing in MySQL using the mysql> RENAME TABLE shippers TO shipper; How do you change the datatype of an existing column?SQL query to change the column type in PostgreSQL database
We can use ALTER TABLE ALTER COLUMN statement to change the datatype of the column. The syntax to change the datatype of the column is the following. In the syntax, Tbl_name: Specify the table name that contains the column that you want to change.
Which command is used to modify existing columns?The SQL ALTER TABLE command is used to add, delete or modify columns in an existing table. You should also use the ALTER TABLE command to add and drop various constraints on an existing table.
Which SQL statement is used to change the data type of a column?The ALTER COLUMN command is used to change the data type of a column in a table.
Which statement is used to modify column values of an existing row in a table?The SQL UPDATE Statement
The UPDATE statement is used to modify the existing records in a table.
|