Conditional Constraints

Question

Post reply

Conditional Constraints

Negmat

SSC Eights!

Points: 863
More actions
November 28, 2006 at 8:41 am

#173616

Guys,
Does SQL Server allow for creating conditional constraints on the table columns?
My table is:
CREATE TABLE [X]
(
            [UID] [int] IDENTITY (1, 1) NOT NULL ,
            [A] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL ,
            [bit] NOT NULL ,
            CONSTRAINT [PK_X] PRIMARY KEY CLUSTERED
            (
            [UID]
            ) ON [PRIMARY]
) ON [PRIMARY]
I want to add the following constraints:
           CONSTRAINT [IX_UNIQUE1] UNIQUE NONCLUSTERED
         (
                   A, B = 1

            ) ON [PRIMARY]
            CONSTRAINT [IX_UNIQUE2] UNIQUE NONCLUSTERED
            (
                 UPPER(A), B = 0

            ) ON [PRIMARY]

That is, if the table contains "Hello" in column A, then an insert of ("hello", 1) will be rejected, but ("hello", 0) will be inserted.
Any ideas?
Thanks a lot

Viewing 11 posts - 1 through 10 (of 10 total)

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

RichB SSCrazy Eights Points: 9651 More actions · Answer 1

write a function and reference the function in the constraint.

eg constraint check (dbo.BobIsRight(colname1, col23) = 'True')

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 2

Or do it in a trigger. That way you can send a more detailed error message.

Negmat SSC Eights! Points: 863 More actions · Answer 3

Negmat

SSC Eights!

Points: 863

November 28, 2006 at 9:05 am

#674406

Thanks a lot guys.

Let me try both approaches.

Negmat SSC Eights! Points: 863 More actions · Answer 4

Guys,

If I create an INSERT trigger and then peform an INSERT of items (A,B), is it possible, inside the trigger, to see the items which are being inserted by the INSERT statement?

That is, I'd like the trigger to behave differently based on the values which get passed to the INSERT statement.

Is that possible?

Thanks a lot

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

All this code is accessible only from inside a trigger

Select * FROM Inserted >> new data (inserted or updated)

Select * FROM Deleted >> old date (updated or deleted)

Negmat SSC Eights! Points: 863 More actions · Answer 6

Negmat

SSC Eights!

Points: 863

November 28, 2006 at 12:35 pm

#674490

This is great info.

Thanks a lot

Negmat SSC Eights! Points: 863 More actions · Answer 7

There appears to be a problem with using a trigger in my case.

Namely, I believe we only have an AFTER TRIGGER and an INSTEAD OF TRIGGER.

If I use INSTEAD OF TRIGGER, then my data would never get inserted.

If I use AFTER TRIGGER, then the data will get inserted before I can run any tests on it.

Is there any way to have an INSERT made only after the trigger finishes its code?

I know that I can perform a rollback, but that won't work for me.

The issue is that I need to check that a certain element is already in the table. But if it is INSERTed first, then it would always be in the table and I won't be able to perform the check correctly.

Is there any other way to go that anyone can see?

Thanks a lot

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 8

Both trigger types could do this. I would probabely use the instead of insert (just a hunce).

Once you validated the new data in the instead of trigger, you have to insert into manually in the table from the trigger for the insert to take place.

Negmat SSC Eights! Points: 863 More actions · Answer 9

Ah ... initially I assumed that by performing an INSERT within the TRIGGER we might enter some infinite loop where the INSERT would fire a TRIGGER, which would in turn perform and INSERT which would fire a trigger ...

Now I tried it and it appears to work fine!

Thanks a lot.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 10

Quoting one member of this forum (sorry forgot your name) :

"When in doubt : test, test, test"