CODEWITHSUNDEEP
sqloracle-databaseoracle-sqldeveloper
Alex Anderson
Alex Anderson

Reputation: 153

Cannot Create Table in SQL with CHECK constraint

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')))

Upvotes: 3

Views: 153

Answers (4)

sanjay Poshiya
sanjay Poshiya

Reputation: 1

CREATE TABLE TravelClinics (Clinic_Number number(3) PRIMARY KEY, Clinic_Street varchar2(20) NOT NULL, Clinic_City varchar2(10) constraint tc_clinic_city_nn NOT NULL CONSTRAINT tc_clinic_city_ck 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)

Upvotes: 0

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726479

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.

Upvotes: 1

Mudassir Hasan
Mudassir Hasan

Reputation: 28741

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

Upvotes: 1

jean
jean

Reputation: 4350

Instead create a domain table CITY and a FK to it. If anytime you want to add a city just add a row. The FK ill act as a constraint.

It also adds the advantage of normalization.

Upvotes: 0

Related Questions