ALTER TABLE > ALTER COLUMNS > DROP COLUMNS IN SQL SERVER

 ALTER TABLE : The ALTER TABLE command is used to alter the columns in a table or add, deletes and modifies columns in a table. So the ALTER TABLE command is used as below:

We have an EmployeeDetail table in TESTDB database and we will add some new columns in EmployeeDetail table by alter command. The syntax od ALTER TABLE is as:

ALTER TABLE EMPLOYEEDETAIL

ADD CONTACT_NUMBER(18,2), ALTERNATE_CONTACT_NUMBER NUMERIC(18,2)

The example is as below:

Here, we have added two new columns i.e. CONTACT_NUMBER, ALTERNATE_CONTACT_NUMBER in EMPLOYEEDETAIL table. So if we want to change the data type of these columns then we will use ALTER COLUMN command.

ALTER COLUMN : The ALTER COLUMN command is used to change the data type of an existing columns in a table. So here we will change a data type of added columns i.e. CONTACT_NUMBER, ALTERNATE_CONTACT_NUMBER in EMPLOYEEDETAIL table. 

The syntax of ALTER COLUMN is as below:

ALTER TABLE EMPLOYEEDETAIL

ALTER COLUMN CONTACT_NUMBER(NVARCHAR(20)),

ALTERNATE_ CONTACT_NUMBER(NVARCHAR(20))

Note: The SQL Server doesn't allow to alter multiple columns with single ALTER TABLE command. 

The example is as below:

Here, we have changed data types of added columns i.e. CONTACT_NUMBER, ALTERNATE_CONTACT_NUMBER in EMPLOYEEDETAIL table. So if we want to drop these columns then we will use DROP COLUMN command.

DROP COLUMN : The DROP COLUMN command is used to delete or drop an existing columns from table. So here we will drop columns i.e. CONTACT_NUMBER, ALTERNATE_CONTACT_NUMBER from EMPLOYEEDETAIL table. 

The syntax of DROP COLUMN is as below:

ALTER TABLE EMPLOYEEDETAIL

DROP COLUMN CONTACT_NUMBER , ALTERNATE_CONTACT_NUMBER

The example is as below:

          

            - - - - - - Cheers, Happy to Help! - - - - - - 

Different types of Keys in SQL Server

Key: A key is a single or combination of more than one columns in a table. We can also say that a key is a field or columns in a table in database. There are the different types of keys in sql server. These are as below:

Primary Key: A primary key is a key that uniquely identify a each record in a table. A primary key can't accept null value while set primary key on a column during table creation in database. Here, ID column is a primary key of an Employee table. Please see the below screenshot.

Super Key: A super key is a set of attributes or set of columns which identify a specific row in a table. A Unique Key, Primary key and Alternate key are a subsets of Super Key. Here, ID, EMP_CODE, EMAIL columns are the super keys of an Employee table. Please see the below screenshot. 

Alternate Key: A alternate key is a field or set of fields that uniquely identify a record in a table. For example ID is a primary key and the other fields i.e. emp_code and email are the alternate keys in table. There will be only one column as primary key in a table. Here, EMP_CODE, EMAIL columns are the alternate keys of an Employee table. Please see the below screenshot. 

Candidate Key: A candidate key is a column or combinations of columns that identify a tuple in a table. There are more than one candidate keys in a table. A candidate key works as same as primary key that uniquely identify a tuples. Here, ID, EMP_CODE, EMAIL columns are the candidates keys of an Employee table.  Please see the below screenshot.

Unique Key: A unique key is a key that uniquely identify a record in a table.Its a combinations of one or more than one columns in a table. A unique key works as same as primary key but it doesn't allow duplicate value in a column. Here, EMP_CODE column is a unique key of an Employee table. Please see the below screenshot.

Foreign Key: A foreign key is a key that is used as a primary key of one table and its links as a foreign key in another table. Mainly, it creates relationship between two or more than two tables. There will be duplicate values in columns when using foreign key in the tables. Here, EMPLOYEE_ID column is a foreign key in EmployeeDetail table and its primary key i.e. ID column in Employee table. Please see the below screenshot. 

Composite Key: A composite key is a set of more than one columns of a table that uniquely identify a particular record in a table. 

           - - - - - - Cheers, Happy to Help! - - - - - -