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.

I assume you mean that the type of the remaining records is `'NA'`, if the first condition is not met. — Gordon Linoff, Nov 09 '20 at 23:02

Gordon Linoff · Accepted Answer · 2020-11-10T00:48:00.010

3

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'
            );

edited Nov 10 '20 at 00:48

answered Nov 09 '20 at 23:02

Gordon Linoff

1,242,037
58
646
786

Does Azure SQL not require the ```CHECK``` keyword for LOV constraints? – alexherm Nov 09 '20 at 23:27

Database Table Constraint using LIKE operator

1 Answers1