Advanced Check Constraints

Question

I have a table something like this:

create table Comments
(
  ComID int not null,
  ComEntityType tinyint not null,
  ComEntityID int not null,
  ...
  constraint PK_Comments
  primary key(ComID)
)

ComEntityType indicates what entity type this comment is for (Activity, Location, Group, etc.), and ComEntityID is the ID of that entity. Because it can be linked to different tables, I cannot define a relationship in SQL Server.

Someone suggested I could use a CHECK constraint to ensure the data stays valid. Someone even provided an example but I really couldn't understand it, or see how it could accomplish what I need.

I did a little research but the examples I found are simple like "CHECK (col>0)".

Can someone help me understand if I can enforce valid values in these entity fields given my schema?

Thanks.

---

Jonathan Wood • SoftCircuits • Developer Blog

Answer 1

Johathan,

You can take a look at AdventureWorks2008 CHECK constraints:

SELECT TOP 1000 [CONSTRAINT_CATALOG]
   ,[CONSTRAINT_SCHEMA]
   ,[CONSTRAINT_NAME]
   ,[CHECK_CLAUSE]
 FROM [AdventureWorks2008].[INFORMATION_SCHEMA].[CHECK_CONSTRAINTS]
/* Partial results
CHECK_CLAUSE
([BirthDate]>='1930-01-01' AND [BirthDate]<=dateadd(year,(-18),getdate()))
(upper([MaritalStatus])='S' OR upper([MaritalStatus])='M')
([HireDate]>='1996-07-01' AND [HireDate]<=dateadd(day,(1),getdate()))
(upper([Gender])='F' OR upper([Gender])='M')
....
([EndDate]>=[StartDate] OR [EndDate] IS NULL)
(upper([ProductLine])='R' OR upper([ProductLine])='M' OR upper([ProductLine])='T' OR upper([ProductLine])='S' OR [ProductLine] IS NULL)
(upper([Class])='H' OR upper([Class])='M' OR upper([Class])='L' OR [Class] IS NULL)
(upper([Style])='U' OR upper([Style])='M' OR upper([Style])='W' OR [Style] IS NULL)
([SellEndDate]>=[SellStartDate] OR [SellEndDate] IS NULL)

*/

You can also build an UDF to be applied specifically in a CHECK constraint.  Example thread:

http://social.msdn.microsoft.com/Forums/en/sqldocumentation/thread/1d4cf16b-9b37-4eb5-b66b-a428487f42c9

 

---

Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM

Answer 2

Thanks, but while I'm an experienced developer, I only have a working knowledge of SQL. Just looking at complex check constraints isn't really clarifying what I don't understand.

That said, looking at what you posted, I'm pretty sure it doesn't do the type of checks I'd need to do in the example I gave.

---

Jonathan Wood • SoftCircuits • Developer Blog

Answer 3

For complex CHECK constraint, you can build a UDF. The link above shows an example.

Here is another variation of the same. It enforces that the SerialNo starts with 0 for the same kind of vehicles:

USE tempdb;
GO
CREATE FUNCTION udfCheckCarMnfrSerialNo (@VehicleModel varchar(32))
RETURNS tinyint
AS
BEGIN
DECLARE @Result tinyint
IF EXISTS( SELECT * FROM LuxuryCarsToGo
   WHERE VehicleModel=@VehicleModel and SerialNo=0)
	SET @Result= 1
ELSE 
	SET @Result= 0
RETURN @Result
END
GO
CREATE TABLE LuxuryCarsToGo
(VehicleModel varchar(32),   
 SerialNo int,
 CONSTRAINT ckSerialNoStartWithZero 
   CHECK (dbo.udfCheckCarMnfrSerialNo(VehicleModel)=1))
GO

INSERT LuxuryCarsToGo VALUES ('BMW 345', 0)
INSERT LuxuryCarsToGo VALUES ('BMW 345', 1)
INSERT LuxuryCarsToGo VALUES ('BMW 345', 2)
INSERT LuxuryCarsToGo VALUES ('Acura RL', 1)

/*
Msg 547, Level 16, State 0, Line 5
The INSERT statement conflicted with the CHECK constraint 
"ckSerialNoStartWithZero". The conflict 
occurred in database "tempdb", table "dbo.LuxuryCarsToGo", 
column 'VehicleModel'.
*/
INSERT LuxuryCarsToGo VALUES ('Acura RL', 0)
-- (1 row(s) affected)
INSERT LuxuryCarsToGo VALUES ('Acura RL', 1)
INSERT LuxuryCarsToGo VALUES ('Mercedes C300', 0)

SELECT * FROM LuxuryCarsToGo
GO
/* 
VehicleModel	SerialNo
BMW 345	0
BMW 345	1
BMW 345	2
Acura RL	0
Acura RL	1
Mercedes C300	0
*/
DROP TABLE LuxuryCarsToGo
DROP FUNCTION udfCheckCarMnfrSerialNo

 

 

---

 

Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM

Answer 4

Hi Jonathan,

You know, I cannot simply ignore your thread. :)

Kalman' example is great. Only one side note. This check constraint is not "fully" functional referential integrity check. It performs checks at the insert stage although it would not prevent "master" rows (rows that Comments table is referencing to) to be deleted. Check on Comments table would not fire on that point.

If you need to have full blown integrity check solution, the only option you have is the triggers which are not exactly performance-friendly. If performance is the key AND you control all data modifications in your system, you can probably accomplish referential integrity check on the DAL level. With probably additional (scheduled sql) job which will find/process orphan comment.

Again, this method has own pros and cons. Referential integrity on the database level on one side vs. Performance and possible down-side of the orphan rows on the other.

---

Thank you!

My blog: http://aboutsqlserver.com

Answer 5

Hi Dmitri,

Thanks, that's the type of information I was looking for. In some ways, keeping the data valid is more important than checking it is valid when I create it. It depends on how clear and reliable I can make my add and delete functions, as to how much time I'll look at CHECK constraints or triggers.

Thanks.

---

Jonathan Wood • SoftCircuits • Developer Blog

Answer 6

Thanks, I'll need to spend some time on this to determine how much sense this makes to me. I'm printing out your posts and the link.

---

Jonathan Wood • SoftCircuits • Developer Blog