CODEWITHSUNDEEP
sqloracle-databaseconstraints
JD87
JD87

Reputation: 123

SQL (oracle) Check Constraint difficulty - Not sure how to implement

I am creating a small database for a telecom system.

One of the tables (calls) requires that a if a phone number's area code is not contained in a predefined list, then the number should not be added to the table.

The way I have thought about doing this is to put a check constraint within the calls table to not accept numbers that are not a part of this mentioned list. However, this list is quite long and I am not too sure if there would be a better implementation method.

Here is the list:

01 or 02: local/national number. Ex.: 01612 338866.
075, 077, 078, 079: mobile phone number. Ex.: 07747 556647.
0800: free number. Ex.: 08002 223344.
0845, 0870: special service. Ex.: 08451 423456.
08442 to 08449: 5p special service. Ex.: 08444 404404.
08712 to 08719: 10p special service. Ex.: 08713 457893.
090, 091, 098: premium rate special service. Ex.: 09119 229595.

The only way I could think of to do this is as follows:

ALTER TABLE calls ADD (CONSTRAINT area_ck
 CHECK area_code ("01" or "02" or "075" or "077" or "078" or  "079" or "0800" or 
        "0845" or "0870" or (BETWEEN ("08442" AND "08449")) or 
        (BETWEEN ("08712" AND "08719")) or 
        "090" or "091" or "098")
      ) ;

My two main issues with this are:

  1. It gives an error as it is implemented incorrectly
  2. If I were to modify it slightly until it did work, would it still be a long way about trying to solve my task?

Upvotes: 2

Views: 588

Answers (3)

rics
rics

Reputation: 5596

You could either write area_code='01' OR area_code='02' ... or you can use area_code in ('01','02', ...). You also need to add area_code before between keywords.

But I would suggest you to store the area codes in a table instead of the check constraint and use the area codes as foreign keys. This way the list of area codes can easily be modified.

Upvotes: 0

Amir Pashazadeh
Amir Pashazadeh

Reputation: 7322

The first problem is about using = and between together. Do it like:

area_code in ('01', '02', '03') or area_code between ('1000' and '1500') or ....

Upvotes: 0

Justin Cave
Justin Cave

Reputation: 231821

The more common approach would be to define a table of valid area codes

CREATE TABLE area_code (
  area_code VARCHAR2(5) PRIMARY KEY
);

Fill the Area_Code table with the set of valid values

INSERT INTO area_code( area_code ) VALUES( '01' );
INSERT INTO area_code( area_code ) VALUES( '02' );
INSERT INTO area_code( area_code ) VALUES( '075' );
...

or

BEGIN
  FOR i IN 1000 .. 2999
  LOOP
    INSERT INTO area_code( area_code )
      VALUES( to_char( i, '00000' ) );
  END LOOP;
END;

And then define a foreign key constraint from your Call table to the Area_Code table

CREATE TABLE call (
  call_id   NUMBER PRIMARY KEY,
  area_code VARCHAR2(5) REFERENCES area_code( area_code ),
  <<other columns>>
);

That's going to be more efficient to enforce than a CHECK constraint and it will be easier to list the valid area codes.

Upvotes: 5

Related Questions