August 4, 2010 at 1:29 pm
I have a function that returns true / false. If the function returns true, the constraint needs to check the value in a specific column of the record. Its essentially an if [true] then [require this]
I'm thinking something like this but I'm getting an error that it doesn't validate.
(CASE IsNull(txtTrailerSealNumber,0) When 0 Then 0 Else -1 END) = fnTrailerDOTNumberRequired(intUsageTypeId)
1) Is this possible at all?
2) Where would I find some examples to build off of?
August 4, 2010 at 8:42 pm
It was my first attempt at a CONSTRAINT. I'm well known for taking baby steps in my technique and code. If I were more experience, then I'd be bothered, but I don't so I'm not. I'm on the leading edge of the learning curve and fully expect others with more experience to recognize that lack of finesse.
The specific scenario is that a value for a column is required based on the value in a related table. Taking the foreign key going out to the other table and get the value stored in specific column in that table.
Here's the definition of the child table. The db was upsized from Access hence the prefixes. The constraint needs to look at the value of intUsageTypeId in the parent, reach out to the child and determine the value of ynExclusiveUse. If ynExclusiveUse is False, no value is required, if not then it is.
CREATE TABLE [dbo].[USystblTrailerUsageTypes](
[intUsageTypeId] [tinyint] IDENTITY(1,1) NOT NULL,
[txtUsageDescriptionLong] [nvarchar](25) NOT NULL,
[ynExclusiveUse] [bit] NOT NULL,
[txtUsageDescriptionShort] [nvarchar](7) NOT NULL,
CONSTRAINT [PK_USystblTrailerUsageTypes] PRIMARY KEY CLUSTERED
(
[intUsageTypeId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply