Styjun

I am using Oracle SQL.
Here is the table rental, and the CC_Type column means the credit card type. After creating the table, I want to add a constraint to make sure the credit card is either 'credit' or 'debit'

 CREATE TABLE rental
( Rental_Num VARCHAR2(5) CONSTRAINT rental_PK PRIMARY KEY,
 Rent_Date DATE DEFAULT SYSDATE,
 Credit_Card_Num CHAR(16),
 CC_Type CHAR(7),
 Member_ID VARCHAR2(5)
);

Therefore, I try to write:

ALTER TABLE RENTAL
 2 ADD CONSTRAINT CC_TYPE_CK
 3 CHECK(CC_TYPE LIKE 'Credit' OR CC_TYPE LIKE 'Debit');

But SQL yield: cannot validate (SYSTEM.CC_TYPE_CK) - check constraint violated

I don't understand it violated what? And how to fix it?
Thanks!!

You apparently have data in the table that violates the constraint. Your code basically works; here is a db<>fiddle (this fixes the extra comma before the closing paren in the create table statement).

So, look for the bad data:

select r.*
from rental r
where r.cc_type not in ('Credit', 'Debit');

I would also write the constraint using in . . . it is simpler:

ALTER TABLE RENTAL
 ADD CONSTRAINT CC_TYPE_CK
 CHECK (CC_TYPE IN ('Credit', 'Debit'));

just remove , after Member_ID VARCHAR2(5) other wise your query working fine

CREATE TABLE rental
 ( Rental_Num VARCHAR2(5) CONSTRAINT rental_PK PRIMARY KEY,
 Rent_Date DATE DEFAULT SYSDATE,
 Credit_Card_Num CHAR(16),
 CC_Type CHAR(7),
 Member_ID VARCHAR2(5)

 );

 ALTER TABLE RENTAL
 ADD CONSTRAINT CC_TYPE_CK
 CHECK(CC_TYPE LIKE 'Credit' OR CC_TYPE LIKE 'Debit');

demo link

I agree with Gordon Linoff, Check for the data in cc_type.

select Distinct r.cc_type
from rental r;

If you still want to create the constraint even when you have the other CC_TYPEs(in the other words, without validating the existing data) then try this

ALTER TABLE RENTAL
 ADD CONSTRAINT CC_TYPE_CK
 CHECK (CC_TYPE IN ('Credit', 'Debit')) NOVALIDATE;