Cannot Create Table in SQL with CHECK constraint

Question

I am trying to create a new table in SQL developer. I need to have a CHECK constraint on for Clinic_City, however it will not work. Can anybody help me?

CREATE TABLE TravelClinics (Clinic_Number number(3) PRIMARY KEY,
Clinic_Street varchar2(20) NOT NULL,
Clinic_City varchar2(10) NOT NULL,
Clinic_County varchar2(15) NOT NULL,
Clinic_Postcode varchar2(7) NOT NULL,
Clinic_Tel varchar2(11) NOT NULL,
Clinic_Fax varchar2(11) NOT NULL
CONSTRAINT
CHECK(Clinic_City IN ('LONDON', 'BIRMINGHAM', 'MANCHESTER', 'LEEDS', 'GLASGOW', 'EDINBURGH')))

Answer 1

In Oracle, you need to attach the constraint to the proper column, and give it a name, like this:

CREATE TABLE TravelClinics (
    Clinic_Number number(3) PRIMARY KEY
,   Clinic_Street varchar2(20) NOT NULL
,   Clinic_City varchar2(10) NOT NULL
        CONSTRAINT Valid_City
        CHECK(Clinic_City IN ('LONDON', 'BIRMINGHAM', 'MANCHESTER', 'LEEDS', 'GLASGOW', 'EDINBURGH'))
,   Clinic_County varchar2(15) NOT NULL
,   Clinic_Postcode varchar2(7) NOT NULL
,   Clinic_Tel varchar2(11) NOT NULL
,   Clinic_Fax varchar2(11) NOT NULL
)

Demo on sqlfiddle.

A better approach would be creating a separate table for cities, inserting six rows into it, and referencing that table from your TravelClinics table. This would reduce the amount of information that needs to be duplicated. It would also immensely simplify adding new cities, because it would be a data operation, rather than a schema operation.

Answer 2

Try this. You are missing comma after defining Clinic_Fax column

CREATE TABLE TravelClinics (Clinic_Number number(3) PRIMARY KEY,
Clinic_Street varchar2(20) NOT NULL,
Clinic_City varchar2(10) NOT NULL,
Clinic_County varchar2(15) NOT NULL,
Clinic_Postcode varchar2(7) NOT NULL,
Clinic_Tel varchar2(11) NOT NULL,
Clinic_Fax varchar2(11) NOT NULL,
CONSTRAINT pk CHECK(Clinic_City IN ('LONDON', 'BIRMINGHAM', 'MANCHESTER', 'LEEDS', 'GLASGOW', 'EDINBURGH')))

SQL Fiddle Demo