| Error while adding not null constraint [message #371078] |
Thu, 24 August 2000 01:06  |
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  |
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 |
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)
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
