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?
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.
alter table orders modify customer_id not null;
