The forum will be down for maintenance over the weekend of August 18-20, 2017. The forum will be shut down on the evening (EDT) of Friday, August 18. Downtime is unknown but may be up to two days. The forum will be restarted as soon as maintenance is complete.

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.

asked 26 Jan '10, 11:57

Breck%20Carter's gravatar image

Breck Carter
26.5k433604876
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...
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).

(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
6.8k110144212
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
30.9k310457668
accept rate: 33%

Your answer
toggle preview

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "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:

×107

question asked: 26 Jan '10, 11:57

question was seen: 695 times

last updated: 04 Feb '10, 19:53