SQL Server Performance Forum – Threads Archive
Check Constraint Using SELECTHi, I suspect that I can only achieve the desired outcome of my problem by creating a Trigger on the table. However, I wonder if I can achieve the same outcome with a Check Constraint on the table. I have a contact table which has a Primary Key column (ContactId), a Surrogate Key column for the Customer (CustomerId) and a Contact Type column (ContactTypeId). I have a requirement to ensure that for any Customer, that a maximum of only one Contact Type is permitted for some of the Contact Types. I cannot create a Unique Constraint on the CustomerId and ContactTypeId because the rule is only specific to a few Contact Types. All other Contact records do not have to comply to the rule, i.e. a Customer may have any number of Contact records for the remaining Contact Types. I need to ensure that the rule is enforced for both inserts and updates to the Contact table. Is this possible in SQL Server 2000? I know you can create Check Constraints which compare column values against values, but is it possible to compare the proposed values against existing ones as per my rule. According to MSDN for SQL Server 2005 you can use a Function within a Check Constraint, but it would seem that you cannot with SQL Server 2000.
I wouldn’t really bother with constraints for this. Also, those ugly constraint violation messages do not say anything about what has caused the violation. If you use a trigger, specific information can be fed back through RAISERROR.
You may have a point and I will consider it. Have you any ideas why when using a User Defined Function in the Check Constraint I get an 547 error: ALTER TABLE statement conflicted with TABLE CHECK constraint ‘CK_Contact_PrimaryContactTypeUniqueForCustomer’. The conflict occurred in database ‘DatabaseName’, table ‘Contact’. UDF:
CREATE FUNCTION dbo.fnContactTypeExistForCustomer( @CustomerId INT, @ContactTypeId INT)
RETURNS BIT AS BEGIN DECLARE @Exists BIT IF EXISTS
CustomerId = ISNULL( @CustomerId, 0)
AND ContactTypeId = ISNULL( @ContactTypeId, 0)
) SET @Exists = 1
SET @Exists = 0 RETURN @Exists
What did you do to get that error? Does the table already contain data?
Does the constraint already refer to the UDF?
Well, I have created the UDF, and then I executed the following script: ALTER TABLE
ADD CONSTRAINT CK_Contact_PrimaryContactTypeUniqueForCustomer
CHECK( dbo.fnContactTypeExistForCustomer( Customer, ContactTypeId) = 0)
Yes, the data does already exist within the Contact table. However, I have checked the table, and I did not find any exceptions to the rule. Unless of course, I can only create the Constraint when the table is empty! I am sure the Constraint does not already exist since I have used sp_help against table and searched ‘sysobjects’ table to confirm.
I’m not quite sure that you can pass parameters in a constraint definition, so – for debugging ONLY – I would add these two lines to the UDF: PRINT ISNULL(@CustomerId, -1)
PRINT ISNULL(@ContactTypeId, -1) Then add the constraint with the NOCHECK option – ALTER TABLE ContactADD WITH NOCHECK
CHECK( dbo.fnContactTypeExistForCustomer( Customer, ContactTypeId) = 0) … and insert some sample data through QA. You should see the id values on the Messages tab. The nice thing about triggers is that you have the INSERTED snapshot, so you don’t have to pass any parameters … IF NOT EXISTS
FROM inserted INNER JOIN Contact ON Contact.CustomerId = inserted.CustomerId
WHERE Contact.ContactTypeId = 1)
END and so on and so forth …
Okay, tested this, and you CAN pass a column as a parameter to the UDF from a constraint, it even works for batch operations –
but you have to handle NULLs because the constraint does not fail on NULL. create function dbo.udf(@param int)
Declare @r int
set @r = case when @param is null then 0 else @param END
go CREATE TABLE dbo.x (y int)
go INSERT INTO dbo.x VALUES (null)
INSERT INTO dbo.x VALUES (1) ALTER TABLE dbo.x with nocheck
add constraint z CHECK (dbo.udf(y) <> 0)
go INSERT INTO dbo.x VALUES (null)
INSERT INTO dbo.x VALUES (1) DROP TABLE dbo.x
DROP function dbo.udf
… anyway: my doubt was based on the fact that you cannot send a column as a parameter for a UDF in a default constraint.
… oh, but you can’t use PRINT in a UDF. Stupid old me.
Of course, you can use a UDF as a CHECK constraint for a column. Here’s a simple example:
CREATE FUNCTION dbo.SimpleUDFCheckConstraint(@c1 AS INT) RETURNS BIT
WHEN @c1 IN (SELECT Number FROM master..spt_values WHERE Type = ‘P’)
CREATE TABLE t
c1 INT NOT NULL
CHECK (dbo.SimpleUDFCheckConstraint(c1) = CAST(1 AS BIT))
) INSERT INTO t(c1) VALUES(‘1’)
INSERT INTO t(c1) VALUES(‘-1’)
INSERT INTO t(c1) VALUES(‘0’) SELECT *
FROM t DROP TABLE t
DROP FUNCTION dbo.SimpleUDFCheckConstraint The INSERT with -1 will fail. —
Microsoft SQL Server MVP
Heute schon gebloggt?http://www.insidesql.de/blogs