SQL Constraint Syntax 2

[kasandoro]

Hey everyone! I need the syntax for MS SQL Server 2000 for a constraint. Here's what I'm trying to do, in plain english:

If [Terminal]='Wilmington, DE' then [Wilmington_Data] is not null

Basically, if the Terminal field is equal to Wilmington, DE, then I want the user to be required to put a value in a different field, [Wilmington_Data]. Anyone have any ideas? Right now, I have:

(((not([Wilmington_Data] is null))) and ((not([terminal] = 'Wilmington, DE'))))

Obviously, this doesn't work. Any ideas?

Thanks in advance!
Sincerely,
Chad

 

[john76]

The only way I can see to enforce this type of constraint is through a trigger.

Here is some sample code that could be modified to meet your needs.

USE tempdb
GO

IF EXISTS(	SELECT		*
			FROM		Information_Schema.Tables
			WHERE		Table_Name = 'Test_Trigger'
)	DROP TABLE	Test_Trigger
GO

CREATE TABLE	Test_Trigger
(
	Field1
		int	NOT NULL
	,
	Field2
		int	NOT NULL
)
GO

CREATE TRIGGER	test_check
	ON		Test_Trigger
	AFTER	Insert, Update

	AS
		BEGIN TRANSACTION
		DECLARE	@Field1	int,
			@Field2	int

		SELECT		@Field1 = Inserted.Field1,
				@Field2 = Inserted.Field2
			FROM		Inserted

		IF @Field1 = 1 AND @Field2 <> 1 BEGIN
			RAISERROR('This transaction is not allowed', 16, 1)
			PRINT '@Field1: ' + CONVERT(varchar, @Field1)
			PRINT '@Field2: ' + CONVERT(varchar, @Field2)
			ROLLBACK TRANSACTION
		END ELSE BEGIN
			COMMIT TRANSACTION
		END
GO

INSERT INTO	Test_Trigger	(	Field1,
					Field2
				)
	VALUES	(	1,
			1
		)

INSERT INTO	Test_Trigger	(	Field1,
					Field2
				)
	VALUES	(	1,
			2
		)
GO

You should be able to cut & paste this into QA and see what is happening.

HTH,
John

 

[nigelrivett]

You don't need the begin tran or commit tran in the trigger as triggers automatically run in a transaction.

alter table mytbl
add constraint x
check ([Terminal]='Wilmington or [Wilmington_Data] is not null)


======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[kasandoro]

@nigelrivett:
Thank you! I was able to use your example and create two check constraints that served my purpose. The first one:

([Terminal] = 'Wilmington, DE' or [Wilmington_Data] is null)

Makes sure that when the terminal is NOT Wilmington, DE that the [Wilmington_Data] field MUST be null. The second constraint:

(((not([Terminal] = 'Wilmington, DE'))) or [Wilmington_Data] is not null)

ensures that when the terminal IS equal to Wilmington, DE that the [Wilmington_Data] field is NOT null.

Thanks for your help!

 

[john76]

nigelrivett,

I had not seen that sort of constraint before, but then again I haven't had the need for that kind of constaint before.

I learned something new, thanks & star from me.

John

 

[nigelrivett]

Hmm.
If you look in bol under check constraint it says

>> CHECK constraints determine the valid values from a logical expression that is not based on data in another column

Which is only true for column check constraints not for table check constraints.
The syntax definition for create table includes table check constraints but doesn't say much about them.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[nigelrivett]

I lied it does say it

>>
It is also possible to apply a single CHECK constraint to multiple columns by creating it at the table level. For example, a multiple-column CHECK constraint can be used to confirm that any row with a country column value of USA also has a two-character value in the state column. This allows multiple conditions to be checked in one place.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[SQLSister]

Yeah it says that but doesn't give any good examples. Neither did any of the books I checked. Nigel this is something most people would do in a trigger and we all know triggers are less efficient than check constraints. Could you write this up as an FAQ - in fact a whole FAQ with a bunch of constraint examples would be great as there are very few examples inthe documentation and people aren't very familiar with how to use them.