sql constraint for checking for two uppercase letters followed by numeric values

Question

i have the following sql check and it accepts values that are supposed to be two uppercase letters followed by 3 numeric values.

---

CREATE TABLE Project(
projectID       NCHAR(5)         NOT NULL       PRIMARY KEY,
                                       CHECK(projectID LIKE '[A-Z][A-Z][0-9][0-9][0-9]'),
projectName   NVARCHAR(20)  NOT NULL        UNIQUE,
budget      MONEY           NOT NULL)

---

how do you make it so that it will only accept uppercase letters followed by 3 numbers for the projectID? ex. it should reject values like 'au123' and accept values like 'AU123'.

Answer 1

Oracle 10g:

SQL> CREATE TABLE CHECK_CHECK (PROJECT_ID NCHAR(5) NOT NULL);

Table created.

SQL> ALTER TABLE CHECK_CHECK ADD CONSTRAINT CHECK_CHECK_CK01
  2   CHECK (REGEXP_LIKE(PROJECT_ID, '[A-Z][A-Z][0-9][0-9][0-9]', 'c'));

Table altered.

SQL> insert into check_check values ('au123');
insert into check_check values ('au123')
*
ERROR at line 1:
ORA-02290: check constraint (MED_AUDIT.CHECK_CHECK_CK01) violated

SQL> insert into check_check values ('AU123');

1 row created.

Answer 2

You need to declare a case-sensitive collation in your CHECK constraint:

Create Table Project    
    (
    ProjectId nchar(5) not null Primary Key
    , ProjectName nvarchar(20) not null Unique
    , Budget money not null
    , Constraint CK_Project 
         Check ( ProjectId Like '[A-Z][A-Z][0-9][0-9][0-9]' Collate Latin1_General_CS_AS )
    )