Search code examples
sqloracleoracle11gcheck-constraint

SQL Table Restriction

How can I specify, 5 distinct values for a varchar column in Oracle Application Express?

I need a column called tipo_conta (varchar) that only accepts 'Conta a ordem', 'Multibanco', 'Rendimento', 'Jovem', 'Rendimento-Habitacao' as possible values.

I tried this but I get this error - ORA-00907: missing right parenthesis.

What am I doing wrong?

CREATE TABLE contas 
(
id_conta NUMBER(6),
tipo_conta VARCHAR2(20),
CONSTRAINT id_conta PRIMARY KEY(id_conta),
CONSTRAINT tipo_conta UNIQUE (tipo_conta)
CONSTRAINT chk_tipo_conta CHECK (Frequency IN ('Conta a ordem', 'Multibanco', 'Rendimento', 'Jovem', 'Rendimento-Habitacao'))
);
Solution

  • Actually it looks like you are missing a comma in your CONSTRAINT CLAUSES over here:

    CONSTRAINT tipo_conta UNIQUE (tipo_conta)

    should instead be:

    CONSTRAINT tipo_conta UNIQUE (tipo_conta),

    Also your CHECK does not reference the column properly:

    Instead of CONSTRAINT chk_tipo_conta CHECK (Frequency IN ...

    try CONSTRAINT chk_tipo_conta CHECK (tipo_conta IN ...