SQL (Oracle) - How to force a value in one column to be NULL if data containing specific value is entered in another column in the same table?

user_15338360 0
Viewed 110

I'm trying to figure out how you would constrain a certain column in a table to have be set to NULL or a specific string like "N/A" if a specific value is entered in another column.

I'm creating a table for teachers and each teacher has a supervisor ID (i.e. an ID relating to another teacher who is there immediate boss) but I don't want the Head Teacher to be able to have a supervisor. How would I create a constraint or method so that if the value for another column in the table - "teacherRole" is inserted as "Head Teacher", you would be prevented from giving this individual a supervisor ID and it would be set to NULL?

I.e. IF teacherRole = "Head Teacher" SET supervisorID = NULL

Also, I'm using another constraint so that "teacherRole" can only be one of a select number of values such as "PE Teacher" or "Maths Teacher" and want to make sure another constraint would not conflict with this?

constraints oracle sql
2 Answers

To prevent this you could add a check constraint to the table:

alter table mytable
   add constraint mytable_chk1
      check (not (teacherRole = 'Head Teacher' and supervisorID is not NULL));

Then if someone tries to insert or update a row such that it validates the constraint they will get an exception raised.

user_187470

apparently I am foolish and nonsensical

user_154267030
Related