CODEWITHSUNDEEP
oracle-databaseconstraints
rmh13
rmh13

Reputation: 13

MODIFY or ADD to add NOT NULL constraint to a column? Oracle sql

ORDERS table in the Oracle Database:

ORDERS
ORDER_ID NOT NULL NUMBER(4)
ORDATE_DATE DATE
CUSTOMER_ID NUMBER(3)
ORDER_TOTAL NUMBER(7,2)

The ORDERS table contains data and all orders have been assigned a customer ID. I'm trying to add a NOT NULL constraint to the CUSTOMER_ID column. Would I use MODIFY CONSTRAINT or ADD CONSTRAINT? I was told you have to drop the constraint and ADD the new one, but if there is no existing constraint to Customer ID number, would it be MODIFY?

Upvotes: 1

Views: 17513

Answers (2)

The Impaler
The Impaler

Reputation: 48770

Just MODIFY the column:

alter table orders modify customer_id not null;

Alternatively, you could add an [overkill] constraint in the form:

alter table orders add constraint nn1 check (customer_id is not null);

Just use the first form.

As a side note, some databases (such as Oracle) consider those two constraint different and somewhat separate: the former is a column constraint, while the latter is a table constraint. Oracle keeps track in case you drop one, while the other is still in effect.

Upvotes: 4

Littlefoot
Littlefoot

Reputation: 142713

alter table orders modify customer_id not null;

Upvotes: 3

Related Questions