CODEWITHSUNDEEP
sqloracle-databaseconstraintscheck-constraints
user14842715
user14842715

Reputation:

Can I allow only certain specific strings to be inputted for a table in Oracle 10G?

CREATE TABLE trial (
    phone_number NUMBER(5) PRIMARY KEY;
    name VARCHAR2(20)
)

In this table, I want a constraint such that, name inputted should be only 'trial','experiment' or 'test'.

Upvotes: 1

Views: 1296

Answers (3)

MT0
MT0

Reputation: 167972

Use a CHECK constraint:

CREATE TABLE trial (
    phone_number NUMBER(5)
                 CONSTRAINT trial__phone_number__pk PRIMARY KEY,
    name         VARCHAR2(20)
                 NOT NULL
                 CONSTRAINT trial__name__ck CHECK (
                   name IN ( 'trial', 'experiment', 'test' )
                 )
);

Then:

INSERT INTO trial ( phone_number, name )
SELECT 1, 'trial' FROM DUAL UNION ALL
SELECT 2, 'experiment' FROM DUAL UNION ALL
SELECT 3, 'test' FROM DUAL;

Works but:

INSERT INTO trial ( phone_number, name ) VALUES ( 4, 'not valid' );

Outputs:

ORA-02290: check constraint (SCHEMA_NAME.TRIAL__NAME__CK) violated

db<>fiddle here

Upvotes: 0

Popeye
Popeye

Reputation: 35900

You can add the CHECK constraint as follows:

ALTER TABLE trial  
ADD CONSTRAINT name_CHK CHECK (NAME IN ('trial','experiment','test'));

If you don't want the null values in the name then you can also use NOT NULL constraint on the name column.

While creating the table, you can use the Constraint inside it as follows:

CREATE TABLE trial (
    phone_number NUMBER(5) PRIMARY KEY;
    name VARCHAR2(20) not null,
    CONSTRAINT name_CHK CHECK (NAME IN ('trial','experiment','test')
);

Upvotes: 1

Littlefoot
Littlefoot

Reputation: 142705

It is a check constraint:

SQL> create table trial
  2    (phone_number number(5) primary key,
  3     name         varchar2(20) constraint ch_tri_nam check
  4                                 (name in ('trial', 'experiment', 'test'))
  5    );

Table created.

SQL> insert into trial (phone_number, name) values (1, 'trial');

1 row created.

SQL> insert into trial (phone_number, name) values (1, 'what');
insert into trial (phone_number, name) values (1, 'what')
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.CH_TRI_NAM) violated


SQL>

Alternatively, you could use a trigger whose message is more descriptive (check constraint only says that it is violated; in a trigger, you can create your own message).

SQL> create table trial
  2    (phone_number number(5) primary key,
  3     name         varchar2(20)
  4    );

Table created.

SQL> create or replace trigger trg_biu_trial
  2    before insert or update on trial
  3    for each row
  4  begin
  5    if nvl(:new.name, :old.name) not in ('trial', 'experiment', 'test') then
  6       raise_application_error(-20000, 'Invalid name; allowed values are: trial, experiment, test');
  7    end if;
  8  end;
  9  /

Trigger created.

SQL> insert into trial (phone_number, name) values (1, 'trial');

1 row created.

SQL> insert into trial (phone_number, name) values (1, 'what');
insert into trial (phone_number, name) values (1, 'what')
            *
ERROR at line 1:
ORA-20000: Invalid name; allowed values are: trial, experiment, test
ORA-06512: at "SCOTT.TRG_BIU_TRIAL", line 3
ORA-04088: error during execution of trigger 'SCOTT.TRG_BIU_TRIAL'


SQL>

Upvotes: 0

Related Questions