Search code examples
sqloracleconstraintscheck-constraints

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

Solution

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