Home » SQL & PL/SQL » SQL & PL/SQL » constraints error (sql command line)
constraints error [message #601930] Wed, 27 November 2013 09:25 Go to next message
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.
Re: constraints error [message #601931 is a reply to message #601930] Wed, 27 November 2013 09:28 Go to previous messageGo to next message
BlackSwan
Messages: 23134
Registered: January 2009
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
Re: constraints error [message #601933 is a reply to message #601931] Wed, 27 November 2013 09:45 Go to previous messageGo to next message
aravindreddy.akiti
Messages: 14
Registered: October 2013
Location: hyderabad
Junior Member
can you pls explain the exact error in above syntax
Re: constraints error [message #601935 is a reply to message #601933] Wed, 27 November 2013 09:48 Go to previous messageGo to next message
pablolee
Messages: 2658
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 Go to previous messageGo to next message
cookiemonster
Messages: 11278
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.
Re: constraints error [message #601960 is a reply to message #601938] Wed, 27 November 2013 15:22 Go to previous messageGo to next message
Littlefoot
Messages: 19882
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>
Re: constraints error [message #601974 is a reply to message #601960] Thu, 28 November 2013 00:50 Go to previous messageGo to next message
John Watson
Messages: 4857
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.



Re: constraints error [message #601975 is a reply to message #601974] Thu, 28 November 2013 01:03 Go to previous message
Lalit Kumar B
Messages: 2544
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.
Previous Topic: Error In Function
Next Topic: trim or remove data and spaces
Goto Forum:
  


Current Time: Thu Dec 18 00:13:36 CST 2014

Total time taken to generate the page: 0.13784 seconds