Constraint for only certain values
-
Hello,
I am trying to place a certain constraint on a table of mine and have hit that awesome wall.
I have a table that would look something like this
CREATE TABLE [dbo].[MyTable]([DomainID] [int] IDENTITY(1,1) NOT NULL,
[AffiliateID] [int] NOT NULL,
[DomainName] [varchar](100) NOT NULL,
[IsActive] [bit] NOT NULL,
[CreatedDate] [datetime] NOT NULL,
I am trying to put a constraint on this table so that I only have 1 DomainName that is active at a time.
so it would be like this:
samedomain.com 1
samedomain.com 0
samedomain.com 0
Any takers on how to do this?
Things I thought about:
* using a trigger to automatically update the other rows when one is set to active
* just making a dynamic active table
-
CREATE VIEW dbo.ActiveDomainNames
WITH SCHEMABINDING
AS
SELECT DomainID,AffiliateID,DomainName,CreatedDate
FROM dbo.MyTable
WHERE IsActive=1
CREATE UNIQUE CLUSTERED INDEX ix ON dbo.ActiveDomainNames(DomainName)
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Mark-101232 (10/2/2009)
CREATE VIEW dbo.ActiveDomainNames
WITH SCHEMABINDING
AS
SELECT DomainID,AffiliateID,DomainName,CreatedDate
FROM dbo.MyTable
WHERE IsActive=1
CREATE UNIQUE CLUSTERED INDEX ix ON dbo.ActiveDomainNames(DomainName)
I see the idea (very neat), but can't figure out how that would limit it to a single active domain.
Shouldn't it be CREATE UNIQUE CLUSTERED INDEX ix ON dbo.ActiveDomainNames(IsActive)
(after adding IsActive to the view as well)?
-
I'd go with a separate table that stores the active domain value. That would also allow you to track the history of it if you wanted to include a DateEffective type column.
But it's easy enough to have an Instead of Insert trigger on the MyTableActiveDomain table that would prevent a second row from ever showing up in that table.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
-
Ian Scarlett (10/2/2009)
Mark-101232 (10/2/2009)
CREATE VIEW dbo.ActiveDomainNames
WITH SCHEMABINDING
AS
SELECT DomainID,AffiliateID,DomainName,CreatedDate
FROM dbo.MyTable
WHERE IsActive=1
CREATE UNIQUE CLUSTERED INDEX ix ON dbo.ActiveDomainNames(DomainName)
I see the idea (very neat), but can't figure out how that would limit it to a single active domain.
Shouldn't it be CREATE UNIQUE CLUSTERED INDEX ix ON dbo.ActiveDomainNames(IsActive)
(after adding IsActive to the view as well)?
Um, no. The view only selects rows with an active domain, IsActive is a fixed value for these.
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537 -
Um, no. The view only selects rows with an active domain, IsActive is a fixed value for these.
I must be having a "senior moment", but I still can't work it out.
If the view selects only those that have IsActive = 1 from the base table, how does that stop >1 row having the IsActive flag set. The unique index on the view is on DomainName, so surely that allows >1 row to have the flag set, as long as it has a different domain name.
EDIT:
Doh. I was having a "senior moment".
I though the OP was looking to have only 1 row with IsActive = 1, regardless of domain name.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply