April 27, 2011 at 8:42 am
I would like every record to have Value_Numeric or Value_Character populated but not both. Is there any way to constrain the table in such a way?
CREATE TABLE [dbo].[AttributeValues](
[AttributeID] [int] NOT NULL,
[EntityID] [int] NOT NULL,
[Value_Numeric] [decimal](38, 8) NULL,
[Value_Character] [varchar](max) NULL,
[StartDate] [datetime] NOT NULL,
[EndDate] [datetime] NOT NULL
) ON [PRIMARY]
April 27, 2011 at 8:46 am
Use a CHECK constraint.
April 27, 2011 at 9:01 am
Ok thanks that sent me in the correct direction.
ALTER TABLE [dbo].[AttributeValues]
ADD CONSTRAINT CK_Attributevalue
CHECK
(
(Value_Numeric IS NULL AND Value_Character IS NOT NULL)
OR
(Value_Numeric IS NOT NULL AND Value_Character IS NULL)
)
April 27, 2011 at 9:23 am
I've had to use this kind of table before, and something I've found valuable is a column that indicates which kind of data a given row will hold. You can add that to the check constraint as well. You can also include that column in joins to the table, which enforces data typing in the lookups on it.
- 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
April 27, 2011 at 11:20 am
Thank you GSquared.
I have an 'AttributeDataType' field in my Attributes tables which has a constraint limiting that field to either 'Numeric' or 'Character'.
Is it possible for my 'one or the other' constraint in table Attribute_Values to look to the Attributes table and see whether the particular record is 'Numeric' or 'Character'.
So logically my constraint on the Attribute_Values table would be:
If the related value in Attributes is Numeric then Value_Numeric must be NOT NULL and Value_Character must be NULL or if the related value in Attributes is Character then Value_Numeric must be NULL and Value_Character must be NOT NULL.
Or perhaps I should have a AttributeDataType field in both tables.
Right now I am using a COALESCE statement to choose whether I return Value_Numeric or Value_Character.
CREATE TABLE [dbo].[Attributes](
[AttributeID] [int] IDENTITY(1,1) NOT NULL,
[AttributeCategory] [varchar](50) NOT NULL,
[AttributeName] [varchar](50) NOT NULL,
[AttributeDataType] [varchar](50) NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[Attributes] WITH CHECK ADD CONSTRAINT [CK_Attributes_AttributeDataType] CHECK (([AttributeDataType]='Numeric' OR [AttributeDataType]='Character'))
GO
ALTER TABLE [dbo].[Attributes] CHECK CONSTRAINT [CK_Attributes_AttributeDataType]
CREATE TABLE [dbo].[Attribute_Values](
[AttributeID] [int] NOT NULL,
[EntityID] [int] NOT NULL,
[Value_Numeric] [decimal](38, 8) NULL,
[Value_Character] [varchar](max) NULL,
[StartDate] [datetime] NOT NULL,
[EndDate] [datetime] NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[Attribute_Values] WITH CHECK ADD CONSTRAINT [CK_AttributeValue] CHECK (([Value_Numeric] IS NULL AND [Value_Character] IS NOT NULL OR [Value_Numeric] IS NOT NULL AND [Value_Character] IS NULL))
GO
ALTER TABLE [dbo].[Attribute_Values] CHECK CONSTRAINT [CK_AttributeValue]
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply