The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.

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: 21%

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: 20%

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: 32%

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: 652 times

last updated: 04 Feb '10, 19:53