i've the next sql check also it accepts values that are meant to be two uppercase letters then 3 number 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 can you allow it to be to ensure that it'll only accept uppercase letters then 3 amounts for that projectID? ex. it will reject values like 'au123' and accept values like 'AU123'.

You have to declare a situation-sensitive collation inside 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 )
    )

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.