Table constraint to check for unique value

Table constraint to check for unique value

Post by Chris Edwar » Wed, 05 Feb 2003 12:09:51



Given the following table:

CREATE TABLE [dbo].[tblOPCode] (
        [pkOpCodeID] [int] IDENTITY (1, 1) NOT NULL ,
        [OpCode] [int] NOT NULL ,
        [ItemNumber] [char] (10) NULL
)

is it possible (in SQL Server 2000) to write a table constraint that
would ensure that ItemNumber is unique for all rows where OpCode =
200?  When OpCode is <> 200, I don't care what ends up in ItemNumber.

Or is this a normalization issue?

TIA
Chris

 
 
 

Table constraint to check for unique value

Post by BP Margoli » Wed, 05 Feb 2003 12:59:07


Chris,

Try the following:

create function dbo.Unique_OPCode_200 ( )
returns int
as
begin
  return (select case when count(distinct OpCode) = count(OpCode)
                   then 1 else 0
                 end
             from dbo.tblOPCode
             where OpCode = 200)
end
go

CREATE TABLE [dbo].[tblOPCode] (
[pkOpCodeID] [int] IDENTITY (1, 1) NOT NULL ,
[OpCode] [int] NOT NULL ,
[ItemNumber] [char] (10) NULL,
  check (1 = dbo.Unique_OPCode_200( ))
)
go

insert into dbo.tblOPCode values (1, 'abc')
insert into dbo.tblOPCode values (1, 'abc')
insert into dbo.tblOPCode values (200, 'abc')
insert into dbo.tblOPCode values (200, 'abc')

select * from dbo.tblOPCode

I do not usually recommend using user-defined functions in CHECK constraints
because SQL Server 2000 has some bugs when doing so, but I **think** that
you might be safe.

Nope ... this won't work. See the following counter-example:

update dbo.tblOPCode
  set OpCode = 200
where pkOpCodeID = 1
go

select * from dbo.tblOPCode
go

I believe that you will have to resort to a trigger rather than a CHECK
constraint    ;-(

-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.


Quote:> Given the following table:

> CREATE TABLE [dbo].[tblOPCode] (
> [pkOpCodeID] [int] IDENTITY (1, 1) NOT NULL ,
> [OpCode] [int] NOT NULL ,
> [ItemNumber] [char] (10) NULL
> )

> is it possible (in SQL Server 2000) to write a table constraint that
> would ensure that ItemNumber is unique for all rows where OpCode =
> 200?  When OpCode is <> 200, I don't care what ends up in ItemNumber.

> Or is this a normalization issue?

> TIA
> Chris


 
 
 

Table constraint to check for unique value

Post by Dan Guzma » Wed, 05 Feb 2003 13:01:32


One method is to create an indexed view on the table like the example
below.  Alternatively, you can create an insert/update trigger and
rollback the transaction when dups are detected.

SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
SET NUMERIC_ROUNDABORT OFF
GO

CREATE TABLE [dbo].[tblOPCode] (
[pkOpCodeID] [int] IDENTITY (1, 1) NOT NULL PRIMARY KEY,
[OpCode] [int] NOT NULL ,
[ItemNumber] [char] (10) NULL
)
GO

CREATE VIEW uv_tblOPCode
WITH SCHEMABINDING
AS
SELECT ItemNumber
FROM dbo.tblOpCode
WHERE OpCode = 200 AND
    ItemNumber IS NOT NULL
GO

CREATE UNIQUE CLUSTERED INDEX uv_tblOPCode_cdx
ON uv_tblOPCode(ItemNumber)
GO

INSERT INTO tblOpCode VALUES(199, '1')
INSERT INTO tblOpCode VALUES(199, '1')
INSERT INTO tblOpCode VALUES(200, '1')
INSERT INTO tblOpCode VALUES(200, '1')
GO

--
Hope this helps.

Dan Guzman
SQL Server MVP

-----------------------
SQL FAQ links (courtesy  Neil Pike):

http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
-----------------------


Quote:> Given the following table:

> CREATE TABLE [dbo].[tblOPCode] (
> [pkOpCodeID] [int] IDENTITY (1, 1) NOT NULL ,
> [OpCode] [int] NOT NULL ,
> [ItemNumber] [char] (10) NULL
> )

> is it possible (in SQL Server 2000) to write a table constraint that
> would ensure that ItemNumber is unique for all rows where OpCode =
> 200?  When OpCode is <> 200, I don't care what ends up in ItemNumber.

> Or is this a normalization issue?

> TIA
> Chris

 
 
 

Table constraint to check for unique value

Post by Steve Kas » Wed, 05 Feb 2003 14:10:18


BP,

  These almost never work.  The problem is that they are
checked for each row, destroying atomicity.  The way to
break them is with a multirow update or insert where the
constraint is false at some point midway through the query
(something one shouldn't ever have to talk about).

Try this after your inserts, and if I understood the question,
it will show the problem.

update dbo.tblOPCode set
  OpCode = 201-OpCode
where pkOpCodeID > 1
go

select * from dbo.tblOPCode
alter table dbo.tblOPCode nocheck constraint all
go

update dbo.tblOPCode set
  OpCode = 201-OpCode
where pkOpCodeID > 1
go

select * from dbo.tblOPCode

These may be successful if only single-row updates and
inserts are allowed (and the constraint holds after each one).

Steve Kass
Drew University


>Chris,

>Try the following:

>create function dbo.Unique_OPCode_200 ( )
>returns int
>as
>begin
>  return (select case when count(distinct OpCode) = count(OpCode)
>                   then 1 else 0
>                 end
>             from dbo.tblOPCode
>             where OpCode = 200)
>end
>go

>CREATE TABLE [dbo].[tblOPCode] (
>[pkOpCodeID] [int] IDENTITY (1, 1) NOT NULL ,
>[OpCode] [int] NOT NULL ,
>[ItemNumber] [char] (10) NULL,
>  check (1 = dbo.Unique_OPCode_200( ))
>)
>go

>insert into dbo.tblOPCode values (1, 'abc')
>insert into dbo.tblOPCode values (1, 'abc')
>insert into dbo.tblOPCode values (200, 'abc')
>insert into dbo.tblOPCode values (200, 'abc')

>select * from dbo.tblOPCode

>I do not usually recommend using user-defined functions in CHECK constraints
>because SQL Server 2000 has some bugs when doing so, but I **think** that
>you might be safe.

>Nope ... this won't work. See the following counter-example:

>update dbo.tblOPCode
>  set OpCode = 200
>where pkOpCodeID = 1
>go

>select * from dbo.tblOPCode
>go

>I believe that you will have to resort to a trigger rather than a CHECK
>constraint    ;-(

>-------------------------------------------
>BP Margolin
>Please reply only to the newsgroups.
>When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
>can be cut and pasted into Query Analyzer is appreciated.



>>Given the following table:

>>CREATE TABLE [dbo].[tblOPCode] (
>>[pkOpCodeID] [int] IDENTITY (1, 1) NOT NULL ,
>>[OpCode] [int] NOT NULL ,
>>[ItemNumber] [char] (10) NULL
>>)

>>is it possible (in SQL Server 2000) to write a table constraint that
>>would ensure that ItemNumber is unique for all rows where OpCode =
>>200?  When OpCode is <> 200, I don't care what ends up in ItemNumber.

>>Or is this a normalization issue?

>>TIA
>>Chris

 
 
 

1. Change table check constraint to column check constraint?

After our SQL 6.5 database was wiz'd into SQL 7.0 format, I noticed that many
(but not all) of the column check constraints were converted to table check
constraints.  These really are simple data-entry checks, and logically belong
with the column.

Is there a way to convert them back to column check constraints without
having to recreate the table?  I can't find any syntax in ALTER TABLE to add
a column check constraint.

Why do I care?  It's either change the ERWin schema or fix the database, and
frankly, I think ERWin is right this time.

TIA
--
Bobb Head

Sent via Deja.com http://www.deja.com/
Before you buy.

2. shrinkdatabase doesn't work

3. Column check constraint vs table check constraint

4. Is there a Database Out there for me ... !

5. pguniqchk -- checks uniqueness of unique constraints on tables

6. ODBC connection to msaccess from vfp 6.0

7. Problem with Alter Table Constraint NO CHECK/CHECK?

8. DBList Control - selecting item & deleting

9. How to check for unique constraints?

10. Fixes for checking unique constraints on RI creation

11. unique constraints with null values

12. PK constraint vs Index with Unique values

13. Multiple NULL Values in Unique Constraint/Index