CODEWITHSUNDEEP
sqlregexoracle-databaseconstraints
Ross Knight
Ross Knight

Reputation: 39

How to prevent a input of certain letters using Oracle

The code is the category of the video, it is represented by one upper case character, excluding I, O, Q, V, Y and Z, followed by a numeric character.

So far, I took a guess and got this. Any suggestions on how to fix it?

create table channelTable (
    channelID number NOT NULL,
    ChannelName varchar(100) NOT NULL,
    ChannelDate date NOT NULL,
    UserName varchar(100) NOT NULL UNIQUE,
    TopicCode varchar(4) NOT NULL);

    CONSTRAINT channelID_pk PRIMARY KEY (channelID)
    CONSTRAINT c_topicCode LIKE '[A-Za-z][0-9] NOT (I,O,Q,N,Y,Z)
);

Upvotes: 0

Views: 336

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1271031

Some comments:

  • NOT NULL is not needed for PRIMARY KEY columns.
  • In Oracle, use VARCHAR2().

Then, I would suggests regular expressions. If the value is supposed to be exactly two characters, then declare it as such:

create table channelTable (
    channelID number,
    ChannelName varchar(100) NOT NULL,
    ChannelDate date NOT NULL,
    UserName varchar2(100) NOT NULL UNIQUE,
    TopicCode char(2) NOT NULL;

    CONSTRAINT channelID_pk PRIMARY KEY (channelID)
    CONSTRAINT check (REGEXP_LIKE(c_topicCode, '^[A-HJ-NPR-UYZ][0-9]$') 
);

Or perhaps more simply:

    CONSTRAINT REGEXP_LIKE(c_topicCode, '^[A-Z][0-9]$') AND NOT REGEXP_LIKE(c_topicCode, '^[IOQNYZ]'))

All that said, I would rather see a table of TopicCodes that is populated with the correct values. Then you can just use a foreign key relationship to define the appropriate codes.

Upvotes: 3

MT0
MT0

Reputation: 168681

Use the regular expression ^[A-HJ-MPR-X]\d$ to match an upper-case character excluding I,O,Q,N,Y,Z followed by a digit:

CREATE TABLE channels (
  id        number       CONSTRAINT channel__id__pk   PRIMARY KEY,
  Name      varchar(100) CONSTRAINT channel__name__nn NOT NULL,
  DateTime  date         CONSTRAINT channel__date__nn NOT NULL,
  UserName  varchar(100) CONSTRAINT channel__username__NN NOT NULL
                         CONSTRAINT channel__username__U UNIQUE,
  TopicCode varchar(4),
  CONSTRAINT channel__topiccode__chk CHECK ( REGEXP_LIKE( topiccode, '^[A-HJ-MPR-X]\d$' ) )
);

db<>fiddle

Also, you don't need to call the table channeltable just call it channels and you don't need to prefix the column names with the table name and you can name all the constraints (rather than relying on system generated constraint names which makes it much harder to track down issues when you are debugging).

Upvotes: 2

GMB
GMB

Reputation: 222682

Consider the following check constrait:

create table channelTable (
    ...
    topicCode varchar(4) not null
        check( 
            substr(c_topicCode, 1, 1) not in ('I', 'O', 'Q', 'V', 'Y', 'Z')
            and regexp_like(topicCode, '^[A-Z]\d')
        ),
    ...
);

The first condition ensures that the code does not start with one of the forbidden characters, the second valides that it stats with an upper alphabetic character, followed by a number.

To avoid using two conditions, an alternative would be to list all allowed characters in the first position:

check(regexp_like(topicCode, '^[ABCDEFGHJKLMNPRSTUVWX]\d'))

This works in Oracle, and in very recent versions of MySQL.

Upvotes: 1

Related Questions