Geek asks and answers

Unable to create table in SQL with CHECK constraint

I am trying to create a new table in SQL developer. I need to limit CHECK for Clinic_City, however this 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')))
+3
source share
4 answers

In Oracle, you need to bind the constraint to the corresponding column and give it a name, for example:

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 to create a separate table for cities, insert six rows into it, and link to this table from your table TravelClinics. This will reduce the amount of information that needs to be duplicated. It will also greatly simplify the addition of new cities, as it will be a data operation, not a schema operation.

+1
source

. Clinic_Fax

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

+1

CITY FK. , . FK .

.

0

CREATE TABLE TravelClinics (Clinic number_Number (3) PRIMARY KEY, Clinic_Street varchar2 (20) NOT NULL, Clinic_City varchar2 (10) restriction tc_clinic_city_nn NOT NULL CONSTRAINT tc_clinic_city_CONFIDENT, CLIENT CREDIT "," GLASGO "," 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)

0
source

More articles:


All Articles