Hierarchy: Check Constraint to Prevent Child->Parent and Parent->Child

Question

Hierarchy: Check Constraint to Prevent Child->Parent and Parent->Child

trapnell

Old Hand

Points: 311
More actions
December 8, 2012 at 7:14 am

#271808

How do I write a check constraint which precludes this situtation in a hierarchy table:
Child |Parent
-------------------
Tom |Joe
Joe |Tom
Thanks,
Tom (but not Joe)

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004441 More actions · Answer 1

trapnell (12/8/2012)
How do I write a check constraint which precludes this situtation in a hierarchy table:
Child |Parent
-------------------
Tom |Joe
Joe |Tom
Thanks,
Tom (but not Joe)

In T-SQL, you either need a Trigger to check or a Scalar Function which would be used in a table level constraint. Like this.

Here's a function to the check on the EmployeeID and ManagerID of a table called dbo.Employee.

CREATE FUNCTION dbo.IsReversePresent

(@EmployeeID INT, @ManagerID INT)

RETURNS INT

AS

BEGIN

RETURN (

SELECT ReversePresent = COUNT(*)

FROM dbo.Employee

WHERE EmployeeID = @ManagerID

AND ManagerID = @EmployeeID

)

END

;

The table would look like this...

--===== Do this in a nice, safe place that everyone has

USE tempdb;

--===== Conditionaly drop the test table so we can do reruns more easily

IF OBJECT_ID('tempdb.dbo.Employee','U') IS NOT NULL

DROP TABLE tempdb.dbo.Employee;

--===== Create the test table with a clustered PK and an FK to itself to make

-- sure that a ManagerID is also an EmployeeID.

CREATE TABLE dbo.Employee

(

EmployeeID INT NOT NULL,

ManagerID INT NULL,

EmployeeName VARCHAR(10) NOT NULL,

CONSTRAINT PK_Employee

PRIMARY KEY CLUSTERED (EmployeeID),

CONSTRAINT FK_Employee_Employee

FOREIGN KEY (ManagerID)

REFERENCES dbo.Employee (EmployeeID)

ON UPDATE NO ACTION

ON DELETE NO ACTION

)

;

--===== Create an additional index to speed things up

CREATE UNIQUE INDEX By_ManagerID_EmployeeID

ON TempDB.dbo.Employee (ManagerID,EmployeeID)

;

All you have to do is add the table level constraint like this...

--===== Add new constraint to check for reversals

ALTER TABLE dbo.Employee WITH CHECK

ADD CONSTRAINT CheckForReverse

CHECK (dbo.IsReversePresent(EmployeeID,ManagerID)=(0))

;

When you try to run something like the following, the constraint will produce an error.

--===== Add new constraint to check for reversals

ALTER TABLE dbo.Employee WITH CHECK

ADD CONSTRAINT CheckForReverse

CHECK (dbo.IsReversePresent(EmployeeID,ManagerID)=(0))

;

[font="Courier New"]Msg 547, Level 16, State 0, Line 2

The INSERT statement conflicted with the CHECK constraint "CheckForReverse". The conflict occurred in database "tempdb", table "dbo.Employee".

The statement has been terminated.[/font]

If you typically insert more than one row at a time, a trigger is likely going to be much more effective because it can check more than one row at a time in a high performance set-based manner.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

trapnell Old Hand Points: 311 More actions · Answer 2

Hi Jeff,

Pefect! An answer which not only works, but explains clearly.

Thanks much,

Tom

Jeff Moden SSC Guru Points: 1004441 More actions · Answer 3

Thanks for the feedback, Tom.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)