Solved

Foreign key based on view

Posted on 2005-08-04
17
10,764 Views
Last Modified: 2011-08-18
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)
)
/

0
Comment
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 6
  • 3
17 Comments
 
LVL 23

Assisted Solution

by:paquicuba
paquicuba earned 50 total points
ID: 14604612
Use with check option when inserting:

SQL> INSERT INTO (SELECT USERNAME FROM AUDIT_LOG WHERE USERNAME IN( SELECT USERNAME FROM ALL_USERS) WITH CHECK OPTION) VALUES ( '1');
INSERT INTO (SELECT USERNAME FROM AUDIT_LOG WHERE USERNAME IN( SELECT USERNAME FROM ALL_USERS) WITH CHECK OPTION) VALUES ( '1')
                                  *
ERROR at line 1:
ORA-01402: view WITH CHECK OPTION where-clause violation


Elapsed: 00:00:00.07
SQL> INSERT INTO USER_TAB_1 VALUES('1');

1 row created.

Elapsed: 00:00:00.04
SQL> INSERT INTO (SELECT USERNAME FROM AUDIT_LOG WHERE USERNAME IN( SELECT USERNAME FROM ALL_USERS) WITH CHECK OPTION) VALUES ( '1');

1 row created.

Elapsed: 00:00:00.00
SQL> SELECT * FROM USER_TAB_1;

U
-
1
a

Elapsed: 00:00:00.06
SQL> SELECT * FROM AUDIT_LOG;

U
-
1

Elapsed: 00:00:00.01
SQL> INSERT INTO (SELECT USERNAME FROM AUDIT_LOG WHERE USERNAME IN( SELECT USERNAME FROM ALL_USERS) WITH CHECK OPTION) VALUES ( 'a');

1 row created.

Elapsed: 00:00:00.01
SQL> SELECT * FROM AUDIT_LOG;

U
-
1
a

Elapsed: 00:00:00.01
SQL> INSERT INTO (SELECT USERNAME FROM AUDIT_LOG WHERE USERNAME IN( SELECT USERNAME FROM ALL_USERS) WITH CHECK OPTION) VALUES ( '5');
INSERT INTO (SELECT USERNAME FROM AUDIT_LOG WHERE USERNAME IN( SELECT USERNAME FROM ALL_USERS) WITH CHECK OPTION) VALUES ( '5')
                                  *
ERROR at line 1:
ORA-01402: view WITH CHECK OPTION where-clause violation


Elapsed: 00:00:00.03
SQL> INSERT INTO USER_TAB_1 VALUES('5');

1 row created.

Elapsed: 00:00:00.00
SQL> INSERT INTO (SELECT USERNAME FROM AUDIT_LOG WHERE USERNAME IN( SELECT USERNAME FROM ALL_USERS) WITH CHECK OPTION) VALUES ( '5');

1 row created.

Elapsed: 00:00:00.01
SQL> SELECT * FROM AUDIT_LOG;

U
-
1
a
5

Elapsed: 00:00:00.01
0
 
LVL 77

Author Comment

by:slightwv (䄆 Netminder)
ID: 14607742
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!!!).
0
 
LVL 23

Expert Comment

by:paquicuba
ID: 14608746
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>
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 77

Author Comment

by:slightwv (䄆 Netminder)
ID: 14609349
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.
0
 
LVL 22

Accepted Solution

by:
DrSQL earned 450 total points
ID: 14609782
slightwv,
   I think I might go back to your initial physical design to answer this one.  Basically, you had a superentity (we'll call it USERS) that had two subentities (we're calling those EMPLOYEES and EXTERNAL).  You chose to use application logic to distinguish the entity type and created the subentities as distinct entities (tables).  I can understand your reluctance to create them as a superentity with a deterministic type code (putting it all in one table when there are almost NO columns in common).  There is, however, another option.  This is to use surrogate-key normalization of the superentity - which means you create all three:

USERS (username, password, users_type, surrogate_key_id) primary key surrogate_key_id, unique key username
EMPLOYEES(surrogate_key_id references USERS, <other EMPLOYEE columns except username/pass>) primary key surrogate_key_id
EXTERNAL(surrogate_key_id references USERS, <other EXTERNAL columns except username/pass>) primary key surrogate_key_id

That maintains the rule you wanted.  You can also either adapt your code to take advantage of the fact that you havea single table for password information, or create a updateable view that looks EXACTLY like your current two tables.  That would mean NO CHANGES to apps and no need to trust the developers (and, as a developer, I can say that with a straight face).

create or replace view internal_employees updateable /* I think that's the syntax */ as
    select username,password,<employee columns> from USERS, EMPLOYEES
      where users.surrogate_key_id = employees.surrogate_key_id;

Obviously, you'd also need a sequence generator, but you could do that in a pre-insert trigger on the USERS table - so again, no impact on development.

Good luck!
DrSQL
0
 
LVL 77

Author Comment

by:slightwv (䄆 Netminder)
ID: 14610068
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.
0
 
LVL 22

Expert Comment

by:DrSQL
ID: 14610150
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
0
 
LVL 77

Author Comment

by:slightwv (䄆 Netminder)
ID: 14610389
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.
0
 
LVL 22

Expert Comment

by:DrSQL
ID: 14610581
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.
0
 
LVL 77

Author Comment

by:slightwv (䄆 Netminder)
ID: 14611016
ROTFL!!!!

>>Have a great weekend
I'm getting ready to start mine.  I'll type at ya Monday.
0
 
LVL 77

Author Comment

by:slightwv (䄆 Netminder)
ID: 14624122
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...    :)
0
 
LVL 22

Expert Comment

by:DrSQL
ID: 14624582
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?
0
 
LVL 77

Author Comment

by:slightwv (䄆 Netminder)
ID: 14624834
>>Did you notice there's now someone...

nope.  missed that one.  Thanks for pointing it out.
0
 
LVL 23

Expert Comment

by:paquicuba
ID: 14625052
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....   : )  

 
0
 
LVL 77

Author Comment

by:slightwv (䄆 Netminder)
ID: 14625402
>>Let's see between the two of you

I vote for the good doctor!!!!!
0
 
LVL 22

Expert Comment

by:DrSQL
ID: 14625500
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
0
 
LVL 22

Expert Comment

by:DrSQL
ID: 14626542

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.
0

Featured Post

Enroll in June's Course of the Month

June's Course of the Month is now available! Every 10 seconds, a consumer gets hit with ransomware. Refresh your knowledge of ransomware best practices by enrolling in this month's complimentary course for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

724 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question