Welcome to SQLA Beta

Question

4

How many times have you wanted to code something like this?

DECLARE @upgrade_ok VARCHAR ( 1 ) NOT NULL DEFAULT 'Y' 
                       CHECK ( @upgrade_ok IN ( 'Y', 'N' ) );

If you're like me, lotsa times. In fact, quite a few times I have coded NOT NULL... on purpose, not by accident.

flag	asked Jan 26 at 11:57 Breck Carter♦♦ 1,025●1●11

I type my variables currently with a DOMAIN. It would be great if the constraints would work in this case too. CREATE DOMAIN "BOOLEAN" char(1) DEFAULT 'N' check(@column is null or(@column in( 'Y','N') )); DECLARE @upgrade_ok BOOLEAN; – TDuemesnil Jan 28 at 12:25

1

@TDuemesnil: You can shorten the check - CHECK constraints are only violated when the expression is FALSE, not when UNKNOWN. This is different to search conditions where only expressions that are TRUE get selected. Therefore the "@column is null or "-part can be omitted (though it might be more comprehensible). – Volker Barth Jan 28 at 15:06

Justin Willey · Accepted Answer · 2010-01-27 17:10:37Z UTC

2

Yes please - tried to write that but yesterday before realising you can't do it!!

answered Jan 27 at 17:10

Justin Willey
688●7

	Wondering where the 100 points came from? A random bounty, that's where! – Breck Carter♦ Feb 4 at 19:54
	I was, and now I know - isn't knowledge a wonderful thing! – Justin Willey Feb 8 at 12:52

Volker Barth · Answer 2 · 2010-01-28 15:10:48Z UTC

What about FKs, i.e. something like

CHECK (@value IN (SELECT pk FROM myTable));

I haven't had such requirement but that works with checks on columns and should then work with checks on local variables...

(Note, for columns, one would use a FOREIGN KEY constraint here, I know, but a check with a sub-select to a different table is possible, too, and senseful in some cases where a FK can't be used´).

Please implement constraints for local variable declarations.

2 Answers