Adding Constraint based on two columns

Question

Adding Constraint based on two columns

KVK4YOU

Valued Member

Points: 64
More actions
March 14, 2024 at 3:50 am

#4394972

Hello,
I have a table which has two columns such as Type and Date. If I enter "Apple" in Type I have to restrict the user to enter date > 01/01/2020 or if they enter any other value for Type, I don't need to restrict the value for Date. If they don't enter any date the value should default to 12-31-2199.
How can we achieve this in constraint as two columns are involved.
ALTER TABLE [Testing]
ADD CONSTRAINT CHK_Dates CHECK (([Type] = 'Apple' AND [Date] >= '01-01-2020') OR [Type] LIKE '%%' )
ALTER TABLE [Testing]
ADD CONSTRAINT DF_Dates DEFAULT '12-31-2199' FOR [Date]
The above constraint is not working as expected. Please share your thoughts.

Thanks in advance
March 19, 2024 at 2:35 pm

This was removed by the editor as SPAM

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Emperor100 SSCrazy Points: 2173 More actions · Answer 1

Your constraint should be something like below, drop the existing one on the table and create it as the following.

ADD CONSTRAINT CHK_Dates CHECK 
(
    ([Type] = 'Apple' AND [Date] >= '01-01-2020') OR 
    ([Type] <> 'Apple' AND [Date] between '1-1-1900' and '12-31-2199')
)

Change the dates that fulfills your requirement in the between clause.

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

Emperor100 SSCrazy Points: 2173 More actions · Answer 2

deleted the duplicate post.

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

ratbak Ten Centuries Points: 1358 More actions · Answer 3

And if you truly don't care about the date entered when Type is not "Apple", you can simplify Emperor100's example to

ALTER TABLE [Testing]
ADD CONSTRAINT CHK_Dates CHECK 
(
    ([Type] = 'Apple' AND [Date] >= '01-01-2020') OR 
    ([Type] <> 'Apple')
)

KVK4YOU Valued Member Points: 64 More actions · Answer 4

Thank you.

How to add default constraint with this one? I want CHK_Dates constraint as well if someone enters other than apple and if they don't enter [Date]. It must default to '12-31-2199'.

KVK4YOU Valued Member Points: 64 More actions · Answer 5

Thank you.

How to add default constraint with this one? I want CHK_Dates constraint as well if someone enters other than apple and if they don't enter [Date]. It must default to '12-31-2199'.