Constraint needs relaxing? 1

[litton1]

Hi all, I need to put a constraint on a table. This constraint will help to pull out an error in our legacy software, as the legacy software is putting duplicate records in a table.

Fairly simple you might think. My problem is that on one of the fields I only want the constraint to fail if it’s a certain value for eg ‘A’ in the field. This is because I want to cause minimum disruption within the software. Here is my constraint

ALTER TABLE MySmallTable 
	ADD CONSTRAINT UQ_MySmallTable UNIQUE (Feild1, Feild2, Feild3, Feild4)

Assuming that field4 can be an A or a B then I would like it to fail only if it’s an A but not fail if it’s a B.
Is this even possible? If not what are my alternatives?
Thx in advance T


Age is a consequence of experience

 

[RiverGuy]

Write a trigger to check for the values of the inserted/updated records. In the trigger, roll back the transaction and log it when you get the bad value.

 

[litton1]

Thanks for your reply, we want the error to come back to the application. so we need the constraint i think?

Age is a consequence of experience

 

[RiverGuy]

Raise the error in the trigger. Off the top of my head, try this example on a test database

CREATE TABLE Test1 (Col1 VARCHAR(50))
GO

CREATE TRIGGER t1 ON Test1 AFTER INSERT, UPDATE
AS
BEGIN
  IF (SELECT COUNT(*) FROM INSERTED WHERE Col1 = 'A') >= 1
	BEGIN
		ROLLBACK
		RAISERROR ('you entered bad data', 15, 1)
	END
END

GO

INSERT INTO Test1 SELECT 'A' UNION ALL SELECT 'B'
GO

SELECT * FROM Test1
GO

 

[RiverGuy]

I should mention.....I don't completely understand your business rules, and the above code is to illustrate how to do things like this in a trigger. You'll have to add more checking to resolve if there can be an 'A'.

 

[litton1]

Thanks for taking the time to do that, I will have a mess with it tomorrow and get back to you

Age is a consequence of experience

 

[litton1]

Thanks, Giving it a go.

Age is a consequence of experience