Database Table Constraint using LIKE operator

Question

I am trying to setup constraints on my database table using like operator. Is this possible in Azure SQL Server?

I have a column FILE_NAME that has for example 'VID' as a common pattern in most of the records. Then, I have another column FILE_TYPE where I want to setup constraint on so that only possible values can be inserted.

Table Definition:

CREATE TABLE dbo.CUST_LIBRARY
(
 FILE_NAME VARCHAR(20),
 FILE_TYPE VARCHAR(10)
);

Here is how my data looks like:

FILE_NAME

---

VID_GEO_1 |

IMG-ART_1 |

TER-VID_6 |

FIL-PAR_1 |

Now I want to setup a constraint on Column FILE_TYPE where we can only insert values 'MP4', 'AVI' if the FILE_NAME has 'VID' in it. Otherwise, the remaining records should always be defaulted to 'NA' and nothing else should be inserted.

Answer 1

You want a check constraint:

CREATE TABLE dbo.CUST_LIBRARY (
  FILE_NAME VARCHAR(20),
  FILE_TYPE VARCHAR(10),
  CONSTRAINT CHK_CUST_LIBRARY 
      CHECK ( (FILE_TYPE IN ('MP4', 'AVI') AND (FILE_NAME LIKE '%VID%')) OR
              FILE_TYPE = 'NA'
            );