0

THE SCENARIO I am working on an educational problem in which I need to convert some 'NOT NULL' constraints into 'NULL's and vice-versa.

PREVIOUS RESEARCH I have read up a few sources that tell me /how/ I can do this (with methods that I will describe below) but they don't explain why I would want to use a seemingly longer and more convoluted method over a seemingly faster method.

THE PROBLEM I've been instructed to query the table that I want to amend as follows: 

SELECT constraint_name, constraint_type
FROM all_constraints
WHERE table_name = 'FILM'
AND owner = 'SAKILA'

This gives me all the constraints on the table including what appear to be a bunch of constraints that were automatically created by the database: 

FK_FILM_LANGUAGE
FK_FILM_LANGUAGE_ORIGINAL
PK_FILM
SYS_C00738336
SYS_C00738337
SYS_C00738338
SYS_C00738339
SYS_C00738340

I am then being instructed to manually work out which SYS_**** relates to which item in the table and then manually drop those constraints and write in new ones as follows: 

ALTER TABLE film DROP CONSTRAINT SYS_C00738336;
ALTER TABLE film ADD CONSTRAINT constraint_name;
CONSTRAINT complete_nn CHECK (complete IS NOT NULL);

What I'm struggling to understand is why do I have to go through all of that when it appears that I can just use a 'modify' statement such as: 

alter table
    film
modify
    (film_title NOT NULL);

My understanding is that if you set something to 'NOT NULL' when you create it, the system automatically generates the constraint and that you don't need to manually drop it, which would save me quite a lot of time as there are a fairly large number of NULL and NOT NULL values that need to be altered.

Thank you in advance for any help you're able to provide.

Ross Cheeseright
  • 57
  • 8
  • 1
    You don't need to drop the constraint yourself! I would just go with the "modify column null/not null" method; it's easier, it lets Oracle do the work of finding the correct constraint to drop and you have the confidence that the right constraint has been dropped. – Boneist Nov 14 '17 at 11:18
  • @Boneist That's what I was thinking. It seems like the obvious solution, but I'm being asked to 'Jusfity' the use of the 'Modify' method and it's making me second-guess myself XD. Thank you for taking the time out to respond to my question. – Ross Cheeseright Nov 14 '17 at 11:23
  • I would be asking them to justify the other method! It's far more risky (what if you drop the wrong constraint?!), IMHO. – Boneist Nov 14 '17 at 12:05
  • Also, if the point is to have a nicely named constraint (rather than a system generated name, then you can do that in the modify statement. See https://asktom.oracle.com/pls/asktom/f?p=100:11:6215293737507::::P11_QUESTION_ID:9535102000346515432 for more info (including a slight difference between `modify ... not null` and `check (... is not null)`). – Boneist Nov 14 '17 at 12:09
  • Shouldn't your "modify" be `modify (film_title NULL)` do drop a NOT NULL constraint? –  Nov 14 '17 at 12:44

1 Answers1

1

This depends on how the table was created.

There are essentially three ways to define a not null column:

  1. film_title varchar(100) not null,
  2. film_title varchar(100) constraint nn_film_title not null
  3. film_title varchar(100) check (film_title is not null)

With option 3 the column is still marked as "NULLABLE" in USER_TAB_COLUMNS. In that case running alter table film3 modify film_title null will not remove the check constraint.

For option 1. and 2. the not null constraint can be removed either by using modify or by dropping the implicitly created check constraint by name.

I would also agree that for 1. and 2. using modify film_title null is the more stable (and less error-prone) approach.

However, dropping the check constraint is the only way that guarantees that the column is nullable afterwards - regardless on how the restriction was defined.

But it's extremely difficult to actually find out if a check constraint defines (only) a not null condition.