Create Table – ORA-00907: missing right parenthesis — CHECK and DEFAULT Order Swapped

Most people, including myself, don’t memorize the syntax and associated options for every programming language. So, I was interested in determining what the issue was with the following create table command that one of my developers was having. He continued to receive the following error, regardless of what he did with parentheses:

ORA-00907: missing right parenthesis

CREATE TABLE AppItem (id INTEGER PRIMARY KEY NOT NULL,
name VARCHAR(100),
url VARCHAR(100),
required CHAR(1) CHECK (required IN('N','Y')) DEFAULT 'N',
img VARCHAR(100),
title VARCHAR(100),
description VARCHAR(100)
);

Being that the parentheses evened out, I first thought that the word required would be a reserved word in Oracle, so I tried changing that to is_required. No dice. After a little more fiddling — and only a few seconds before I would have turned to some documentation — I realized that the CHECK constraint needed to come after the DEFAULT value setting.

CREATE TABLE AppItem (id INTEGER PRIMARY KEY NOT NULL,
name VARCHAR(100),
url VARCHAR(100),
required CHAR(1) DEFAULT 'N' CHECK (required IN('N','Y')),
img VARCHAR(100),
title VARCHAR(100),
description VARCHAR(100)
);

Leave a Reply

Your email address will not be published. Required fields are marked *