Check constraint based on other tables

Question

Check constraint based on other tables

mikek 50666

Right there with Babe

Points: 779
More actions
August 25, 2016 at 4:09 am

#329306

Hi,
I have a number of tables each containing information about a different piece of machine tooling. Each piece of tooling has a tube diameter to indicate the size of tubing that it can be used for.
I then have a table which allows combinations of the machine tooling to be entered by having columns which are the primary keys of the sub table.
Something like the below. What I need is when a tool set record is inserted or updated to check that all the tube_diameters match.
I believe that I can't reference other tables in the check constraint dialog of SQL server management studio but need to create a function to do the comparison and call the function from the check constraint. I am having a hard time getting my head around what the function would look like - how do I tell it which record in the tool set table I am interested in to know the numbers to look at in the sub-tables?
Thanks in advance for any help !
TOOL_SET:
Tool_Set_Number
Clamp_Number
Former_Number
Wiper_Number
Clamp:
Clamp_Number
Tube_Diameter
Length
Height
Former:
Former_Number
Tube_Diameter
CLR
Wiper:
Wiper_Number
Tube_Diameter
Length
Height

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

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

Alan Burstein SSC Guru Points: 61141 More actions · Answer 1

Welcome to the SQLServerCentral forums.

This should be easy to sort out but it would help if you could provide some DDL, a few rows of sample data and an example of what you (in this case) don't want to allow. Note the link in my signature about getting help -its very useful. In the meantime, here's a high-level explanation of how to tackle this:

You are correct that a scalar function is the way to do what you want. The way I tackle this type of thing is to first create a scalar function that looks like this:

CREATE FUNCTION dbo.fnCheckSomething()

RETURNS BIT AS

BEGIN

DECLARE @isbad bit = 0;

IF EXISTS (< condition that you don't want >)

SET @isbad = 1;

RETURN @isbad;

END

Then your CHECK constraint would look like this:

CHECK (dbo.fnCheckSomething() = 0)

Be warned that scalar functions usually hurt performance. Just the presence of a scalar udf on a table (either by way of CHECK constraint or computed column) often causes the query optimizer to create a poorer execution plan than it would otherwise - even when that column in question is not referenced.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

And be warned that such a constraint doesn't work properly. If, for example, you put a constraint using a scalar udf like that on the TOOL_SET table, it only gets checked when the TOOL_SET is changed. If it was checking existence of rows in the other three tables, it would not prevent those other three tables having the rows changed in such a way as to make a row in TOOL_SET not satisfy the constraint.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

mikek 50666 Right there with Babe Points: 779 More actions · Answer 3

Hi,

Thanks for the reply.

What I can't get my head around is how in the function to reference the correct rows, i.e. I enter a new tool set, the constraint calls the function, but how do I pass the function the correct row to know which values to check? So in the example below when the 4th line is inserted I presume I need do do a select statement to get the former number, clamp number and pressure die number where the tool set number matches that inserted then do a further select in each of the sub tables to get the tube diameters to compare them?

Example data:

dbo.Tool_Set

Tool_Set_Number Former_NumberClamp_NumberPressure_Die_Number

T1 F1 C1 P1 <--- Tube diameter of F1, C1 and P1 all 32 so OK

T2 F2 C4 P2 <--- Tube diameter of F2, C4 and P2 all 20 so OK

T3 F3 C2 P3 <--- Tube diameter of F3, C2 and P3 all 16 so OK

T4 F2 C2 P2 <--- Tube diameter of F2 and P2 is 20 but C2 is 16 so bad data

dbo.Pressure_Die

Pressure_Die_NumberTube_DiameterLengthHeightWidth

P1 32 200 70 70

P2 20 200 70 70

P3 16 200 70 70

dbo.former

Former_NumberTube_Diameter CLROverall_HeightCentre_Line_Height

F1 32 6480 20

F2 20 4080 20

F3 16 4880 20

dbo.clamp

Clamp_NumberTube_DiameterLengthHeightWidth

C1 32 96 60 80

C2 16 48 60 80

C3 16 32 60 80

C4 20 50 60 80

#########EDIT#####

Thanks Gail, OK if this method will not work what is the correct way to do this?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

mikek 50666 (8/25/2016)
Thanks Gail, OK if this method will not work what is the correct way to do this?

This method is about the only way to do it, unless you can change the table design such that you can use normal foreign key references.

Also, if you're absolutely certain that the relevant information in the child tables won't be updated after a row in Tool_Set has been created, then the scalar function method is safe. Otherwise you'll need to put constraints/triggers on all of the child tables to prevent data changes that would invalidate a row in Tool_Set.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass