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

Comments

SET ROWCOUNT

Stop the processing after the number of rows has been affected. Example: Step-1: Create the Table and insert some sample data. CREATE TABLE STUDENT ( ID INT IDENTITY ( 1 , 1 ) PRIMARY KEY , STUDENTNAME VARCHAR ( 100 ) NOT NULL , LIVINGLOCATION VARCHAR ( 100 ) NULL ) INSERT INTO STUDENT ( STUDENTNAME , LIVINGLOCATION ) SELECT 'Rakesh' , 'Hyderabad' UNION ALL SELECT 'Raju' , 'Delhi' UNION ALL SELECT 'Madhu' , 'Hyderabad' UNION ALL SELECT 'Naresh' ,NULL UNION ALL SELECT 'Venaktesh' , 'Chennai' In above Insert Statement we inserted 5 sample records. Step-2: Using SET ROWCOUT SET ROWCOUNT 3 ; SELECT * FROM STUDENT ORDER BY ID DESC It will affected 3 rows after that processing has been stopped. Step-2: Using SET ROWCOUT 0 SET ROWCOUNT 0 ; SELECT * FROM STUDENT ORDER BY ID DESC ...

Simple Trick Using ISNULL

Simple Trick Using ISNULL: Step-1: Create Sample Student Table. CREATE TABLE Student ( Id INT IDENTITY ( 1 , 1 ) PRIMARY KEY , StudentName VARCHAR ( 100 ) NOT NULL, LivingLocation VARCHAR ( 100 ) NULL ) Step-2 :Insert Some Sample Data INSERT INTO Student SELECT 'Rakesh' , 'Hyderabad' UNION ALL SELECT 'Raju' , 'Delhi' UNION ALL SELECT 'Madhu' , 'Hyderabad' UNION ALL SELECT 'Naresh' ,NULL UNION ALL SELECT 'Venaktesh' , 'Chennai' Step-3 : Select Data from Student Table SELECT * FROM Student If we observe above data of student table we have student id 4 record LivingLocation is NULL. Step -4 :Select Data from Student Table With Where Clause. SELECT * FROM Student WHERE LivingLocation = 'Hyderabad' In the above query we are included the where clause w...

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...

Tech Learn

Search This Blog