Home » SQL & PL/SQL » SQL & PL/SQL » NOT NULL Constraint Oracle SQL (Oracle Database 10g, Windows)
NOT NULL Constraint Oracle SQL [message #386541] Mon, 16 February 2009 01:33 Go to next message
helloafzaal
Messages: 17
Registered: February 2009
Location: Dubai,UAE
Junior Member

Hello I want to ALTER TABLE and ADD NOT NULL constraint.Oracle By defaults gives name to NOT NULL constraints. But in my case I want to give constraint name by my self with ALTER command. What will be the syntax for this?

Thanks
Afzaal
Re: NOT NULL Constraint Oracle SQL [message #386544 is a reply to message #386541] Mon, 16 February 2009 01:52 Go to previous messageGo to next message
dr.s.raghunathan
Messages: 540
Registered: February 2008
Senior Member
use check constraint
Re: NOT NULL Constraint Oracle SQL [message #386556 is a reply to message #386541] Mon, 16 February 2009 02:29 Go to previous messageGo to next message
Frank
Messages: 7877
Registered: March 2000
Senior Member
My first reaction was "Why bother?"
Re: NOT NULL Constraint Oracle SQL [message #386562 is a reply to message #386541] Mon, 16 February 2009 02:37 Go to previous messageGo to next message
m_golam_hossain
Messages: 85
Registered: August 2008
Location: Uttara, Dhaka, Bangladesh
Member

Mr. Afzaal,

Please follow the following example:

ALTER TABLE data.transdocdtl 
	MODIFY(
	docid   VARCHAR2(14) NOT NULL,
	doctype VARCHAR2(14) NOT NULL, 
	transdt DATE         NOT NULL);



And study on ALTER TABLE ..... ADD CONSTRAINT command.


Mohd. Golam Hossain
Uttara, Dhaka, Bangladesh.

[Updated on: Mon, 16 February 2009 02:41]

Report message to a moderator

Re: NOT NULL Constraint Oracle SQL [message #386574 is a reply to message #386562] Mon, 16 February 2009 03:02 Go to previous messageGo to next message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
Senior Member
If you really want to do this, try this

CREATE TABLE temp_drop ( a NUMBER NOT NULL);

SELECT constraint_name
  FROM user_constraints
 WHERE table_name = 'TEMP_DROP';


CONSTRAINT_NAME               
------------------------------
SYS_C00158927                 

1 row selected.

ALTER TABLE temp_drop RENAME CONSTRAINT SYS_C00158927 TO new_name;

SELECT constraint_name
  FROM user_constraints
 WHERE table_name = 'TEMP_DROP';

CONSTRAINT_NAME               
------------------------------
NEW_NAME                      

1 row selected.


Thanks
Trivendra
Re: NOT NULL Constraint Oracle SQL [message #386580 is a reply to message #386574] Mon, 16 February 2009 03:12 Go to previous messageGo to next message
helloafzaal
Messages: 17
Registered: February 2009
Location: Dubai,UAE
Junior Member

Thanks for you reply

Actually I am developing one batch process. In that batch process I want to perfom this functionality. I will first create NOT NULL constraints and after i wnat to DISABLE that constraint and this can be done several times times.
In this case I can't hard code constraint name like "SYS_C00158927 ".

Do you have any solution for it.

Thanks

Afzaal
Re: NOT NULL Constraint Oracle SQL [message #386582 is a reply to message #386580] Mon, 16 February 2009 03:17 Go to previous messageGo to next message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
Senior Member
By two ways

1. Create a alter script for ENABLE and DISABLE of constraint.

2. Create a block, select constraint name from USER_CONSTRAINTS view and use dynamic sql to ENABLE/DISABLE constraint.

Thanks
Trivendra
Re: NOT NULL Constraint Oracle SQL [message #386601 is a reply to message #386541] Mon, 16 February 2009 03:56 Go to previous messageGo to next message
cookiemonster
Messages: 10846
Registered: September 2008
Location: Rainy Manchester
Senior Member
Alternatively you could dispense with adding and removing not null constraints and use a staging table instead.

If you do insist on loading your data this way you really don't need to know the constraint names, just the column names will do.
Read up on the alter table command.
Re: NOT NULL Constraint Oracle SQL [message #386603 is a reply to message #386582] Mon, 16 February 2009 04:06 Go to previous messageGo to next message
helloafzaal
Messages: 17
Registered: February 2009
Location: Dubai,UAE
Junior Member

I think my question is not clear to you.

Following are steps in my batch process

1-
EXECUTE IMMEDIATE 'ALTER TABLE XXX MODIFY YYYY NOT NULL';


2-EXECUTE IMMEDIATE 'ALTER TABLE XXX DISABTLE CONSTRAINT '????' ;

4-Perform DML OPERATIONS 0N the table.

5- EXECUTE IMMEDIATE 'ALTER TABLE XXX ENABLE CONSTRAINT '???";

In this case how can I get the exact constraint name for NOT NULL constraint because for NOT NULL oracle automatically gives a constraint name?

Now I have only one question that can I use ALTER TABLE statemnet in which I can specify the name for NOT NULL constraint? Yes or No.

Thanks for your reply.

Afzaal








Re: NOT NULL Constraint Oracle SQL [message #386605 is a reply to message #386603] Mon, 16 February 2009 04:12 Go to previous messageGo to next message
Michel Cadot
Messages: 58545
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Now I have only one question that can I use ALTER TABLE statemnet in which I can specify the name for NOT NULL constraint? Yes or No.

If you use CONSTRAINT to create the constraint (either in CREATE TABLE or ALTER TABLE). Yes.
If you just use NOT NULL. No.

Regards
Michel
Re: NOT NULL Constraint Oracle SQL [message #386613 is a reply to message #386605] Mon, 16 February 2009 04:27 Go to previous messageGo to next message
helloafzaal
Messages: 17
Registered: February 2009
Location: Dubai,UAE
Junior Member

Thanks


SQL> create table abc( a number);

Table created.

SQL> alter table abc add constraint abc_a_nn not null;
alter table abc add constraint abc_a_nn not null


ERROR at line 1:
ORA-00904: : invalid identifier
----------------------------------
Now When I try to add constraint by a name which i am specifying i have error.in this case "abc_a_nn" is constraint name which i want to specify for NOT NULL constraint.

What will be the exact systax to add constraint, which must include Programmer's defind constraint name not oracle default?

Thanks again for response.

Bests
Afzaal



Re: NOT NULL Constraint Oracle SQL [message #386616 is a reply to message #386541] Mon, 16 February 2009 04:31 Go to previous messageGo to next message
sukhijank
Messages: 5
Registered: February 2009
Junior Member
You can use:

CREATE TABLE TEST123 (A NUMBER NOT NULL, B NUMBER, C NUMBER);

ALTER TABLE TEST123 ADD CONSTRAINT B_NOT_NULL CHECK ("B" IS NOT NULL);

ALTER TABLE TEST123 DISABLE CONSTRAINT B_NOT_NULL;

Re: NOT NULL Constraint Oracle SQL [message #386619 is a reply to message #386616] Mon, 16 February 2009 04:37 Go to previous messageGo to next message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
Senior Member
I dont know why you want to change the name of NOT NULL constraint. ENABLE and DISABLE can be eaisly done by given two methods in my above post. Just to clarify you ..


SQL> CREATE TABLE temp1 (a NUMBER NOT NULL, b NUMBER NOT NULL)
Table created.
SQL> CREATE TABLE temp2 (a NUMBER NOT NULL, b NUMBER NOT NULL)
Table created.
SQL> CREATE TABLE temp3 (a NUMBER NOT NULL, b NUMBER NOT NULL)
Table created.

SQL> -- Case 1

SQL> -- Create Manual Script For DISABLE

SQL> SELECT    'ALTER TABLE '
       || table_name
       || ' DISABLE CONSTRAINT '
       || constraint_name
       || ';'
  FROM user_constraints
 WHERE table_name IN ('TEMP1', 'TEMP2', 'TEMP3')

'ALTERTABLE'||TABLE_NAME||'DISABLECONSTRAINT'||CONSTRAINT_NAME||';'             
--------------------------------------------------------------------------------
ALTER TABLE TEMP1 DISABLE CONSTRAINT SYS_C00158946;                             
ALTER TABLE TEMP1 DISABLE CONSTRAINT SYS_C00158947;                             
ALTER TABLE TEMP2 DISABLE CONSTRAINT SYS_C00158948;                             
ALTER TABLE TEMP2 DISABLE CONSTRAINT SYS_C00158949;                             
ALTER TABLE TEMP3 DISABLE CONSTRAINT SYS_C00158950;                             
ALTER TABLE TEMP3 DISABLE CONSTRAINT SYS_C00158951;                             

6 rows selected.

SQL> -- Create Manual Script For ENABLE 

SQL> SELECT    'ALTER TABLE '
       || table_name
       || ' ENABLE CONSTRAINT '
       || constraint_name
       || ';'
  FROM user_constraints
 WHERE table_name IN ('TEMP1', 'TEMP2', 'TEMP3')

'ALTERTABLE'||TABLE_NAME||'ENABLECONSTRAINT'||CONSTRAINT_NAME||';'              
--------------------------------------------------------------------------------
ALTER TABLE TEMP1 ENABLE CONSTRAINT SYS_C00158946;                              
ALTER TABLE TEMP1 ENABLE CONSTRAINT SYS_C00158947;                              
ALTER TABLE TEMP2 ENABLE CONSTRAINT SYS_C00158948;                              
ALTER TABLE TEMP2 ENABLE CONSTRAINT SYS_C00158949;                              
ALTER TABLE TEMP3 ENABLE CONSTRAINT SYS_C00158950;                              
ALTER TABLE TEMP3 ENABLE CONSTRAINT SYS_C00158951;                              

6 rows selected.

SQL> /* You can this scripts to ENABLE or DISABLE your table constraints*/

SQL> -- case 2 
SQL> -------------------------------------------------------------------------------
SQL> -- BY BLOCK to DISABLE constraints
SQL> -------------------------------------------------------------------------------

SQL> DECLARE
   l_fun   VARCHAR2 (10) := 'DISABLE';
BEGIN
   FOR i IN (SELECT table_name, constraint_name
               FROM user_constraints
              WHERE table_name IN ('TEMP1', 'TEMP2', 'TEMP3'))
   LOOP
      EXECUTE IMMEDIATE    'ALTER TABLE '
                        || i.table_name
                        || ' '
                        || l_fun
                        || ' '
                        || 'CONSTRAINT '
                        || i.constraint_name;
   END LOOP;
END;
PL/SQL procedure successfully completed.

SQL> -- Check for DISABLE constarints

SQL> SELECT table_name, constraint_name, status
  FROM user_constraints
 WHERE table_name IN ('TEMP1', 'TEMP2', 'TEMP3')

TABLE_NAME                     CONSTRAINT_NAME                STATUS  
------------------------------ ------------------------------ --------
TEMP1                          SYS_C00158946                  DISABLED
TEMP1                          SYS_C00158947                  DISABLED
TEMP2                          SYS_C00158948                  DISABLED
TEMP2                          SYS_C00158949                  DISABLED
TEMP3                          SYS_C00158950                  DISABLED
TEMP3                          SYS_C00158951                  DISABLED

6 rows selected.

SQL> ------------------------------------------------------------------------------- 
SQL> -- BY BLOCK to DISABLE constraints 
SQL> -------------------------------------------------------------------------------

SQL> DECLARE
   l_fun   VARCHAR2 (10) := 'ENABLE';
BEGIN
   FOR i IN (SELECT table_name, constraint_name
               FROM user_constraints
              WHERE table_name IN ('TEMP1', 'TEMP2', 'TEMP3'))
   LOOP
      EXECUTE IMMEDIATE    'ALTER TABLE '
                        || i.table_name
                        || ' '
                        || l_fun
                        || ' '
                        || 'CONSTRAINT '
                        || i.constraint_name;
   END LOOP;
END;
PL/SQL procedure successfully completed.

SQL> -- Check for DISABLE constarints

SQL> SELECT table_name, constraint_name, status
  FROM user_constraints
 WHERE table_name IN ('TEMP1', 'TEMP2', 'TEMP3')

TABLE_NAME                     CONSTRAINT_NAME                STATUS  
------------------------------ ------------------------------ --------
TEMP1                          SYS_C00158946                  ENABLED 
TEMP1                          SYS_C00158947                  ENABLED 
TEMP2                          SYS_C00158948                  ENABLED 
TEMP2                          SYS_C00158949                  ENABLED 
TEMP3                          SYS_C00158950                  ENABLED 
TEMP3                          SYS_C00158951                  ENABLED 

6 rows selected.


Thanks
Trivendra
Re: NOT NULL Constraint Oracle SQL [message #386620 is a reply to message #386541] Mon, 16 February 2009 04:37 Go to previous messageGo to next message
helloafzaal
Messages: 17
Registered: February 2009
Location: Dubai,UAE
Junior Member

Great!!!!

This is the answer of my question.

Thanks a lot for your reply.

Best Rgards,
Afzaal.


Re: NOT NULL Constraint Oracle SQL [message #386621 is a reply to message #386603] Mon, 16 February 2009 04:38 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Oracle only auto-names Not null constraints if you don't bother to name them yourself.

If you use syntax like this:
create table test_102 (col_1 number constraint test_102_nn01 not null);

Then you can give your constraints names you control, and can then enable and disable them to your heart's content.
Re: NOT NULL Constraint Oracle SQL [message #386623 is a reply to message #386616] Mon, 16 February 2009 04:39 Go to previous messageGo to next message
ThomasG
Messages: 3087
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Maybe you should also tell us what you are actually trying to ACHIEVE with all this.

Two things that might be better than all this creating/dropping enabling/disabling, depending on what you are trying to do, might be :

1) Using a staging table where you clean up the data before you do the insert into the final table. (as already suggested)

2) Use a Deferred Constraint, which is checked after the transaction is finished with a commit.
Re: NOT NULL Constraint Oracle SQL [message #386625 is a reply to message #386613] Mon, 16 February 2009 04:45 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Probably if you have spent your time reading the oracle reference manual you would have got the answer. May be this link will help you how to do it.

Inline Constraint :
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/clauses002.htm#CJAGIICD

Out_Of_Line Constraint :
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/clauses002.htm#CJADJGEC

In short you could give a name to not null constraint only inline (i.e) while creating the table. If you choose to add the constraint using the alter table then you cannot name the not null constraint.

Hope this helps.

Regards

Raj

[Edit: ] @JRowbottom, I didn't see your post. Apologies.

[Updated on: Mon, 16 February 2009 04:46]

Report message to a moderator

Re: NOT NULL Constraint Oracle SQL [message #386627 is a reply to message #386541] Mon, 16 February 2009 04:57 Go to previous messageGo to next message
helloafzaal
Messages: 17
Registered: February 2009
Location: Dubai,UAE
Junior Member

Thanks a lot many useful replies and helping links.
This meets my requirement.
In my requirements there was not only one time commit but multiple time process and multiple time commit

Thanks a lot.

Best Regards,
Afzaal.

Re: NOT NULL Constraint Oracle SQL [message #386692 is a reply to message #386613] Mon, 16 February 2009 12:34 Go to previous messageGo to next message
joy_division
Messages: 4485
Registered: February 2005
Location: East Coast USA
Senior Member
helloafzaal wrote on Mon, 16 February 2009 05:27

SQL> create table abc( a number);

Table created.

SQL> alter table abc add constraint abc_a_nn not null;
alter table abc add constraint abc_a_nn not null


ERROR at line 1:
ORA-00904: : invalid identifier



I think you probably what you were looking for with the responses after this question, but I just wanted to point out that this error is there because if you look at this ALTER command carefully, what would adding a NOT NULL constraint mean if there is no column associated with it?
Re: NOT NULL Constraint Oracle SQL [message #386773 is a reply to message #386692] Mon, 16 February 2009 23:57 Go to previous messageGo to next message
m_golam_hossain
Messages: 85
Registered: August 2008
Location: Uttara, Dhaka, Bangladesh
Member

Mr. Afzaal,

You may try this:

--drop the constraint if it already exists 
alter table abc drop constraint abc_a_nn; 
alter table abc add constraint abc_a_nn CHECK (a IS NOT NULL);



Thanks,

Mohd. Golam Hossain
Uttara, Dhaka, Bangladesh.

[Updated on: Tue, 17 February 2009 00:01]

Report message to a moderator

Re: NOT NULL Constraint Oracle SQL [message #386795 is a reply to message #386773] Tue, 17 February 2009 00:49 Go to previous messageGo to next message
Michel Cadot
Messages: 58545
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
@m_golam_hossain

This does not add anything to the topic and the provided documentation, just spoon feed OP.
As already said to you:
Don't put solution only hint or clue as requested in OraFAQ Forum Guide, "Responding to Posts" section:
Quote:
When responding to questions, if it is obviously a student with a homework assignment or someone else just learning, especially in the homework and newbies forums, it is usual to provide hints or clues, perhaps links to relevant portions of the documentation, or a similar example, to point them in the right direction so that they will research and experiment on their own and learn, and not provide complete solutions to problems. In cases where someone has a real-life complex work problem, or at least it may seem complex to them, it may be best to provide a complete demo and explanation.


Regards
Michel
Re: NOT NULL Constraint Oracle SQL [message #386805 is a reply to message #386795] Tue, 17 February 2009 01:05 Go to previous message
Frank
Messages: 7877
Registered: March 2000
Senior Member
Since this whole thread is full of solutions, I think it is not fair to blame m_golam_hossain for giving a "full solution".
However, his post does indeed not really add to the thread, since his suggestion was already in there.
Previous Topic: inserting rows
Next Topic: Problem in formatting Date
Goto Forum:
  


Current Time: Thu Jul 24 18:51:35 CDT 2014

Total time taken to generate the page: 0.16030 seconds