Thread: Oracle newbie Kim
12-06-05, 15:26 #1Registered User
- Join Date
- Dec 2005
Unanswered: Oracle newbie Kim
Hello, I am working on a database which is designed for dvds, two tables which are directors and movies are joined together by the foreign key director_No. However I would like to add a clause so that in the database a director has to have at least one movie. I have tried to implement this using triggers and procedures but have failed.
I made the code below but it doesn't work and I also had another procedure which inserted data into the database and if a director had no movies the director would be removed. Any help/advice would be great. Thank you Kimmi
CREATE OR REPLACE PROCEDURE count_MOVIES(dir_no IN INTEGER, count_test OUT INTEGER, min_no OUT INTEGER)
min_no := 1;
WHERE director_no = dir_no;
CREATE OR REPLACE TRIGGER movie_needed
FOR EACH ROW
count_movies(:NEW.director_no, dir, min_no_movies);
IF dir < min_no_movies THEN
12-06-05, 15:54 #2Registered User
- Join Date
- Jun 2004
- Liverpool, NY USA
I am not sure why you want to enforce such a silly rule, how are you every going to be able to enter a movie for a new director without adding the director record first. However if after all the movies and directoriers are added in and you want to perform cleanup, then do the following
delete directors a
where not exists
from movies b
where a.director_no = b.director_no);Bill
You do not need a parachute to skydive. You only need a parachute to skydive twice.
12-06-05, 16:10 #3Lost Boy
Provided Answers: 4
- Join Date
- Jan 2004
- Croatia, Europe
First of all, why would you want to delete directors? If you delete all his movies, are you sure you won't have another one soon? This would require you to enter all his data again. And again. And again. Space requirements certainly shouldn't be a problem so - I'm just interested - why?
This (deleting directors if their movies are deleted) can be done your way; procedure code could be moved into the trigger code to simplify the whole thing, but that would finally lead you to a mutating table error which, of course, can be solved, but the question is: is this worth all the pain?
To me, it seems that your model is twisted: movies are parents and directors are children. I think that it would be more logical if it was opposite - every director can have one or many movies, while every movie has one director.
The code you posted doesn't make much sense; it doesn't follow your theory which says that "no director can exist without at least one movie". Trigger checks whether a NEW inserted director has records in the database. Why would you do that? There is always a "first time" when a director is inserted (with his/her first movie in the database).
Therefore, I'd suggest you to check documentation about referential constraints, especially the ON DELETE CASCADE option. Shortly, the ON DELETE CASCADE clause tells Oracle to delete the child tables when the corresponding parent table row is deleted. If the ON DELETE CASCADE clause is left off, Oracle will not allow the deletion of a parent row unless all the child rows are deleted first.
This would allow deleting all movies of a certain director in "my" model, using two simple CREATE TABLE statements and no additional coding, such as:Code:
CREATE TABLE DIRECTOR (dir_id NUMBER PRIMARY KEY, dir_name VARCHAR2(10) ); CREATE TABLE MOVIE (mov_id NUMBER PRIMARY KEY, mov_NAME VARCHAR2(10), dir_id NUMBER CONSTRAINT fk_dir REFERENCES DIRECTOR (dir_id) ON DELETE CASCADE );
12-08-05, 12:50 #4Registered User
- Join Date
- Jul 2003
Every movie can have 1 or MORE directors.
You should account for this possibility.- The_Duck
you can lead someone to something but they will never learn anything ...