Unique constraint for the columns doesn't work when one of values is null.

2 Message(s) by 2 Author(s) originally posted in php sql

From: Pugi!

Date: Friday, September 14, 2007

I have a table

<database> A collection of {records} in a {relational database}.

	(1997-06-04)

with a unique constraint

<programming, mathematics> A {Boolean} {relation}, often an
	equality or {ineqality} relation, between the values of one or
	more mathematical variables (often two).  E.g. x>3 is a
	constraint on x.  {constraint satisfaction} attempts to assign
	values to variables so that all constraints are true.

	{Usenet} newsgroup: {news:comp.constraints}.  {FAQ
	(http://www.cs.unh.edu/ccc/archive/)}.

	(2002-06-08)

on two integer

<mathematics> (Or "whole number") One of the {finite} numbers
	in the infinite set

		..., -3, -2, -1, 0, 1, 2, 3, ...

	An {inductive definition} of an integer is a number that is
	either zero or an integer plus or minus one.  An integer is a
	number with no fractional part.  If written as a {fixed-point}
	number, the part after the decimal (or other base) point will
	be zero.

	A {natural number} is a non-negative integer.

	(2002-04-07)

columns.
Works fine, but one of the two columns can be null

<programming> A special value used in several languages to
	represent the thing referred to by an uninitialised pointer.

	<database> A special value that may be stored in some database
	columns to represent an unknown, missing, not applicable, or
	undefined value.  Nulls are treated completely differently
	from ordinary values when evaluating SQL expressions and there
	are several SQL constructs for dealing with nulls.

	(2003-06-17)

and then the
constraint doesn't work.
1 2 : OK
1 3 : OK
1 2 : error
2 null : OK
2 null : OK -> I want this to result in an error

Pugi!

From: Captain Paralytic

Date: Friday, September 14, 2007

wrote in message:

I have a table with a unique constraint on two integer columns.
Works fine, but one of the two columns can be null and then the
constraint doesn't work.
1 2 : OK
1 3 : OK
1 2 : error
2 null : OK
2 null : OK -> I want this to result in an error
Pugi!

Don't multi-post. Cross-post if you must but do not multi post It
wastes
people's time. http://www.blakjak.demon.co.uk/mul_crss.htm

Next Message: Problem Getting MS SQL Data With Single Quotes

Blogs related to Unique constraint for the columns doesn't work when one of values is null.

The official Magazine Website.
piccilo petes started a business in home care pleasant acres trailer park az www24seven.net pet shop river street hackensack nj wwwutrackonline.com pchcinema.com unique couture mother of the bride notes to parents from teachers realtors ...