CODEWITHSUNDEEP
sqloracle-databaseconstraints
Hyperion 1872
Hyperion 1872

Reputation: 17

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?

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?

Upvotes: 1

Views: 274

Answers (2)

user15426703
user15426703

Reputation: 16

apparently I am foolish and nonsensical

Upvotes: 0

Tony Andrews
Tony Andrews

Reputation: 132620

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.

Upvotes: 1

Related Questions