Results 1 to 14 of 14
  1. #1
    Join Date
    Dec 2003
    Posts
    50

    Unanswered: Way to delete...

    Hi!

    Me again and my music problems !!

    I have three tables

    Interpreter(intName, nationality,...)

    Album(albName, albYear, label,...)

    Music(musName, musYear, genre,...)

    and another one that associates the 3 presented:

    releasedBy(musName, musYear, albName, albYear, intName, nTrack, nCd)

    I want that when I delete a Interpreter all the entries in table releasedBy related to that Interpreter are also deleted and the entries in tables Album and Music are also deleted.

    I can't delete the albums and musics from their tables having entries in releasedBy with that values because that entries are child registers from the tables Album and Music. So,

    Code:
    DELETE FROM album WHERE albName IN (SELECT albName FROM releasedBy WHERE intName='Frank Sinatra');
    
    DELETE FROM music WHERE musName IN (SELECT musName FROM releasedBy WHERE intName='Frank Sinatra');
    doesn't work.

    I have to delete first the entries in releasedBy but if I do that how do I know, then, what are the musics and albuns in tables MUsic and Album that belonged to that interpreter in order to delete them? That information is only in table releasedBy.

    Is it possible to do this at once?

    Thans for your attention.
    Ah! Não ser eu toda a gente e toda a parte!

  2. #2
    Join Date
    Sep 2003
    Location
    Milan, Italy
    Posts
    130
    Use a DEFERRED constraint:

    SQL> create table parent (id integer primary key);

    Table created.

    SQL> create table child (id references parent(id) DEFERRABLE);

    Table created.

    SQL> insert into parent values (1);

    1 row created.

    SQL> insert into child values (1);

    1 row created.

    SQL> delete from parent;
    delete from parent
    *
    ERROR at line 1:
    ORA-02292: integrity constraint (DELLERA.SYS_C002575) violated - child record
    found


    SQL> set constraints all deferred;

    Constraint set.

    SQL> delete from parent;

    1 row deleted.

    SQL> delete from child;

    1 row deleted.

    SQL> commit;

    Commit complete.

    HTH
    Alberto

  3. #3
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    You could also set a foreign key from the child to the parent table with a delete cascade option ... When the parent is deleted, the child will also get deleted

    HTH
    Gregg

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Way to delete...

    Alberto's deferred constraints will resolve your specific issue, but I have to say that I am confused by your database design. Can one Album have ReleasedBy records (no pun intended!) for more than one Interpreter? If so, you do not want to delete the Album when some but not all of its associated ReleasedBys are deleted! On the other hand, if each Album is related to only one Interpreter, then your design is incorrect (there should be a foreign key from Album to Interpreter, and no foreign key from ReleasedBy to Interpreter).

    Similar comments apply to table Music.

    Assuming your design is correct, i.e. an Album may be associated with more than one Interpreter, then you only want to delete Album and Music records that have no associated ReleasedBys once the Interpreter and his ReleasedBys have been deleted. That can be done without deferred constraints like this:

    delete ReleasedBy where intName = 'Frank Sinatra';
    delete Interpreter where intName = 'Frank Sinatra';
    delete Album a where not exists (select 1 from ReleasedBy r where r.AlbName = a.AlbName and r.Albyear = a.AlbYear);
    delete Music m where not exists (select 1 from ReleasedBy r where r.MusName = m.MusName and r.Musyear = m.MusYear);

    With an ON DELETE CASCADE on the foreign key from ReleasedBy to Interpreter, this could be simplified to:

    delete Interpreter where intName = 'Frank Sinatra';
    delete Album a where not exists (select 1 from ReleasedBy r where r.AlbName = a.AlbName and r.Albyear = a.AlbYear);
    delete Music m where not exists (select 1 from ReleasedBy r where r.MusName = m.MusName and r.Musyear = m.MusYear);

  5. #5
    Join Date
    Dec 2003
    Posts
    50

    Re: Way to delete...

    Originally posted by andrewst
    Alberto's deferred constraints will resolve your specific issue, but I have to say that I am confused by your database design. Can one Album have ReleasedBy records (no pun intended!) for more than one Interpreter?


    No, one album identified by its name and its year can only have one interpreter. But of course there will be as many entries in releasedBy for one album of an interpreter as the number of musics in that album. Then, I want to delete the album when I delete the interpreter.

    If so, you do not want to delete the Album when some but not all of its associated ReleasedBys are deleted! On the other hand, if each Album is related to only one Interpreter, then your design is incorrect (there should be a foreign key from Album to Interpreter, and no foreign key from ReleasedBy to Interpreter).



    Similar comments apply to table Music.
    I think you are right! I have three entities in the entitie-association model (Music, Album and Interpreter) related by the ternary association releasedBy:

    releasedBy(Music, ALbum, Interpreter) N:N:N total/total/partial

    The conversion to the relational model took me to that design. So, I had to have:

    releasedBy(Interpreter, Album) N:1 partial/total
    belongsTo(Music,Album) N:N partial/total

    in order to have a correct design, I think

    Assuming your design is correct, i.e. an Album may be associated with more than one Interpreter, then you only want to delete Album and Music records that have no associated ReleasedBys once the Interpreter and his ReleasedBys have been deleted. That can be done without deferred constraints like this:

    delete ReleasedBy where intName = 'Frank Sinatra';
    delete Interpreter where intName = 'Frank Sinatra';
    delete Album a where not exists (select 1 from ReleasedBy r where r.AlbName = a.AlbName and r.Albyear = a.AlbYear);
    delete Music m where not exists (select 1 from ReleasedBy r where r.MusName = m.MusName and r.Musyear = m.MusYear);

    With an ON DELETE CASCADE on the foreign key from ReleasedBy to Interpreter, this could be simplified to:

    delete Interpreter where intName = 'Frank Sinatra';
    delete Album a where not exists (select 1 from ReleasedBy r where r.AlbName = a.AlbName and r.Albyear = a.AlbYear);
    delete Music m where not exists (select 1 from ReleasedBy r where r.MusName = m.MusName and r.Musyear = m.MusYear);
    Thanks for all the answers guys.
    Ah! Não ser eu toda a gente e toda a parte!

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Way to delete...

    Sounds to me more like your data model should be:

    Interpreter(intName, nationality,...)
    Album(albName, albYear, label, intName, ...)
    Music(musName, musYear, genre,...)
    AlbumTrack(musName, musYear, albName, albYear, nTrack, nCd)

    But this still leaves some doubt in my mind about what Music represents: is it a generic song like "My Way", or is it a specific recording of the song like "Frank Sinatra's version of My Way recorded in 1972" (or whenever it was)? If the latter then the data model becomes:

    Interpreter(intName, nationality,...)
    Album(albName, albYear, label, intName, ...)
    Music(musName, musYear, genre, intName, ...)
    AlbumTrack(musName, musYear, albName, albYear, nTrack, nCd)

    ... with a rule (not enforceable by Oracle constraints) that the Music on an AlbumTrack must be associated with the same Interpreter as the Album.

  7. #7
    Join Date
    Dec 2003
    Posts
    50
    I'm sorry, what does SELECT 1 ... make ?

    Thanks again!
    Ah! Não ser eu toda a gente e toda a parte!

  8. #8
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    I just use "SELECT 1" when I mean "select anything" -- i.e. all I care about is that the row exists, I don't care what is in it. I could equally well have said "SELECT albName" or "SELECT 'foo'" or "SELECT NULL". "SELECT 1" is just less typing!

    SQL forces you to select something in the subquery, even though what I really want to say is:

    -- Invalid syntax:
    delete Album a where not exists (ReleasedBy r where r.AlbName = a.AlbName and r.Albyear = a.AlbYear);

  9. #9
    Join Date
    Sep 2003
    Location
    Milan, Italy
    Posts
    130
    Originally posted by rpOliveira
    I'm sorry, what does SELECT 1 ... make ?
    "exists" and "not exists" are only concerned with their argument existing (having at least one row) or not - "1" is just a dummy value, any other value will do (even null)

    SQL> select * from dual where exists (select 1 from dual where 1=1);

    DUM
    ---
    X

    SQL> select * from dual where exists (select 2 from dual where 1=1);

    DUM
    ---
    X

    SQL> select * from dual where exists (select null from dual where 1=1);

    DUM
    ---
    X


    I.e. where exists (<query>) will evaluate to true if <query> returns at least one row, <false> if it returns zero rows. The actual value returned doesn't matter.

    Alberto

  10. #10
    Join Date
    Dec 2003
    Posts
    50
    Thanks. I understood your explanations but this last one has arisen me another doubt:

    What dual means? What it represents?
    Ah! Não ser eu toda a gente e toda a parte!

  11. #11
    Join Date
    Jan 2004
    Location
    India
    Posts
    62

    Thumbs up

    Hi!
    Dual is a special table in Oracle, which has a single column with single row.
    When you want to select something which is not in a physical table, (some expression or system date like)
    you have to write a select statement. i.e. there must be a from clause.
    If you select the expression or system date from a physical table, it will display the result for the no of rows times.
    Instead you can select the expression or system date from dual.
    So you will get the result only once as dual has a single row.
    select sysdate from dual;
    select 12*12 from dual.
    Regards,

    Rushi

  12. #12
    Join Date
    Jan 2004
    Posts
    24
    Originally posted by rpOliveira
    Thanks. I understood your explanations but this last one has arisen me another doubt:

    Hi,

    The Dual table is a table with one column and one row used to fulfill
    the syntactic requirements of sql select statements.The dual table is
    owned by Sys and is used when all data to be processed by the query
    is present in the statement and no data will be pulled from the database
    for ex:
    select 2+2 from dual;
    select user from dual;


    Regards
    Uma



    What dual means? What it represents?

  13. #13
    Join Date
    Nov 2003
    Location
    Bangalore, INDIA
    Posts
    333

    Thumbs up

    Hi,

    DUAL is used to satisfy the SQL syntax construct Stating that all SQL statements must contain a from clause that names the table from which the data will be selected. When a user does not want to pull data from any table, but rather wants simply to use an arithmetic operation on a constant value, he can include the values, operations, and the from DUAL clause.
    SATHISH .

  14. #14
    Join Date
    Dec 2003
    Posts
    50
    OK. Thanks for the answers!
    Ah! Não ser eu toda a gente e toda a parte!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •