OraFAQ Forum: SQL & PL/SQL » constraints error

Home » SQL & PL/SQL » SQL & PL/SQL » constraints error (sql command line)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

constraints error [message #601930]

Wed, 27 November 2013 09:25

aravindreddy.akiti
Messages: 14
Registered: October 2013
Location: hyderabad

Junior Member

create table subject
(
sub_code varchar2(10) constraint subj_code_pk
);

error at line 3:
constraint specification not allowed here.
can you explain me what is the error.

Report message to a moderator

Re: constraints error [message #601931 is a reply to message #601930]

Wed, 27 November 2013 09:28

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

SQL> create table subject
(
sub_code varchar2(10) constraint subj_code_pk
);  2    3    4  
sub_code varchar2(10) constraint subj_code_pk
                      *
ERROR at line 3:
ORA-02253: constraint specification not allowed here


SQL> !oerr ora 2253
02253, 00000, "constraint specification not allowed here"
// *Cause: Constraint specification is not allowed here in the statement.
// *Action: Remove the constraint specification from the statement.

SQL>

use only valid syntax as written in the Fine Manual below

http://docs.oracle.com/cd/E16655_01/server.121/e17209/statements_7002.htm#SQLRF01402

Report message to a moderator

Re: constraints error [message #601933 is a reply to message #601931]

Wed, 27 November 2013 09:45

aravindreddy.akiti
Messages: 14
Registered: October 2013
Location: hyderabad

Junior Member

can you pls explain the exact error in above syntax

Report message to a moderator

Re: constraints error [message #601935 is a reply to message #601933]

Wed, 27 November 2013 09:48

pablolee
Messages: 2882
Registered: May 2007
Location: Scotland

Senior Member

Wow, that was a big document for you to read through in just 17 minutes (assuming that you accessed the link as soon as it was posted). Was there no information in that link pertaining to constraints and the syntax surrounding them? I'm shocked! Shocked an appalled I say, that Oracle would not include information on such a topic.

[Updated on: Wed, 27 November 2013 09:49]

Report message to a moderator

Re: constraints error [message #601938 is a reply to message #601935]

Wed, 27 November 2013 10:19

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

You know there are multiple types of constraint? You have to tell oracle which one you had in mind.

Report message to a moderator

Re: constraints error [message #601960 is a reply to message #601938]

Wed, 27 November 2013 15:22

Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

"pk" in constraint name suggests that you want to create a primary key constraint. However, Oracle can't read your mind (but I can!) and it doesn't know what you want to do, unless you explicitly tell it so. Here are several ways you could use to create a primary key. They are most probably explained in a document you were instructed to read; take your time.

As of the error you got: it was because you tried to invent your own syntax, but Oracle didn't like it. Therefore, stick to valid syntax and you won't have problem of this kind.

OK then, here are some quick examples. As you can see, there are different ways to achieve the same goal, but you must follow the rules.

SQL> create table test (col number primary key);

Table created.

SQL> create table test1 (col number constraint pk_test1 primary key);

Table created.

SQL> create table test2 (col number);

Table created.

SQL> alter table test2 add constraint pk_test2 primary key (col);

Table altered.

SQL>

Report message to a moderator

Re: constraints error [message #601974 is a reply to message #601960]

Thu, 28 November 2013 00:50

John Watson
Messages: 8931
Registered: January 2010
Location: Global Village

Senior Member

With regard to this syntax,

create table test (col number primary key);

speaking as a DBA, please do not use it! In no particular order:
1. It will have a created a contraint and an index with a silly system generated name. You will have to rename them.
2. The index will be a unique index. This means that the constraint is non-deferrable.
3. If you disable the constraint, the index will be dropped.

This is better,

create table test1 (col number constraint pk_test1 primary key);

because the constraint and the index will have proper names. The other problems remain.

You should create the index first, and think about it: how should it be structured? Definitely nonunique. What columns, in what order? Reverse key? Compressed? Nologging? Then define the constraint: deferrable initially immediate? A constraint and an index are not the same thing (OK, they were decades ago) and should be created separately.

Report message to a moderator

Re: constraints error [message #601975 is a reply to message #601974]

Thu, 28 November 2013 01:03

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

John Watson wrote on Thu, 28 November 2013 12:20

3. If you disable the constraint, the index will be dropped.

A constraint and an index are not the same thing (OK, they were decades ago) and should be created separately.

Indeed a very important point made by John, specially whenever we want to DISABLE/NOVALIDATE the constraint. KEEP/USING index can't be used if a non-unique index was not pre-created. If we do not create an index explicitly, then the unique index which is automatically created will be dropped. A simple syntax, but it could impact the entire design.

Report message to a moderator

Previous Topic:	Error In Function
Next Topic:	trim or remove data and spaces

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Apr 24 07:05:02 CDT 2024