Skip to main content

FOREIGN KEY WITH DELETE CASCADE

DELETE CASCADE: Foreign key delete cascade means when parent table records is deleted the corresponding child table records also deleted.
--CREATE DEMOS DATABASE
CREATE DATABASE DEMOS
USE DEMOS

CREATE TABLE DBO.DEPT
(
            ID INT PRIMARY KEY,
            DEPTNAME VARCHAR(100)
)


--INSERT DATA TO DEPT TABLE
INSERT INTO DBO.DEPT(ID,DEPTNAME) VALUES(1,'SOFTWARE'),(2,'BPO')


--CREATE EMP TABLE
CREATE TABLE DBO.EMP
(
            ID INT IDENTITY(1,1) PRIMARY KEY,
            FIRTSNAME VARCHAR(100) ,
            LASTNAME VARCHAR(100),
            LOCATION VARCHAR(100),
            DOB DATETIME,
            SALARY MONEY,
            DEPT INT
)

--ADD FOREIGN KEY TO EMP TABLE
ALTER TABLE DBO.EMP
ADD CONSTRAINT FK_DEPTID FOREIGN KEY (DEPT)
REFERENCES DBO.DEPT(ID) ON DELETE CASCADE ON UPDATE CASCADE

--INSERT SOME DATA TO EMP TABLE
INSERT INTO DBO.EMP(FIRTSNAME,LASTNAME,LOCATION,DOB,SALARY,DEPT)
VALUES
('RAKESH','KALLURI','HYDERABAD','07-23-1989',24000,1),
('NARESH','CH','PUNE','07-23-1987',48000,1),
('SRUJAN','KUMAR','HYDERABAD','07-23-1988',25000,1),
('VENKATESH','BODUPPALY','HYDERABAD','07-23-1986',32000,2),
('ALI','MD','HYDERABAD','07-23-1987',38000,2),
('GANGA','RAJAYAM','PUNE','05-26-1987',390000,2),
('RAVI','KUMAR','CHENNAI','03-23-1986',47000,1),
('PRAVEEN','KUMAR','DELHI','07-23-1988',33000,2)


SELECT * FROM DBO.DEPT













SELECT * FROM DBO.EMP











DELETE FROM  DBO.DEPT WHERE ID=1

Whenever we are trying to Delete records from DEPT table Whose DEPTID Is 1, this child EMP table records also get deleted.













--CHECK THE RECORDS AFTER DELETE
 SELECT * FROM DBO.EMP



                       


Labels:

Comments

Post a Comment

Popular posts from this blog

Coalesce function

Coalesce function returns the first non-null value among the arguments. Syntax: Coalesce (expression [,..n]) Here is example using Coalesce function Example 1 DECLARE @Str1 varchar ( 10 ), @str2 varchar ( 20 ), @Str3 varchar ( 20 ) SET @Str2 = 'Sql' , @Str3 = 'Server' SELECT COALESCE ( @Str1 , @str2 , @Str3 ) As [Coalesce] In above example @Str2 value is ‘Sql’ , @str3 value is ‘Server’  and @str1 values is Null because it not assigned any value . Output: It return’s “Sql” because Coalesce function return’s first non null value. Example 2: Coalesce in select statement. IF OBJECT_ID ( 'Employee' , 'U' ) IS NOT NULL DROP TABLE Employee CREATE TABLE Employee (   ID INT IDENTITY ( 1 , 1 ) PRIMARY KEY ,   NAME VARCHAR ( 20 ),   SALARY INT ) INSERT INTO Employee   ( NAME , SALARY ) VALUES ( 'Rakesh' , 5000 ),(NULL, 6000 ),( 'Naresh...
1 comment
Read more

Variables in T-SQL

Variables can be used to store the data in temporally based on data type. Variable name begin with @ symbol. There are two types of variables in T-SQL. 1.   Local Variables (It must begin @ symbol). 2.   Global Variables (it must begin @@ symbol) this variables also known as system variables. Syntax Declare a Variable DECLARE   @Variable-Name   DATATYPE EXAMPLE: DECLARE   @Name   VARCHAR ( 100 ) In before 2008 versions of SQL SERVER we declare a variable and assign a value in 2 lines.                 DECLARE   @Name   VARCHAR ( 100 ) SET   @Name = 'LeadFirstSQL' In 2008 and Later versions of SQL SERVER we declare a variable and assign a value in single line. DECLARE   @Name   VARCHAR ( 100 )   = 'LeadFirstSQL' Variables must be declare and used with the same batch. DECLARE   @Name   VARCHAR ( 100 ) ...
Post a Comment
Read more

CONCAT FUNCTION IN SQL SERVER 2012

Concat function is used concatnating the values among the different arguments. Syntax: Concat([String1],[String2]..[StringN]);  DECLARE @FName VARCHAR ( 50 ), @LName VARCHAR ( 50 ); SET @FName = 'Rakesh' ; SET @LName = 'Kalluri' ; SELECT CONCAT ( @FName , ' ' , @LName ) as [FullName] ; Output: Rakesh Kalluri --Before SQL 2012 Versions we can use like this way DECLARE @FName VARCHAR ( 50 ), @LName VARCHAR ( 50 ); SET @FName = 'Rakesh' ; SET @LName = 'Kalluri' ; SELECT @FName + ' ' + @LName as [FullName] ; Output: Rakesh Kalluri --But Before SQL 2012 Versions there are 2 problems when we  contacting . --1.Need to replace NULL Value to Empty.if we directly concat NULL Value the  Entire  result also NULL. --Before 2012: DECLARE @FName VARCHAR ( 50 ), @LName VARCHAR ( 50 ); SET @FName = 'Rakesh' ; SELECT @FName + ' ' + @LName a...
Post a Comment
Read more