SQL Tutorial for Create,Alter,Rename,Drop a table and adding Primary Key,Foreign Key to your tables

Bangalore: Here we are going to discuss some DDL queries which we are using frequently in our databse operations. Tools Required: SQL Server Management Studio. Create a Test database in your SQL server. normally we will create our tables columns all things through our SSMS, here we are discussing how we can create it through query.
so all these Create,Alter,Drop queries comes under DDL(Data definition Language) because its used to modify the db ojects in the databse.

1) Create Table

use 
Test 
go

Create Table deptmnt(
dept_id int primary key identity(1,1),
depart_name varchar(50)
) 

It will create a table `deptmnt` in your `Test` database with two columns `dept_id` and `depart_name` 

here department id is a primary key and it will auto increment by 1 when we add next row to the table.

because we have given identity(1,1).

if we have given identity(2,2) it will increment by two.

2) Drop Table

Drop Table deptmnt;

This sql query will drop your table deptmnt.

3) Alter Table

If we want to do any modifiaction in our already created table we will use alter key word.

Create Table employee(

employee_id int,

employee_name varchar(50)

) 

So we have created a brand new table employee we want add one more column to this table so we will alter our table.

ALTER TABLE employee

Add  dept_id int

It will add one more column to your table.

So here employee_id is not primary key and its not auto incrementing one so we are going to implement it through sql queries.

3.1) Altering column into primary key and adding identity

First we will drop the employee_id field

ALTER TABLE employee

   drop column employee_id 

Then we will add employee_id column as identity

ALTER TABLE employee

   ADD employee_id  INT IDENTITY(1,1)

   

Then we will include the primary key constarint to the Column

   ALTER TABLE employee

   ADD CONSTRAINT PK_employee_id

   PRIMARY KEY(employee_id)

3.2) Altering Column Data Type

It will change data type employe_name varchar 50 to 100 or you can change to other data types also.

ALTER TABLE employee

ALTER COLUMN employee_name varchar(100)

4) Adding Foreign Key Reference

Here we have two tables department and employees so we need to add a foreign key relation ship between these two tables dept_id in employee table is related with department id in department table.

so foreign key relation in employee table with department table.

ALTER TABLE employee

ADD CONSTRAINT fk_employee_dept_id  FOREIGN KEY(dept_id ) REFERENCES deptmnt(dept_id )

GO

5) Renaming Table/Column in SQL

Here we want to rename our deptmnt table into department so here is the sql query.

old to new

sp_RENAME   'deptmnt ' , 'department '

Now we want to change the department_name column  into d_name so here we go.

sp_RENAME 'department .department_name ' , 'd_name ', 'COLUMN'