ALTER statement.
- ALTER statement alter table design.
- It Add New Column.
- it Add the Column constraints Primary key, foreign key etc.
- it modify column name.
- It modify column data type.
- It Drop the column.
Add new column (Iam using MSSQL Server)
ALTER TABLE table_name ADD column_name DataType;
Example:
CREATE TABLE Employee(id int);
we create table employee with only one column id, now we add extra column using ALTER command.
Employee
id |
int |
ALTER TABLE Employee ADD name nvarchar(20),Age numeric(18) ;
id | name | age |
int | nvarchar(20) | numeric(18) |
ALTER TABLE Employee ADD dept_id int, location nvarchar(10) ;
id | name | age | dept_id | location |
int | nvarchar(20) | numeric(18) | int | nvarchar(10) |
Modify Column datatype:
To change the datatype dept_id int to numeric(18,0)
ALTER TABLE Employee ALTER COLUMN id numeric(18,0);in MS SQL Server.
ALTER TABLE Employee MODIFY id numeric(18,0); in Oracle
ALTER TABLE Employee MODIFY COLUMN id numeric(18,0); in ACCESS
id | name | age | dept_id | location |
int | nvarchar(20) | numeric(18) | numeric(18,0) | nvarchar(10) |
Changing Size of Column:
ALTER TABLE Employee ALTER COLUMN AGE nvarchar;
id | name | age | dept_id | location |
int | nvarchar(20) | nvarchar | numeric(18,0) | nvarchar(10) |
ALTER TABLE Employee ALTER COLUMN name nvarchar(30);
This statement change the size of column name for nvarchar(20) to nvarchar(30);
id | name | age | dept_id | location |
int | nvarchar(30) | nvarchar | numeric(18,0) | nvarchar(10) |
DROP column:
To delete column in table use drop command in alter statement.
ALTER TABLE Employee DROP COLUMN age;
id | name | dept_id | location |
int | nvarchar(30) | numeric(18,0) | nvarchar(10) |
Thank You. Like, Share and Subscribe 👍 Followlect.com
Comments
Post a Comment