How to CREATE TABLE with formatted column

问题:

I need to create a new empty table, where each new value should be validated by server using format of its column. Like, ###-###-##, where each # is a digit from 0 to 9, and it should contain those - symbols. How?

回答1:

Use CHECK Constraint:

CREATE TABLE YourTable ( 
    Col1 VARCHAR(10),
    CONSTRAINT MyContraint CHECK (Col1 LIKE '[0-9][0-9][0-9]-[0-9][0-9][0-9]-[0-9][0-9]')
                );

回答2:

No. Don't do that! That's not going to work.

If you have multi-tenant application and different customers would like to store the value in different format, you will be trapped.

Best way is to just validate the pattern on client side, and store in db in free form.