Home » SQL & PL/SQL » SQL & PL/SQL » NOT NULL Constraint
NOT NULL Constraint [message #347713] Sat, 13 September 2008 02:36 Go to next message
ygsunilkumar
Messages: 100
Registered: August 2008
Senior Member
I missed out to create NOT NULL Constraint while creating TABLE. Can NOT NULL constraint can be added by using ALTER TABLE option? I am getting error. Pls suggest
Re: NOT NULL Constraint [message #347715 is a reply to message #347713] Sat, 13 September 2008 02:42 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
You are required to copy and paste your SQL*Plus session which describes your attempt. "I am getting an error" is far from enough.

And yes, it can be done using the ALTER TABLE command (but only if you have read the documentation carefully enough). The solution requires 7 words and a semi-colon.
Re: NOT NULL Constraint [message #347716 is a reply to message #347713] Sat, 13 September 2008 02:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes you can but without the error we can't help you.
Nevertheless I bet your error is because some of the fields are null. In this case, just give them a value.

Regards
Michel
Re: NOT NULL Constraint [message #347717 is a reply to message #347715] Sat, 13 September 2008 02:48 Go to previous messageGo to next message
ygsunilkumar
Messages: 100
Registered: August 2008
Senior Member
Pls find the attachment for the error and give me correct query.
  • Attachment: NOT NULL.txt
    (Size: 0.95KB, Downloaded 218 times)
Re: NOT NULL Constraint [message #347718 is a reply to message #347717] Sat, 13 September 2008 02:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Copy and paste your session HERE (with formatting) don't attach a file for 10 lines.

ALTER TABLE syntax in in SQL Reference

Regards
Michel
Re: NOT NULL Constraint [message #347719 is a reply to message #347717] Sat, 13 September 2008 02:58 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
There's no need to attach the file; put your code into the message and enclose into the [code] tags:
SQL> desc sunil
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                        NOT NULL NUMBER(2)
 FIRST_NAME                                NOT NULL VARCHAR2(20)
 LAST_NAME                                 NOT NULL VARCHAR2(20)
 DOB                                                DATE
 SEX                                                VARCHAR2(1)
 QUALIFICATION                                      VARCHAR2(20)
 AGE                                                NUMBER(2)



SQL> alter table sunil
  2  add constraint sexnn not null (sex);
add constraint sexnn not null (sex)
                     *
ERROR at line 2:
ORA-00904: invalid column name


SQL> alter table sunil
  2  add constraint not null (sex);
add constraint not null (sex)
                         *
ERROR at line 2:
ORA-01735: invalid ALTER TABLE option

It appears that you didn't study the ALTER TABLE after all; do that!

A hint: MODIFY is the keyword you'll also need to use.
Re: NOT NULL Constraint [message #347720 is a reply to message #347719] Sat, 13 September 2008 03:09 Go to previous messageGo to next message
ygsunilkumar
Messages: 100
Registered: August 2008
Senior Member
I have studied the ALTER TABLE option but unable to get the correct query. Give me correct query to add constraint not null using alter table.
Re: NOT NULL Constraint [message #347723 is a reply to message #347720] Sat, 13 September 2008 03:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Maybe you should have a closer to out_of_line_constraint

Regards
Michel
Re: NOT NULL Constraint [message #347765 is a reply to message #347713] Sat, 13 September 2008 15:55 Go to previous messageGo to next message
aniruddha_jathar
Messages: 5
Registered: September 2008
Junior Member
This should work :

SQL> alter table sunil
2 add constraint sex not null;

Regards,

Aniruddha
Re: NOT NULL Constraint [message #347767 is a reply to message #347765] Sat, 13 September 2008 16:06 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
aniruddha_jathar wrote on Sat, 13 September 2008 22:55
This should work :

SQL> alter table sunil
2 add constraint sex not null;


Perhaps on your database version, but not on my 10g:
SQL> create table sunil (sex number);

Table created.

SQL> alter table sunil
  2  add constraint sex not null;
add constraint sex not null
                   *
ERROR at line 2:
ORA-00904: : invalid identifier


SQL>

Re: NOT NULL Constraint [message #347770 is a reply to message #347767] Sat, 13 September 2008 16:17 Go to previous messageGo to next message
aniruddha_jathar
Messages: 5
Registered: September 2008
Junior Member
I am sorry the correct code should be as follows :

 alter table sunil modify sex constraint sex not null;


Thanks,

Aniruddha
Re: NOT NULL Constraint [message #347771 is a reply to message #347770] Sat, 13 September 2008 16:37 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
As we're at it, why not simply
SQL> alter table sunil modify sex not null;

Table altered.

SQL>
Re: NOT NULL Constraint [message #347772 is a reply to message #347771] Sat, 13 September 2008 17:01 Go to previous messageGo to next message
aniruddha_jathar
Messages: 5
Registered: September 2008
Junior Member
Yes of course. But I believe that it is always a good practice to name your constraints (rather than allow Oracle to provide a generic name) that are easy to remember just in case you decide to drop the constraint at a later point of time.
Re: NOT NULL Constraint [message #347818 is a reply to message #347772] Sun, 14 September 2008 05:52 Go to previous message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Does it mean that you create tables which contain NOT NULL columns differently from a simple
create table test
(id number not null);
and, instead, use the full NOT NULL constraint syntax? Fine with me; however, why do you name constraints the same as columns? How can you distinguish which "sex" you are talking about - a column name or a constraint name?
Previous Topic: WHEN OTHERS Exception
Next Topic: Insert date on a table
Goto Forum:
  


Current Time: Thu Dec 08 14:33:40 CST 2016

Total time taken to generate the page: 0.08083 seconds