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

                       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.

asked 26 Jan '10, 11:57

Breck%20Carter's gravatar image

Breck Carter
accept rate: 20%

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;

(28 Jan '10, 12:25) Thomas Dueme...

@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).

(28 Jan '10, 15:06) Volker Barth

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

permanent link

answered 27 Jan '10, 17:10

Justin%20Willey's gravatar image

Justin Willey
accept rate: 19%

Wondering where the 100 points came from? A random bounty, that's where!

(04 Feb '10, 19:54) Breck Carter

I was, and now I know - isn't knowledge a wonderful thing!

(08 Feb '10, 12:52) Justin Willey

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´).

permanent link

answered 28 Jan '10, 15:10

Volker%20Barth's gravatar image

Volker Barth
accept rate: 34%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:


question asked: 26 Jan '10, 11:57

question was seen: 1,399 times

last updated: 04 Feb '10, 19:53