Constraint on a column - SQL Server Forums

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

SQL Server Administration (2000)

Constraint on a column

Author

Topic

CanadaDBA

583 Posts

Posted - 2005-01-06 : 12:45:10

One of our developers is asking me to create a table with two columns: ID (Int), and IsActive (Bit). He wants IsActive to be null for all rows and only one row is set (means IsActive = 1 for that row). He is asking for a constraint on the table that it cannot set IsActive to 1 if there is another raw set. He wants to be sure that at a given time only one row is set.

I am thinking of a Trigger. Is there any other solution?

Canada DBA

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-01-06 : 13:21:09

maybe this?

update MyTable
set IsActive = case when id = @id then 1 else null end

Go with the flow & have fun! Else fight the flow

CanadaDBA

583 Posts

Posted - 2005-01-07 : 08:56:37

Actually, his design is like this:


ID           WaiverCode       IsActive
01           01               Null
02           02               Null
03           08               1
04           10               Null

So, you believe I should use triggers and probably use the script, right?

quote:
Originally posted by spirit1

maybe this?

update MyTable
set IsActive = case when id = @id then 1 else null end

Go with the flow & have fun! Else fight the flow

Canada DBA

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-01-07 : 09:49:41

use the script inside the trigger. that should do it.
maybe someone has a better idea...

Go with the flow & have fun! Else fight the flow

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-01-07 : 17:24:39

I think you would be better off with moving the Active Indicator to another table that references the WaiverCode table. The new table should be contrained to allow only a single row. With a foreign key reference it would be impossible to have more than on active row, and you would not be able to delete the active trow. It is a much cleaner design if you are allowed to have only one active row.

See the code example:

CREATE TABLE dbo.WaiverCode (
WaiverCodeID int IDENTITY (1, 1) NOT NULL ,
WaiverCode int NOT NULL
)
GO

CREATE TABLE dbo.ActiveWaiverCode (
ActiveWaiverCodePK int NOT NULL ,
WaiverCodeID int NOT NULL
)
GO

ALTER TABLE dbo.ActiveWaiverCode WITH NOCHECK ADD
PRIMARY KEY CLUSTERED ( ActiveWaiverCodePK )
GO

ALTER TABLE dbo.WaiverCode WITH NOCHECK ADD
PRIMARY KEY CLUSTERED ( WaiverCodeID )
GO

ALTER TABLE dbo.ActiveWaiverCode ADD
CONSTRAINT CK_ActiveWaiverCode_ActiveWaiverCodePK_Is_1
CHECK (ActiveWaiverCodePK = 1)
GO

CREATE INDEX IX_ActiveWaiverCode_WaiverCodeID
ON dbo.ActiveWaiverCode(WaiverCodeID)
GO

ALTER TABLE dbo.WaiverCode ADD
CONSTRAINT AK_WaiverCode UNIQUE NONCLUSTERED
( WaiverCode )
GO

ALTER TABLE dbo.ActiveWaiverCode ADD
CONSTRAINT FK_ActiveWaiverCode_WaiverCode FOREIGN KEY
( WaiverCodeID )
REFERENCES dbo.WaiverCode ( WaiverCodeID )
GO

quote:
Originally posted by farhadr

One of our developers is asking me to create a table with two columns: ID (Int), and IsActive (Bit). He wants IsActive to be null for all rows and only one row is set (means IsActive = 1 for that row). He is asking for a constraint on the table that it cannot set IsActive to 1 if there is another raw set. He wants to be sure that at a given time only one row is set.

I am thinking of a Trigger. Is there any other solution?

Canada DBA

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2005-01-07 : 19:23:06

Michael,

How are you proposing to limit the new table to a single row? If it's via a trigger on the new table then I what have we really gained?

Farhadr,

A small point... Why allow NULLs for the non-IsActive rows? We know the value to use if the row is active (1) or inactive (0).

HTH

=================================================================

A faith that cannot survive collision with the truth is not worth many regrets. -Arthur C Clarke, science fiction writer (1917- )

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-01-07 : 21:24:04

Bustaz Kool,

The constraint to limit the new table to a single row is there in the code I supplied. I guess I should have explained it.

Constraint CK_ActiveWaiverCode_ActiveWaiverCodePK_Is_1 constrains the value of column ActiveWaiverCodePK in table ActiveWaiverCode to a value of 1. Since ActiveWaiverCodePK is the primary key, it limits the table to a single row.

Michael

ALTER TABLE dbo.ActiveWaiverCode WITH NOCHECK ADD
PRIMARY KEY CLUSTERED ( ActiveWaiverCodePK )
GO
-- Limit table to a single row by limiting Primay Key to a value of 1
ALTER TABLE dbo.ActiveWaiverCode ADD
CONSTRAINT CK_ActiveWaiverCode_ActiveWaiverCodePK_Is_1
CHECK (ActiveWaiverCodePK = 1)

quote:
Originally posted by Bustaz Kool

Michael,

How are you proposing to limit the new table to a single row? If it's via a trigger on the new table then I what have we really gained?

Farhadr,

A small point... Why allow NULLs for the non-IsActive rows? We know the value to use if the row is active (1) or inactive (0).

HTH

=================================================================

A faith that cannot survive collision with the truth is not worth many regrets. -Arthur C Clarke, science fiction writer (1917- )

CanadaDBA

583 Posts

Posted - 2005-01-10 : 14:41:57

Michael, it is cool! Good point of view and very smart coding. Thanks

Canada DBA

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2005-01-10 : 14:53:53

I like Michael's solution, but if you have to keep the same structure add a computed column and a constraint...

ComputedConstraint AS CASE WHEN IsActive IS NULL THEN CAST(ID AS VARCHAR(20)) ELSE IsActive END, UNIQUE (ComputedConstraint))

Oh yeah and another CHECK constraint: IsActive != 0

Here's a link to the basic idea of the computed constraint...
http://weblogs.sqlteam.com/davidm/archive/2004/05/21/1364.aspx

DavidM

"Always pre-heat the oven"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources