ALTER Table in SQL

Let’s see, How can you Alter table in SQL. In SQL, ALTER  statement is used to add, delete, or modify columns in an existing table.

The ALTER TABLE command is used to change the structure of an existing table. It helps to add or delete columns, create or destroy indexes, change the type of existing columns, or rename columns or the table itself.

 

SQL Syntax :

SQL> ALTER TABLE table_name
ADD column_name datatype ;

 

Add a Column to a Table

Using the ALTER TABLE statement we can add the column in the existing table. The column will be added at the end of the table. To add a column in a table, use the following syntax:

SQL> ALTER TABLE employee

         ADD branch_code number(10);

 

Remove a Column from a Table

Using ALTER TABLE command we can also remove the column from the table. To remove the column, use the following syntax :

SQL> ALTER TABLE employees

          DROP (branch_code) ;

 

Change Datatype of Column in Table

Using ALTER TABLE command, we also can change the datatype of the column of Table.

SQL> ALTER TABLE employees

         MODIFY COLUMN Salary Number (25); 

 

ADD or REMOVE PRIMARY KEY of a Table

Using ALTER TABLE command we can add or remove the existing PRIMARY KEY from the table.

Add Primary Key –

SQL> ALTER TABLE  employees

          ADD CONSTRAINT pk_emp_code  PRIMARY KEY(emp_id);  

 

Remove Existing Key –

SQL > ALTER TABLE  agent1  

           DROP CONSTRAINT pk_emp_code; 

 

ADD or REMOVE FOREIGN KEY of a Table

Using ALTER TABLE command we can add or remove the FOREIGN KEY from the table.

Add FOREIGN Key –

SQL> ALTER TABLE departments

          ADD CONSTRAINT   fk_dept_code  

          FOREIGN KEY (dept_code)  REFERENCES employees(departments); 

 

Remove Existing Key –

SQL > ALTER TABLE  departments

           DROP CONSTRAINT fk_dept_code; 

 

ALTER to add or remove CHECK Constraint

Using ALTER TABLE statement, we can add and remove the-the CHECK constraints on the column of the Table.

 

Add CHECK Constraint –

SQL> ALTER TABLE students

          ADD CONSTRAINT  du_che_con  CHECK (grade>=1 AND grade<=3); 

 

Remove CHECK Constraints

SQL> ALTER TABLE students  

          DROP CONSTRAINT du_che_con; 

 

0 Comment

Leave a Reply