ORA-02290: check constraint violated

I keep getting the message “ORA-02290: check constraint violated” whenever I try to insert values into my table.

Here’s the code for the table STORE:

    CREATE TABLE STORE
    (
    StoreID INT PRIMARY KEY,
    StoreName VARCHAR2(30) NOT NULL,
    City VARCHAR2(30) NOT NULL,
    Country VARCHAR2(30) NOT NULL CHECK (Country IN('Singapore','India','United States','Peru','Phillippines','People''s Republic of China','Canada')),
    Phone VARCHAR2(30) NOT NULL,
    Fax VARCHAR2(30),
    Email VARCHAR2(50) UNIQUE,
    Contact VARCHAR2(30) NOT NULL,
    UNIQUE (StoreName, City)
    );

And here’s the INSERT statements I am trying to accomplish:

    INSERT INTO STORE(StoreID, StoreName, City, Country, Phone, Fax, Email, Contact)
    VALUES (1, 'Eastern Sales', 'Singapore', 'Singapore', '65-543-1233', '65-543-1239', '[e-mail]', 'Jeremy');

    INSERT INTO STORE(StoreID, StoreName, City, Country, Phone, Fax, Email, Contact)
    VALUES (2, 'Eastern Treasures', 'Manila', 'Philippines', '63-2-654-2344', '63-2-654-2349', '[e-mail]', 'Gracielle');

    INSERT INTO STORE(StoreID, StoreName, City, Country, Phone, Fax, Email, Contact)   
    VALUES (3, 'Jade Antiques', 'Singapore', 'Singapore', '65-543-3455', '95-543-3459', '[e-mail]', 'Swee Lai');

    INSERT INTO STORE(StoreID, StoreName, City, Country, Phone, Fax, Email, Contact)    
    VALUES (4, 'Andes Treasures', 'Lima', 'Peru', '51-14-765-4566', '51-14-765-4569', '[e-mail]', 'Juan Carlos');

    INSERT INTO STORE(StoreID, StoreName, City, Country, Phone, Fax, Email, Contact)    
    VALUES (5, 'Eastern Sales', 'Hong Kong', 'People''s Republic of China', '852-876-5677', '852-876-5679', '[e-mail]', 'Sam');

    INSERT INTO STORE(StoreID, StoreName, City, Country, Phone, Fax, Email, Contact)
    VALUES (6, 'Eastern Treasures', 'New Delhi', 'India', '91-11-987-6788', '91-11-987-6789', '[e-mail]', 'Deepinder');

    INSERT INTO STORE(StoreID, StoreName, City, Country, Phone, Fax, Email, Contact)    
    VALUES (7, 'European Imports', 'New York City', 'United States', '800-432-8766', '800-432-8769', '[e-mail]', 'Marcello');

It has created the 4th, 6th, and 7th values, but not the rest. What is going wrong?

Answer

You are violating the CHECK constraint as the values allowed doesn’t match with the value you are trying to insert:

CHECK (Country IN(‘Singapore’,’India’,’United States’,’Peru’,’Phillippines’,’People”s Republic of China’,’Canada’))

VALUES (2, ‘Eastern Treasures’, ‘Manila’, ‘Philippines’, ’63-2-654-2344′, ’63-2-654-2349′, ‘[e-mail]’, ‘Gracielle’)

'Phillippines' in the constraint doesn’t match with 'Philippines' in the insert statement.

Correct the country name in the CHECK constraint to 'Philippines' and it will work fine:

CREATE TABLE STORE
    (
    StoreID INT PRIMARY KEY,
    StoreName VARCHAR2(30) NOT NULL,
    City VARCHAR2(30) NOT NULL,
    Country VARCHAR2(30) NOT NULL 
      CHECK (Country IN(
        'Singapore','India','United States','Peru','Philippines','People''s Republic of China','Canada')),
    Phone VARCHAR2(30) NOT NULL,
    Fax VARCHAR2(30),
    Email VARCHAR2(50) UNIQUE,
    Contact VARCHAR2(30) NOT NULL,
    UNIQUE (StoreName, City)
    );