Constraint question
-
I have a table like below. CREATE TABLE [dbo].[BatchProcessRule]( [BatchProcessRuleId] [int] IDENTITY(1,1) NOT NULL, [EffectiveDate] [datetime] NOT NULL, [EndDate] [datetime] NULL, [LastModifiedBy] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [LastModifiedOn] [datetime] NOT NULL) How can I create a constraint so that only one row may exists with a null EndDate? I tried using the constraint text (select count (*) from BatchProcessRuleDetail where EndDate is null ) > 1 but I got an error saying subqueries are not allowed. Any thought?
I didn't get any requirements for the signature
-
I have a table like below. CREATE TABLE [dbo].[BatchProcessRule]( [BatchProcessRuleId] [int] IDENTITY(1,1) NOT NULL, [EffectiveDate] [datetime] NOT NULL, [EndDate] [datetime] NULL, [LastModifiedBy] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [LastModifiedOn] [datetime] NOT NULL) How can I create a constraint so that only one row may exists with a null EndDate? I tried using the constraint text (select count (*) from BatchProcessRuleDetail where EndDate is null ) > 1 but I got an error saying subqueries are not allowed. Any thought?
I didn't get any requirements for the signature
How about this ?
CREATE TABLE [dbo].[BatchProcessRule](
[BatchProcessRuleId] [int] IDENTITY(1,1) NOT NULL,
[EffectiveDate] [datetime] NOT NULL,
[EndDate] [datetime] CONSTRAINT BatchProcessRule_UNIQUE UNIQUE,
[LastModifiedBy] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[LastModifiedOn] [datetime] NOT NULL)Making a column unique means it would take its value NULL but only once.:thumbsup:
Abhishek Sur
My Latest Articles **Create CLR objects in SQL Server 2005 C# Uncommon Keywords Read/Write Excel using OleDB
**Don't forget to click "Good Answer" if you like to.
-
How about this ?
CREATE TABLE [dbo].[BatchProcessRule](
[BatchProcessRuleId] [int] IDENTITY(1,1) NOT NULL,
[EffectiveDate] [datetime] NOT NULL,
[EndDate] [datetime] CONSTRAINT BatchProcessRule_UNIQUE UNIQUE,
[LastModifiedBy] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[LastModifiedOn] [datetime] NOT NULL)Making a column unique means it would take its value NULL but only once.:thumbsup:
Abhishek Sur
My Latest Articles **Create CLR objects in SQL Server 2005 C# Uncommon Keywords Read/Write Excel using OleDB
**Don't forget to click "Good Answer" if you like to.
That will only work if the EndDates are unique.
-
That will only work if the EndDates are unique.
Isnt that what he wanted ?:confused:
Abhishek Sur
My Latest Articles **Create CLR objects in SQL Server 2005 C# Uncommon Keywords Read/Write Excel using OleDB
**Don't forget to click "Good Answer" if you like to.
-
Isnt that what he wanted ?:confused:
Abhishek Sur
My Latest Articles **Create CLR objects in SQL Server 2005 C# Uncommon Keywords Read/Write Excel using OleDB
**Don't forget to click "Good Answer" if you like to.
-
I have a table like below. CREATE TABLE [dbo].[BatchProcessRule]( [BatchProcessRuleId] [int] IDENTITY(1,1) NOT NULL, [EffectiveDate] [datetime] NOT NULL, [EndDate] [datetime] NULL, [LastModifiedBy] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [LastModifiedOn] [datetime] NOT NULL) How can I create a constraint so that only one row may exists with a null EndDate? I tried using the constraint text (select count (*) from BatchProcessRuleDetail where EndDate is null ) > 1 but I got an error saying subqueries are not allowed. Any thought?
I didn't get any requirements for the signature
I don't think you can do this via a constraint. You might be able to do this with a trigger that runs on insert/update to check the date and raise an error if it violates your rule. Another possibility might be to restrict update and insert permissions to the table so that the only way to update/insert is via a stored procedure which does the check for null dates and raises an error accordingly.
-
I don't think you can do this via a constraint. You might be able to do this with a trigger that runs on insert/update to check the date and raise an error if it violates your rule. Another possibility might be to restrict update and insert permissions to the table so that the only way to update/insert is via a stored procedure which does the check for null dates and raises an error accordingly.
I am only using a stored proc to do the update, so I'm just going to leave it that way. I just like to set up all my relationships / constraints before I start developing. I can recall at least 3 or 4 times where database rules have prevented small bugs from causing major headaches.
I didn't get any requirements for the signature
-
I have a table like below. CREATE TABLE [dbo].[BatchProcessRule]( [BatchProcessRuleId] [int] IDENTITY(1,1) NOT NULL, [EffectiveDate] [datetime] NOT NULL, [EndDate] [datetime] NULL, [LastModifiedBy] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [LastModifiedOn] [datetime] NOT NULL) How can I create a constraint so that only one row may exists with a null EndDate? I tried using the constraint text (select count (*) from BatchProcessRuleDetail where EndDate is null ) > 1 but I got an error saying subqueries are not allowed. Any thought?
I didn't get any requirements for the signature
>MyAARPMedicare is one of the most popular medical insurance plans in the United States. The main reason for the popularity of MyAARPMedicare plans is its service quality and the rewards/benefits that customers get when they enroll for a Medicare insurance plan under AARP.