ON UPDATE/DELETE CASCADE SQL SERVER

ON UPDATE/DELETE CASCADE


Let's explore CASCADE and see how it work.

USE Test2
GO

--Let's create a parent table Named Employee

IF EXISTS (SELECT * FROM sys.objects
WHERE name = N'Employees' AND [type] = 'U')
DROP TABLE Employees
CREATE TABLE Employees
(
EmplID INT  PRIMARY KEY ,
FName VARCHAR(30),
MName VARCHAR(1),
LName VARCHAR(30),
Division VARCHAR(30)
)
GO

--Let's create a child table Named Employee Education

IF Exists (SELECT * FROM sys.objects WHERE name = N'EmployeeEducation' AND [type] = 'U')
DROP TABLE EmployeeEducation;
CREATE TABLE EmployeeEducation (
EducationID int IDENTITY (100, 1) Primary Key,
EmplID int, 
HighSchoolName Varchar(70),
HS_City Varchar(50),
YearPassed int,
UnderGraduate Varchar(70),
UG_City Varchar(50),
UG_YearPassed int,
Graduated bit,
)
GO

--Create Foreign Key relationship between Employee and EmployeeEducation Table

IF EXISTS (SELECT * FROM sys.objects where name = N'FK_EmployeeEducation_Employees' and [type] = 'F')
ALTER TABLE EmployeeEducation
DROP CONSTRAINT FK_EmployeeEducation_Employees
GO

ALTER TABLE EmployeeEducation
ADD CONSTRAINT [FK_EmployeeEducation_Employees]
Foreign Key (EmplID) References [Employees](EmplID)

--Time to Populate Employee Table


Insert INTO Employees 
SELECT 1,'David', 'D', 'Moore', 'IT' UNION ALL
SELECT 2,'AtulKumar', 'G', 'Patel', 'MKT' UNION ALL
SELECT 3,'Sanjay', 'C', 'Kumar', 'HR' UNION ALL
SELECT 4,'Telly', 'M', 'Morris', 'Purchasing'

Select * FROM Employees

--Time to Populate EmployeeEducation Table

Insert INTO EmployeeEducation
SELECT 1,'Saint Thomas High School', 'Dallas',1987,' BS Computer Science', 'Richardson', 1992, 0 UNION ALL
SELECT 2,'Saint Patrick Monroe School', 'Richardson',1988,' BS Artificial Intelligence', 'Plano', 1990, 1 UNION ALL
SELECT 3,'Saint Thomas High School', 'Plano',1989,' BS Computer Science', 'Irving', 1994, 1 UNION ALL
SELECT 4,'Saint Thomas High School', 'Irving',1990,' BS Computer Science', 'Wichita', 1995, 0 

Select * FROM EmployeeEducation


--Now try to Update Employee Table For 'David'

UPDATE Employees
SET EmplID = 100
Where EmplID = 1

--Msg 547, Level 16, State 0, Line 1
--The DELETE statement conflicted with the REFERENCE constraint "FK_EmployeeEducation_Employees". The conflict occurred in database "PK_Test", table "dbo.EmployeeEducation", column 'EmplID'.
--The statement has been terminated.


--Now Try to Update Employee Table

DELETE FROM Employees
Where EmplID = 2

--We get this error message
--"Msg 547, Level 16, State 0, Line 2
--The DELETE statement conflicted with the REFERENCE constraint "FK_EmployeeEducation_Employees". The conflict occurred in database "PK_Test", table "dbo.EmployeeEducation", column 'EmplID'.
--The statement has been terminated."

--BRING CASCADE ON

--Let's first drop FK Relationship

IF EXISTS (SELECT * FROM sys.objects
WHERE name = N'FK_EmployeeEducation_Employees' AND [type] = 'F')
ALTER TABLE EmployeeEducation
DROP Constraint FK_EmployeeEducation_Employees
GO

--Lets Add CASCADE OPTION to Our Constraints

ALTER TABLE EmployeeEducation
ADD CONSTRAINT [FK_EmployeeEducation_Employees]
FOREIGN KEY (EmplID) References [Employees](EmplID)
ON DELETE CASCADE ON UPDATE CASCADE 
GO

--Now try this time to Update Employee Table For 'David'

Do a Select of both Table.

Select * FROM  Employees;
Select * FROM EmployeeEducation;


This is what you will get.

Now run this UPDATE


UPDATE Employees
SET EmplID = 5
Where EmplID = 1

And now this will be the result:

--So we see that with Cascade On, we were able to update or delete.RIGHT!!!!

--Question is: Why do we want to USE or NOT USE CASCADE ON Option

-- The real use of CASCADE ON is when we have primary key which are not IDENTITY Column in many real world situation. For example Lets say we have 10 digit SSN number sitting somewhere in our government database. we have a master table which maintain SSN and thousands of other tables (banks, benefits, etc) which is referencing this column. Lets say we make a request to change our SSN from government. They obliged to change our SSN number (Well thank if they are able to do it!!!). In that case, lets assume that various bank agency,HR, and thousands of other departments need to be updated at the sametime.

--So by using CASCADE option for UPDATE, if we update SSN in main table, automatically it will be updated every other table it is being referenced.

If you use a natural key (e.g. a regular field from your database table) as your primary key, then there might be certain situations where you need to update your primary keys. Another recent example would be the ISBN (International Standard Book Numbers) which changed from 10 to 13 digits+characters not too long ago.

Follow MSDN for reference: Cascade on MSDN

Column, Table and Database and how do we find which one is where ?

Hello Again!!!

Last evening I went to a SQL user group meeting and heard an interesting conversation between two geek. The back ground is that this person was looking for SQL Server Developer person for his company (A JOB man!!!!).

So naturally I was interested to eve-drop into their conversation (not a good thing to anyway) and I heard this bigger geek asking lesser geek that how do can you give me a list of all the tables where a particular column is used.

So me trying to be smart (.. wait a minute!!!! I am not smart but I am trying to be geek...), thought hmmm that must be stored somewhere in system table or view some where. May be sys.objects? that was my first guess, then sys.columns??? bingo.. I got it..

So this morning I can in early for my work and thought let me give a try and see what all I can come up with. So after few hour of brain-storming on this (Include google search too.. and thanks to all those wonderful people who write blogs about their work), I finally figured out what I need to write.

So here I am sharing with you guys.. (Hope someday it will come handy to someone!!)

Let's work with AdventureWorks database.

Lets look at sys.object table first.

This table list all the objects that are present in your database. If someone ask you a question, give a query which give me all the table in database (not talking about system tables but user tables), this is the place where I can go and get the information from.

Do a basic select * from sys.objects will give you all the object in that particular data base. Now going through this, you will see name, object_id, schema_id, type and type_desc beside other columns.

One thing here is that object_id and name is ideally same thing. You can convert object_id column to name field by using Object_Name function like this.

Select OBJECT_NAME(OBJ.Object_ID) As Object_ID_Name, OBJ.Name from sys.objects OBJ

The two column returned is identical.

So coming to original question, "Give me query which will give me all the table in a database?"  we want to know that table he want to know are "USER_TABLE" which will be under type_desc column and parent_object_id will be zero (0) because these are table and are always parents.

So my query will look something like this.



SELECT Object_Name(O.object_id) 'Name'
,O.type_desc
,O.type
,O.schema_id
,O.create_date
FROM sys.objects O
WHERE O.parent_object_id = 0
AND O.type_desc = 'USER_TABLE'
ORDER BY o.Schema_id
,O.NAME;

This will return all the table in my database which has been created by users

:)

Next question he asked was give me a list of all the table where a particular column is used? for example show me all the table where column "AddressLine1" is being referred?


To do this, we have to look at another system table called sys.columns. This table list all the columns defined in our database. Also we want to what is datatype of "AddressLine1" is it varchar, char, nvarchar or some smart person defined as int.


To do so


SELECT C.NAME 'Col Name'
,Object_Name(C.object_id) 'Table Name'
,T.NAME 'Data Type'
FROM sys.columns C
INNER JOIN sys.types t ON C.system_type_id = t.system_type_id
WHERE C.NAME = 'AddressLine1';



 More to come here... If you have questions, feel free to ask...

SQL Server 2008 R2: Error returned was 15517 (CDC)

Ok!!!!

Here I come.

ERROR 15517

To fix this error, first you need to check who is "DBOwner" of that database.

This error is thrown when you are trying to enable CDC feature on a particular database which has "DBOwner" value of NULL.

Msg 22830, Level 16, State 1, Procedure sp_cdc_enable_db_internal, Line 186 Could not update the metadata that indicates database CDC_AdventureWorks is enabled for Change Data Capture. The failure occurred when executing the command 'SetCDCTracked(Value = 1)'. The error returned was 15517: 'Cannot execute as the database principal because the principal "dbo" does not exist, this type of principal cannot be impersonated, or you do not have permission.'. Use the action and error to determine the cause of the failure and resubmit the request. Msg 266, Level 16, State 2, Procedure sp_cdc_enable_db_internal, Line 0 Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1. Msg 266, Level 16, State 2, Procedure sp_cdc_enable_db, Line 0 Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1. Msg 3998, Level 16, State 1, Line 1 Uncommittable transaction is detected at the end of the batch. The transaction is rolled back.

Let's take "AdventureWorks" as our sample database.

Run following script in SSMS


SELECT name
        ,database_id
        ,USER_NAME(owner_sid) as DBOwner
FROM sys.databases WHERE name ='AdventureWorks'


IF you see 'DBOwner' as NULL value, change this to 'sa'.

To do this run following script

Use AdventureWorks
GO
Exec sp_changedbowner 'sa'
GO


Even we are saying to change it to 'sa' which is not our loginID or userAccount, what this does is that it assign the "DBOwner" to "dbo" over which we can implement CDC.

Now you are all set to get rid of this error.


Note: More to come...