Some clarifications about constraints in oracleSQL

Question

I'm having trouble grasping a concept in my database course. We are going over view's. I understand the utility and syntax for the most part. I am running into trouble trying to insert into the view i created.

For instance,

create table employees ( firstname varchar2(15), lastname varchar2(15), Gender char(1));

now when i make a view of just the first, and last name of all males.

Something like

create view maleEMP AS select firstname, lastname FROM employees where gender = 'm';

Now this will create a view that holds the male employees.. but i run into problems while trying to insert new males into the maleEMP table and them actually appear in the maleEMP table. I think there is some form of constraint i should be looking for? I've investgated FORCE and CHECK but that didn't seem like the right approach.

Any insight would be nice :)

this is just for study

Answer 1

Don't confuse the terms "table" and "view". A table is a data structure in the database. A view is just a SELECT statement which has been stored in the database. Some databases (such as Oracle) allow you to insert or update through a view - but only sometimes, and then under limited circumstances.

As to "How can I make this work?", you'd need to create an INSTEAD OF INSERT trigger on the view which could then redirect the INSERT to the appropriate table, adding values for the missing fields as needed. WARNING: this is a really incredibly bad idea which proves the old adage about "Just because you can do something doesn't mean you should do something". This is the kind of thing which turns into a twisted-spaghetti kind of architecture (if you can call it that) where triggers fire other triggers which call other code which fire other triggers... Seriously, don't go there. Insert into the correct table in your code. It will save you innumerable hours of debugging.

Best of luck.

Answer 2

To view your constraints (without SQL developer), use:

select *
from all_constraints
where table_name like '%EMPLOYEES%'

Answer 3

A constraint might prevent you from adding new rows to the underlying table by modifying the view data.

For example, there might be a constraint that the gender must be one of 'M' or 'F', and when you insert into the male_emp view that value is not being supplied. But you should be getting an error thrown, in that case.

A common way of getting round such problems would be with an INSTEAD OF trigger, which allows you to intercept DML on views and do whatever you like with the change.