Link to home
Start Free TrialLog in
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

asked on

Foreign key based on view

I've been looking at this way to long or had way too many beers so decided to burn some points.  All the solutions I can come up with are way too complex and I'm convinced there is an easy one.

I'm working on an app that has different types of users.  For each type I need to collect different attributes.  The only column they really have in common is username and password.  I really don't want to combine the tables since they really don't have enough in common (keep internal employees and external users in seperate tables).

I have another business rule that the usernames are unique across the app.  So far, I have triggers on the tables to verify they are unique.

Are you still with me?  I hope so.

Now the issue:

I have another table, lets call it an audit table.  I would like to create a foreign key on the audit table that will verify the user exists before insert (I'm old fashoned).

I'm on 10g (10.1.0.4) and a simplified test case will follow.  Before insert into audit_log, make sure the username exists in either tab_1 or tab_2.  

Note:  I know I can trick it by creating another view on audit and use 'instead of' triggers but don't really like that option either.

I think I've covered everything.  I reserve the right to change my requirements later!!!!
------------------------------------------------------------------------
drop table user_tab_1;
create table user_tab_1 (username char(1) primary key);

drop table user_tab_2;
create table user_tab_2 (username char(1) primary key);

insert into user_tab_1 values('a');
insert into user_tab_2 values('b');
commit;

create or replace view all_users(username)
as
select username from user_tab_1
union
select username from user_tab_2
/



drop table audit_log;
create table audit_log
(
      username,      --FOREIGN KEY
      constraint audit_user_fk foreign key (username) references all_users(username)
)
/

SOLUTION
Avatar of paquicuba
paquicuba
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

ASKER

Thanks for the post.  I've never read up on the CHECK OPTION before.  You really can learn something new every day!!!!

However...
Unless I still really don't understand where you're going (which is very possible).  If I really can't control it at the DB level and have to rely on developers proper use of insert statements to ensure integrity,  I'll probably just remove the foreign key constraint all together (you can never trust programmers.....shhh.....  don't tell them I said that!!!).
I won't tell them   ; )

I use WITH CHECK OPTION in forms a lot, to avoid similar situations.

Try giving developers an updatable View:

SQL> SELECT * FROM USER_TAB_1;

U
-
1
3

SQL> SELECT * FROM USER_TAB_2;

U
-
2
4

SQL> SHOW USER
USER is "ALEX"
SQL> DELETE FROM AUDIT_LOG;

1 row deleted.

SQL> REVOKE INSERT ON AUDIT_LOG FROM TESTUSER;

Revoke succeeded.

SQL> CREATE OR REPLACE VIEW AUDIT_LOG_INSERT( USERNAME ) AS SELECT USERNAME FROM AUDIT_LOG WHERE
  2  USERNAME IN( SELECT USERNAME FROM USER_ALL) WITH CHECK OPTION
  3  /

View created.

SQL> CREATE PUBLIC SYNONYM AUDIT_LOG_INSERT FOR AUDIT_LOG_INSERT;

Synonym created.

SQL> GRANT INSERT ON AUDIT_LOG_INSERT TO TESTUSER;

Grant succeeded.

SQL> CONN TESTUSER/TESPSWRD@PROD
Connected.
SQL> SHOW USER
USER is "TESTUSER"
SQL> INSERT INTO AUDIT_LOG_INSERT VALUES('1');

1 row created.

SQL> INSERT INTO AUDIT_LOG_INSERT VALUES('5');
INSERT INTO AUDIT_LOG_INSERT VALUES('5')
            *
ERROR at line 1:
ORA-01402: view WITH CHECK OPTION where-clause violation             ---<<---- WITH CHECK OPTION fires


SQL> INSERT INTO AUDIT_LOG VALUES('2');
INSERT INTO AUDIT_LOG VALUES('2')
            *
ERROR at line 1:
ORA-00942: table or view does not exist                               --<<--- Table cannot be accessed by developers


SQL>
Thanks for the clarification.  I'm thinking I might have to do that.

I'll leave it open a while longer just in case there are other tricks out there.
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
thanks doc.  

>>as a developer...
I've been more of a developer than a DBA the past few years.  I really hate to admit this but.....   I'm actually the developer on the app that needs this, so design changes aren't a problem.

Give me some time to digest this (it will probably be over the weekend).  After a quick scan:  It does look a bit cleaner than anything I've come up with.
slightwv,
    No rush.  Also, just to be thorough, the relationship between USERS and the EXTERNAL/EMPLOYEE entities is obviously an "exclusive arc" on an E-R diagram of the logical-to-physical map layer (as it would be with any super entity instantiated as more than one object).


Good luck!
DrSQL
thx.  E-R diagram?  who uses those things any more?    :)

then again.....  if I had done ALL my homework on the app before I started coding I probably wouldn't have had to ask this question......

To others that may stumble across this question later:
Let this be a lesson for you!!!  

No matter how many layers of management are breathing down your neck to get it done: TAKE TIME FOR PROPER ANALYSIS AND DESIGN!!!!!!  It will save you A LOT of time and headaches down the road.
slightwv (and the "theoretical" people you are yelling at),

   "Referential Integrity" can also be an oxymoron or a statement of values.  Depends on which moron is doing the referring.

Good luck!
DrSQL

P.S.  Have a great weekend.
ROTFL!!!!

>>Have a great weekend
I'm getting ready to start mine.  I'll type at ya Monday.
paquicuba,
you get partial points for teaching me something new.  

DrSQL,
After several beers, I decided to go with your solution and redesigned the base tables.  It was the cleaner approach.  So far, it's working great.

Thanks for the help.     Even though I hate to give points to you since you'll soon pass me...    :)
slightwv,
   Happy to help, even if it was mostly an excuse to practice your particular wizard skill.  And we're months away from any position changes.  Its tough to be top scorer AND page editor, we all appreciate your efforts.

Good luck!
DrSQL

P.S.  Did you notice there's now someone who can offer "sage" advice?
>>Did you notice there's now someone...

nope.  missed that one.  Thanks for pointing it out.
I'm glad I did teach you something, 'cause I'm the one always learning from you guys.

Great job Dr...!!

Let's see between the two of you who gets to the "sage" level first....   : )  

 
>>Let's see between the two of you

I vote for the good doctor!!!!!
paquicuba and slightwv,
   Thank you.  However, my likely pace puts me right at the end of the year for Sage and slightwv is closer to late November or early December.  I mean, it's almost 100k difference at this point - that's 20% of the whole.  I'll do my part to make it close, but I say we avoid any self-fulfilling prophesies and enjoy the ride!

Good luck!
DrSQL

Oh, and one more thing I noticed on the lists - This is the first month where everyone on the "Overall" list is at least a Guru.  I'll be sad to see dbalaski drop off - he was struggling to stay active when I was first on EE (baby and job demands), but had great enthusiasm and knowledge.  He earned his 150k when there was a LOT less traffic and 300 points was the max (maybe even lower for part of his time).  We need a veterans committee Hall of Fame induction process.