SQL Table Restriction

Question

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

user700390 · Accepted Answer · 2016-01-17T23:32:14.477

1

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

edited Jan 17 '16 at 23:32

answered Jan 17 '16 at 22:37

user700390

2,287
1
19
26

You are right. I did that but now I have this error - ORA-00904: "FREQUENCY": invalid identifier – Dany4k Jan 17 '16 at 22:42
@Dany4k There's a problem with the `CHECK` clause, see the updated answer. – user700390 Jan 17 '16 at 22:45
Oracle does not have enums – Jan 17 '16 at 23:12
Your quote is from the **MySQL** manual, not from the Oracle database. – Jan 17 '16 at 23:29
I forgot they moved all that to thw oracle site. Not the first time I've tripped up on that either – user700390 Jan 17 '16 at 23:30

SQL Table Restriction

1 Answers1