--DDL Additional Demo--

Drop Table emp;
Drop Table dept;

Create Table emp
(
EmpId int identity(10000,1) primary key,
EmpName varchar(30),
DepId int
)

Create Table dept
(
DepId int identity(20000, 1),
Depname varchar(30) unique
)

--Try to insert ID with IDENTITY Configuration
Insert Into dept Values(1, 'IT')
Insert Into dept (DepId, Depname) Values(1, 'IT')

-- Insert With No ID
Insert Into dept (Depname) Values('IT')
Insert Into dept (Depname) Values('HR')
Insert Into dept (Depname) Values('MK')

select * from dept

--Error
Insert Into emp values(1, 'John Rambo' , null)

--OK
Insert Into emp values('John Rambo', null)

select * from emp

--Add Foreign Key
Alter Table emp
Add Constraint FK_DEPID foreign key(depid)
References dept(depid);

Alter Table dept
add constraint PK_DEPID primary key(depid)

--Add Foreign Key
Alter Table emp
Add Constraint FK_DEPID foreign key(depid)
References dept(depid);

--Insert Another Value
Insert Into emp values('John Rambo1' , null)

select * from emp

--Insert Another Value
Insert Into emp values('John Rambo2' , 10)

select * from emp

--Drop Constraint

Alter Table emp DROP CONSTRAINT FK_emp_dept

--Rename Column--
--Syntax
--exec sp_rename 'table_name.old_column_name', 'new_column_name' , 'COLUMN';
exec sp_rename 'emp.empName', 'empNameNew' , 'COLUMN';

--Rename Table
--Syntax
--exec sp_rename 'old_table_name','new_table _name';
exec sp_rename 'emp','empNew';