How to create conditional constraint?

Using MS SQL

Given a table roughly like this (Not really ice cream related):

CREATE TABLE icecream( id int IDENTITY(1,1) NOT NULL, vendor_id int NOT NULL, name VARCHAR(20) NOT NULL, flavor VARCHAR(20) NOT NULL, terminated_date DATETIME NULL ) 

I’ve set a unique constraint on vendor_id and name so that a vendor can’t have multiple icecream with the same name. However, i realized that I need a way to update these entries while retaining historical records in case the flavor changes. So I was thinking of using a terminated_date to keep the historical records.

Is there a way to have my constraint be UNIQUE(vendor_id,name) IF terminated_date is null?

submitted by /u/The_Cream_Man
[link] [comments]


Go to Source of this post
Author Of this post: /u/The_Cream_Man
Title Of post: How to create conditional constraint?
Author Link: {authorlink}