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
Solved

Foreign key based on view

Posted on 2005-08-04
17
10,737 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
  • 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 76

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 76

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 76

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 76

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 76

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 76

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 76

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 76

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Create table from select - oracle 6 53
Create Index on a Materialized View 5 32
ORA-02288: invalid OPEN mode 2 56
Read XML values 8 41
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

808 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