Re: Set Null constraints in Oracle 8.0.5?????

Hi,

I have 12 years experince as a DBA, starting from Oracle 5, all the way up to Oracle 8.0.5 and a few days short of one year with Oracle8i. This, I hope, gives some credibility to what I have to say. However, we are all different ad prefer different solutions which are equally good, so please do not take the following as the ONLY way to go.

This, at the time of creation, is reply #3. The other two are right but perhaps it helps to make it quite clear that Oracle does not support the SET NULL option.

Therefore I woud not recommend the ON DELETE CASCADE option, because, obviously, your intent with the SET NULL option would be to keep the child record with a NULL child ID, even though its dad is deleted. The ON DELETE CASCADE option would make it possible to delete the dad, but it would also delete the child.

What I would suggest is something that first makes sure that a.) the child is kept (not necessarily with a father_id of NULL), and b.) the parent goes away. Please do not read on if this not what you want.

Since Oracle does not support this directly (in Oracle a foreign key constraint makes it mandatory for a child to have a parent - the ideal world that we all dream of), we will have to have a dummy parent (say father_id=0) in the father table who is the happy adopter of any child who is about to go orphan. Then we create some kind of a procedure which makes sure that it catches the event of the deletion of the father and before that delete actually executes, it gives up the child(ren) for adoption to father_id=0. Then it lets the deletion of the original father proceed.

How to implement this? A trigger is tipically the way to do something like this (catch an event and run before or after the event, as you specify it), but in this case that is not the right solution because the trigger would have to read from the child table in order to find the children to be given up for adoption. In other words, the trigger is trying to read from a table which may be affected by the pending operion. This causes a mutating table error.

So, what I suggest is to implement a procedure (better yet, a packaged procedure), which, when called, does exactly what it needs to do. This procedure would have to be the only way to access these tables. To achieve this, you may have to request that the developers keep this in min mind. However, ideally, if you have the resources and the time, you may want to apply this design principle to the whole database. This may be expensive and lengthy, depending on how many and how complicated the applications are that use the database. This is because you would have to implement a database interface which is the ONLY ACCESS to the database.

How to write a single (packaged) procedure to do this depends on things like how many rows you (expect to) have in the father table and in the child table, so I cannot make any suggestion now. The Oracle documentation is pretty good in the PL/SQL, procedure, function, package header and package areas, with enough example to get the idea.

If, in your current business situation, your higher management can and does opt for the more resource hungry, but later much easier to maintain (therefore it saves TONS OF $$$ on the long-run, because maintenance costs are orders of magnitude higher than design or even development costs) and way more secure solution, then I would suggest the design (test, document, etc.) and implementation of a generic database access interface concept. (Resolving this situation puts responsibilities on both the DBA and the management of the company. I think, the DBA's responsibility is to provide summary AND detailed information to the management team in order for them to be well informed, thus giving them grounds to evaluate the situation from business, financial, technical, funding, long term planning and many other aspects. I think, the management team's responsibility is to use the information provided by the DBA and other department leaders and, ultimately, to make their decision.)

Genereic database access interface concept:

1. There is one schema called something like sch_security ("sch_" is a naming convention I use for schemas). The purpose is to login before the user logs in and check in tables of this schema (and return some value indicating) if the username and password given passes authorization. The password for this schema is super secret, therefore an encryption algorythm must be used and the application developers should only know the encrypted version.
2. There is one schema (say sch_dbint) containing the database interface. The purpose is twofold. a.) store the packagages, which make up the database interface and b.) for each application or user to log in to this schema alone. Passwords should be established for each application and within that, for each user of the application.
3. There is one schema (say sch_app) where the application's (or applications') actual database objects are stored along with the actual data. The purpose is application data storage. The password is super secret. Not to be encrypted, because it is absolutely not to be known by anyone but the dbpasswordgroup, the members of which are the DBA and (a strictly and very well maintained list of members of) upper management (in case something happens to the DBA). Each member of the dbpasswordgroup must be notified if the member list changes, so that each one of them knows exactly, who currently know this password.

Here is how it all works (login process flow):

Overview:

1. Login to the security layer. Check if the if the attempt is secure. If not, terminate the process.
2. Login to the database interface layer. If it fails, terminate the process.

Details:

1. A user initiates a login through an application login window (LOGINOBJECT) which is responsible for a.) gathering four pieces of information: username, userpassword, applicationname, applicationpassword and b.) executing its private login methos. The user types in their username and password. The application (which presents the login window to the user) is responsible for knowing its own name and password.
2. The LOGINOBJECT executes its FIRST METHOD: Logs in to the database using the the username sch_security and the corresponding encrypted password. a.) If the login failes, it returns an "Oracle not available" error and terminates the login process. b.) If the login succeeds it calls the packaged function sch_Security.pg_Login.f_Login, passing it the four parameters it collected. The function returns 0 for "OK, GO AHEAD" and an error value in case one or more of the four passed parameters is/are invalid. (For example add up these numbers to calculate the return value: 1 for invalid username, 2 for invalid userpassword, 4 for invalid applicationname, 8 for invalid applicationpassword.) This value can be further processed if necessary.
3. The LOGINOBJECT executes its SECOND METHOD: Logs out of the sch_security schema. If it is unsuccessful, it gives an error message and terminates the login process. If it is successful it simply lets the login process continue.
4. The LOGINOBJECT executes its THIRD METHOD: Logs in to Oracle, using the username sch_dbint and the corresponding password. Then it calls the sch_dbint.pg_Login.f_Login packaged function passing it the four parameters it has collected. If an error occures it sends an error message, which refers the user to the DBA, who can then investigate why the login is unsuccessful. After sending the error message, it terminates the login process.
5. Now, that the application has a valid connection, the user can invoke any packaged procedure or packaged function, which, in turn, will access and manipulate the actual application data in the sch_app schema.

Notes:

1. The sch_app schema must give appropriate privileges to the sch_dbint schema.
2. You may have more than one application schemas (for example: sch_dev, sch_qa, sch_test, sch_prod). If you do, the interface must execute dynamic SQL, depending on the schema being used. This has very little overhead, therefore unnoticable performance hit, because the actual package contents are stored in a compiled format in the database, so no parsing, compilation or access methods need to be worked out at runtime by Oracle.

I hope this helps, good luck,

Gabor

Carole Darche <cdarche_at_ispalliance.net> wrote in message news:38B5FEB9.570444BC_at_ispalliance.net...
> Hi all,
>
> Our D.B.A. is having a very strange problem with foreign keys in Oracle
> 8.0.5.
>
> The object is to create a set null constraint, so she followed the
> following procedure
>
> create father and child tables
>
> desc father;
> Column Name Null? Type
> ------------------------------ -------- ----
> FATHER_ID NOT NULL NUMBER
> BIDON VARCHAR2(2)
>
> desc child;
> Column Name Null? Type
> ------------------------------ -------- ----
> CHILD_ID NOT NULL NUMBER
> FATHER_ID NUMBER
> TEXT VARCHAR2(2)
>
> alter table child
> add constraint fk1_child foreign key (father_id) references father
> (father_id);
>
> delete from father where father_id=1;
> ORA-02292: integrity constraint (AMSUSER.FK1_CHILD) violated - child
> record found
>
> How to work our way arround this, How oracle is handling the set null
> constraints?????
>
> Please reply to the following email address: cdarche_at_america.net Or
> belbagri_at_america.net
>
> Thanks for your help
>
>
Received on Sun Mar 05 2000 - 11:43:55 CST