Using a Constraint to disallow duplicate values.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Using a Constraint to disallow duplicate values.

Author

Topic

mdelgado
Posting Yak Master

141 Posts

Posted - 2002-10-08 : 11:07:19

Hello all.

I have a tabel called [MT]. I have a field called [LOGON] that needs to accept null values. However, I need to enforce uniqueness on all NON-NULL values.

Any tips on how to do this?

thanks.

1fred
Posting Yak Master

158 Posts

Posted - 2002-10-08 : 11:22:13

you can make a trigger like this:

create trigger InsertLogon on dbo.MT for Insert
as

declare @logon varchar(50)
set @logon = ''
select @logon=logon from MT where logon = inserted.logon and logon <> null

if ( @logon <> '')
begin
delete from MT where id = inserted.id
end

lozitskiy
Starting Member

28 Posts

Posted - 2002-10-08 : 11:34:31

I like triggers very much but for this situation UNIQUE CONSTRAINT will be more suitable.

-------------
MCP MSSQL

1fred
Posting Yak Master

158 Posts

Posted - 2002-10-08 : 11:41:05

I don't think a unique constraint will allow two NULL in a same collumn

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2002-10-08 : 19:27:44

Shouldn't logon <> null be logon IS NOT null?

JustinBigelow
SQL Gigolo

1157 Posts

Posted - 2002-10-08 : 21:58:07

quote:

I don't think a unique constraint will allow two NULL in a same collumn

Actually since NULL does not equal another NULL, a unique constraint would not have a problem with NULL values in the column.

From BOL

quote:

A column that allows null values.
UNIQUE constraints can be defined on columns that allow null values, whereas PRIMARY KEY constraints can be defined only on columns that do not allow null values.

Justin

Have you hugged your SQL Server today?

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-10-09 : 10:23:21

quote:

a unique constraint would not have a problem with [multiple] NULL values in the column

Try it:


CREATE TABLE UniqueTest (
  pk int PRIMARY KEY,
  uc int NULL UNIQUE
)

GO

INSERT INTO UniqueTest VALUES (1, 1)
INSERT INTO UniqueTest VALUES (2, NULL)
INSERT INTO UniqueTest VALUES (3, NULL)

Edited by - Arnold Fribble on 10/09/2002 10:27:27

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-10-09 : 11:42:05

quote:
Actually since NULL does not equal another NULL

Sure, that is true, but at the same time NULL does not equal itself either ...


if null = null
    print '1'
if null <> null
    print '2'

The way this black-hole is dealt with for a unique constraint is that it will allow one null but no more.

Jay White
{0}

mdelgado
Posting Yak Master

141 Posts

Posted - 2002-10-09 : 12:52:58

Not if you setup a constraint on two fields; the null field and another.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-10-09 : 13:07:54

If you mean:

CREATE TABLE UniqueTest (
pk int not null PRIMARY KEY,
logon int NULL
CONSTRAINT UNQ UNIQUE (pk, logon)
)

That will still allow duplicate logon values:

INSERT INTO UniqueTest VALUES (1,0)
INSERT INTO UniqueTest VALUES (2,0)
INSERT INTO UniqueTest VALUES (3,0)
INSERT INTO UniqueTest VALUES (4,0)

...because it doesn't violate the constraint.

Edited by - robvolk on 10/09/2002 13:08:32

mdelgado
Posting Yak Master

141 Posts

Posted - 2002-10-09 : 13:15:48

your right, but now I'm confused....

Is the answer to the orignal question

a - UNIQUE CONSTRAINT (on multiple fields)
B - TRIGGER

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-10-09 : 13:23:11

Well, let me ask this: WHY does it NEED to allow nulls for Logon? What is this table used for?

mdelgado
Posting Yak Master

141 Posts

Posted - 2002-10-09 : 13:37:15

All of my shipped data is grouped by LOGON; I keep a table called MT that tellS me the employee name, manager, network id, salary info etc... by LOGON.

so everytime I run reports I joing the two tables by logon, so if the same logon is listed 2x a sum([price]) calculation would return 2x the actual amount. This is why I need to enforce uniqueness on that column.

The problem is that I also use that table to keep track of 'misc.' employees or employeed without a logon and those are obviousely NULL.

I know that the quick solution would be to normalize the table but I've ben doing it this way for so long that this MT table is the heart of over 200+ reports and I'm scared to start changing something that has worked for so long....

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-10-09 : 14:11:11

Then it's the trigger:

CREATE TRIGGER checkDupeLogons ON MT FOR INSERT, UPDATE AS
IF EXISTS(
SELECT logon FROM MT
WHERE logon IS NOT NULL
GROUP BY logon
HAVING Count(*)>1)
BEGIN
ROLLBACK TRANSACTION
RAISERROR('Dupe logon. Operation cancelled.',16,1)
RETURN
END

If the table is pretty large (10,000+ rows) this could have a serious performance degradation if you do a lot of INSERT or UPDATE operations. I'd recommend that if the opportunity to redesign the database comes up, especially if it's a major rewrite anyway, you think about changing this table so that logon is your primary key, and either force a logon for everyone or accommodate Null logons in another table, or something.

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-10-09 : 15:49:06

Isn't the normal way to deal with the desire for a nullable, uniquely constrained column that allows multiple NULLs to:
1) Beat yourself over the head with the ANSI SQL 92 standard until you believe that there is a good reason for only one NULL being allowed.
2) Say 'It's denormalized' and put the column in a brand-new table with the pk of the original table as a foreign key?

Edited by - Arnold Fribble on 10/09/2002 15:51:20

JustinBigelow
SQL Gigolo

1157 Posts

Posted - 2002-10-09 : 18:38:26

quote:

Try it:


CREATE TABLE UniqueTest (
  pk int PRIMARY KEY,
  uc int NULL UNIQUE
)

GO

INSERT INTO UniqueTest VALUES (1, 1)
INSERT INTO UniqueTest VALUES (2, NULL)
INSERT INTO UniqueTest VALUES (3, NULL)

Sloppiness will get me everytime

I should have skipped the read of BOL (which in retrospect I misinterpretted anyway) and tested it before answering.

If you choose not to heed Arnold's words...

quote:

Isn't the normal way to deal with the desire for a nullable, uniquely constrained column that allows multiple NULLs to:
1) Beat yourself over the head with the ANSI SQL 92 standard until you believe that there is a good reason for only one NULL being allowed.
2) Say 'It's denormalized' and put the column in a brand-new table with the pk of the original table as a foreign key?

... here are three methods to enforce uniqueness for all non nulls in a column.
[url]http://support.microsoft.com/default.aspx?scid=kb;en-us;Q322002[/url]

Justin

Have you hugged your SQL Server today?

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2002-10-09 : 18:39:57

quote:

I know that the quick solution would be to normalize the table but I've ben doing it this way for so long that this MT table is the heart of over 200+ reports and I'm scared to start changing something that has worked for so long....

Perhaps you could normalise the tables and then create a view that "simulates" your old structure.

DavidM

"SQL-3 is an abomination.."

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-10-10 : 09:00:53

quote:

... here are three methods to enforce uniqueness for all non nulls in a column.
[url]http://support.microsoft.com/default.aspx?scid=kb;en-us;Q322002[/url]

Excellent stuff!

But strange that it should say "There are three possible solutions that you can use to create an ANSI UNIQUE CONSTRAINT" because my understanding was that ANSI unique constraints for nullable columns are supposed to behave as they do in SQL Server -- only allow one NULL.

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2002-10-14 : 13:13:04

CREATE TABLE UniqueTest
(pk INTEGER NOT NULL PRIMARY KEY,
uc INTEGER,
UNIQUE(pk, uc));

INSERT INTO UniqueTest VALUES (1, 1);
INSERT INTO UniqueTest VALUES (2, NULL);
INSERT INTO UniqueTest VALUES (3, NULL);

--CELKO--
Joe Celko, SQL Guru

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2002-10-14 : 13:20:06

>> ... my understanding was that ANSI unique constraints for nullable columns are supposed to behave as they do in SQL Server -- only allow one NULL. <<

That is correct. The reasoning is that UNIQUE constraints (which means both PRIMARY KEY and UNIQUE) are under the same rules as the GROUP BY. That is, when the COUNT(*) of a group is one, then that group is unique. In the GROUP BY, all NULLs group together, even tho they do not test for equality individually.

This is like one of those wierd "set versus the parts" things you see in math -- summation is not the same as addition, subsets can be the same size as the set, etc. and you learn to live with it.

--CELKO--
Joe Celko, SQL Guru

JoeRS
Starting Member

3 Posts

Posted - 2002-10-16 : 17:53:54

Here is an interesting solution: Normalize your database, then create views which connect to your normalized tables, and name the view the name of the original table, that way, it will connect to what it needs to to do your reports, and you will not have to change any of them. This will eliminate the need for report change, but will give you the flexibility of having a normalized database

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources