Page 1 of 2 12 LastLast
Results 1 to 15 of 28
  1. #1
    Join Date
    Jun 2005
    Posts
    21

    Unanswered: cascade worked under oracle but mysql?

    this should result in empty tables.

    Once worked under Oracle but now does not work under MS SQL Server 2005 and mysql

    alter table pets_like_boys
    drop constraint pets_like;

    alter table girls_like_pets
    drop constraint girls_like;

    alter table boys_like_girls
    drop constraint boys_like;

    drop table pets_like_boys;
    drop table boys_like_girls;
    drop table girls_like_pets;

    - first time start here

    create table pets_like_boys (pets_name varchar(32), boys_name varchar(32),primary key (pets_name));
    create table boys_like_girls (boys_name varchar(32), girls_name varchar(32),primary key (boys_name));
    create table girls_like_pets (girls_name varchar(32), pets_name varchar(32),primary key (girls_name));

    insert into pets_like_boys values('fluffy', 'sam');
    insert into pets_like_boys values('rover', 'bob');
    insert into pets_like_boys values('skippy', 'joe');

    insert into boys_like_girls values('bob','jill');
    insert into boys_like_girls values('sam','betty');
    insert into boys_like_girls values('joe','sue');
    insert into boys_like_girls values('george','jill');

    insert into girls_like_pets values('sue','fluffy');
    insert into girls_like_pets values('jill','skippy');
    insert into girls_like_pets values('betty','rover');

    alter table pets_like_boys
    add constraint pets_like
    foreign key (boys_name )
    references boys_like_girls
    on delete cascade;

    alter table boys_like_girls
    add constraint boys_like
    foreign key (girls_name )
    references girls_like_pets
    on delete cascade;

    alter table girls_like_pets
    add constraint girls_like
    foreign key (pets_name )
    references pets_like_boys
    on delete cascade;


    select * from pets_like_boys;
    select * from boys_like_girls;
    select * from girls_like_pets;
    delete from boys_like_girls where boys_name = 'bob';
    select * from pets_like_boys;
    select * from boys_like_girls;
    select * from girls_like_pets;

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    ur doing it wrong lol

    dey gots to be innodb tables, i bets your default is myisam
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2005
    Posts
    21

    how does one change that setting

    I am just starting to learn mysql

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    create table pets_like_boys 
    ( pets_name varchar(32)
    , boys_name varchar(32)
    , primary key (pets_name)
    ) engine=innodb;
    it's right there in the manual
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jun 2005
    Posts
    21

    I would expect cascade to work without any internal option change

    since cascade is not a obscure constraint

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    obviously you are new to mysql
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by http://dev.mysql.com/doc/refman/5.0/en/storage-engines.html
    If you omit the ENGINE or TYPE option, the default storage engine is used. Normally, this is MyISAM, but you can change it by using the --default-storage-engine or --default-table-type server startup option, or by setting the default-storage-engine or default-table-type option in the my.cnf configuration file.

    You can set the default storage engine to be used during the current session by setting the storage_engine or table_type variable:

    SET storage_engine=MYISAM;
    SET table_type=BDB;
    it's right there in da manual
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Jun 2005
    Posts
    21
    I'm sure it is in the manual. I should have read all 15,000 pages before trying a select statement. That makes sense doesn't it?

    OR

    By mental telepathy I should have known about myisam and lookd it up before using a plain old cascade constraint. That makes sense doesn't it?


    Again, why such an involved option change for a simple constraint?

    This make the SQL non-standard/not portable.

    Do you understand? This is a conceptual situation!

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by landonkelsey
    Do you understand? This is a conceptual situation!
    i conceptually understand that you are conceptually frustrated and you have my conceptual condolences

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Jun 2005
    Posts
    21

    curious!!

    I am an amateur psych, philos, theolog!

    Tell me something!

    (1) briefly, what college degrees do you have?

    (2) are you a programmer (C#, C++, PHP)?

    (3) what are your strengths outside of (I suppose) sql

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    1) bachelor of science, university of toronto
    2) no
    3) sweet, sweet lovin

    my turn to ask you some questions

    1) how old are you
    2) what country do you live in
    3) please describe the application you are building
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Jun 2005
    Posts
    21
    sorry!

    engine=innodb didn't help

    alter table pets_like_boys
    drop constraint pets_like;

    alter table girls_like_pets
    drop constraint girls_like;

    alter table boys_like_girls
    drop constraint boys_like;

    drop table pets_like_boys;
    drop table boys_like_girls;
    drop table girls_like_pets;

    create table pets_like_boys (pets_name varchar(32), boys_name varchar(32),primary key (pets_name))engine=innodb;
    create table boys_like_girls (boys_name varchar(32), girls_name varchar(32),primary key (boys_name))engine=innodb;
    create table girls_like_pets (girls_name varchar(32), pets_name varchar(32),primary key (girls_name))engine=innodb;

    insert into pets_like_boys values('fluffy', 'sam');
    insert into pets_like_boys values('rover', 'bob');
    insert into pets_like_boys values('skippy', 'joe');

    insert into boys_like_girls values('bob','jill');
    insert into boys_like_girls values('sam','betty');
    insert into boys_like_girls values('joe','sue');
    insert into boys_like_girls values('george','jill');

    insert into girls_like_pets values('sue','fluffy');
    insert into girls_like_pets values('jill','skippy');
    insert into girls_like_pets values('betty','rover');

    alter table pets_like_boys
    add constraint pets_like
    foreign key (boys_name )
    references boys_like_girls
    on delete cascade;

    alter table boys_like_girls
    add constraint boys_like
    foreign key (girls_name )
    references girls_like_pets
    on delete cascade;

    alter table girls_like_pets
    add constraint girls_like
    foreign key (pets_name )
    references pets_like_boys
    on delete cascade;


    select * from pets_like_boys;
    select * from boys_like_girls;
    select * from girls_like_pets;
    delete from boys_like_girls where boys_name = 'bob';
    select * from pets_like_boys;
    select * from boys_like_girls;
    select * from girls_like_pets;

  13. #13
    Join Date
    Jun 2005
    Posts
    21
    1) how old are you 67
    2) what country do you live in USA
    3) please describe the application you are building |this is an academic exercise

    to get to know mysql after Oracle and MS SQL server

    BTW I was going to ask! How old are you?

  14. #14
    Join Date
    Jun 2005
    Posts
    21
    may I probe further? a question from my academics

    (2) What normal form is violated here?
    A telephone call is uniquely identified by telephone number and time of call. These candidate keys compose the composite primary key. There is an attribute/column “telephone location”

  15. #15
    Join Date
    Jun 2005
    Posts
    21
    no research...just what you know

Posting Permissions

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