Home » RDBMS Server » Server Administration » Error while adding not null constraint
Error while adding not null constraint [message #371078] Thu, 24 August 2000 01:06 Go to next message
Jaya Kumar NM
Messages: 11
Registered: August 2000
Junior Member
alter table emp add constraint nn_sal not null(sal)
the above statement gives me
"ORA-00904: invalid column name" error, please explain?
but it will accept
alter table emp modify sal not null
Re: Error while adding not null constraint [message #371091 is a reply to message #371078] Thu, 24 August 2000 18:05 Go to previous messageGo to next message
Andrew again...
Messages: 270
Registered: July 2000
Senior Member
Try this:
ALTER TABLE emp ADD CONSTRAINT
nn_sal CHECK (sal is NOT NULL);

Although Oracle recommends NOT creating CHECK CONSTRAINT as above, I still have to see a good reason why. Rather they recommend using the NOT NULL CONSTRAINT.
The advantave of using the CHECK CONSTRAINT is that it is Named, so when violated, you get the name which leads you to the actual column which is a problem. Using a NOT NULL CONSTRAINT you simply get the message:
ORA-01400: mandatory (NOT NULL) column is missing or NULL during insert
Re: try the following test... [message #371092 is a reply to message #371078] Thu, 24 August 2000 18:19 Go to previous message
Andrew again...
Messages: 270
Registered: July 2000
Senior Member
Try the following:
CREATE TABLE abc (a NUMBER, b NUMBER, c NUMBER);

ALTER TABLE abc MODIFY a NUMBER CONSTRAINT nn_a NOT NULL;

ALTER TABLE abc ADD CONSTRAINT nn_b CHECK (b IS NOT NULL);

ALTER TABLE abc MODIFY c NUMBER NOT NULL;

INSERT INTO abc VALUES (NULL ,2, 3);
(gives ORA-01400: mandatory (NOT NULL) COLUMN IS missing OR NULL during INSERT)

INSERT INTO abc VALUES (1, NULL, 3);
(gives ORA-02290: CHECK CONSTRAINT (testdb.NN_B) violated)

INSERT INTO abc VALUES (1,2, NULL);
(ORA-01400: mandatory (NOT NULL) COLUMN IS missing OR NULL during INSERT)
Previous Topic: unix-sql
Next Topic: Students in need; Sending email from database through forms.
Goto Forum:
  


Current Time: Tue Oct 20 10:16:39 CDT 2020