Any MySQL-Front of SQLyog Users?

[geosite]

Can anyone tell me how to create a foreign key with either MySQL-Front or SQLyog? I've just about given up on phpMyAdmin. I started using MySQL-Front because it lets me import data (something else I can't do with phpMyAdmin), and I just discovered SQLyog, which looks even better.

But neither one offers clear instructions for creating a foreign key, and I haven't found much help on their websites. I can't access MySQL-Front's online forums, and SQLyog's forums look like they died a couple months ago.

I just wondered if someone could give me a step by step process for creating a foreign key with MySQL-Front or SQLyog. If not, can you recommend another program that can do the job (preferably free .

[r937]

i can give you a very simple step-by-step method for creating a foreign key

but as it involves just sql, you probably aren't interested, right?

[geosite]

i can give you a very simple step-by-step method for creating a foreign key

but as it involves just sql, you probably aren't interested, right?

If you're talking about a "command line" function, then it probably won't be of much help. That's a whole 'nother issue for me to tackle.

Still, if you don't mind posting it, I'd like to add it to my notes for future reference.

Also, I wondered if it's possible to download a MySQL table that has a foreign key already set up. If I had a working model, then I could simply copy and modify it.

[r937]

command line?

i have no such thing, i'm a windows user

i was talking about plain old ordinary sql that you feed into mysql via the phpmyadmin sql box, or the mysql-front query windowpane

"box" and "windowpane" aren't the right words, but you know what i mean

here's a little script i just wrote to demonstrate innodb can actually do ON DELETE CASCADE

create table categories 
( catid smallint not null auto_increment
, catname varchar(30) not null 
, primary key  (catid)
) type=innodb 
;
insert into categories (catname) 
values
 ('dogs')
,('cats')
,('elephants')
,('fish')
;
create table articles 
( artid smallint not null auto_increment
, artname varchar(30) not null 
, artcat smallint  null
, primary key (artid)
, index artcatindex (artcat)
, foreign key (artcat) references categories (catid)
       ON DELETE CASCADE
) type=innodb  
;
insert into articles (artname,artcat) 
values
 ( 'rover', 1)
,( 'ralph', 1)
,( 'garfield', 2)
,( 'morris', 2)
,( 'myguppy', 4)
;
select * 
  from categories c
left outer
  join articles a
    on c.catid = a.artcat
;
/*
catid,catname,artid,artname,artcat
1,dogs,1,rover,1
1,dogs,2,ralph,1
2,cats,3,garfield,2
2,cats,4,morris,2
3,elephants,NULL,NULL,NULL
4,fish,5,myguppy,4
*/
delete from categories
 where catname='cats'
;
select * 
  from categories c
left outer
  join articles a
    on c.catid = a.artcat
;
/*
catid,catname,artid,artname,artcat
1,dogs,1,rover,1
1,dogs,2,ralph,1
3,elephants,NULL,NULL,NULL
4,fish,5,myguppy,4
*/
select * from articles
;
/*
artid,artname,artcat
1,rover,1
2,ralph,1
5,myguppy,4
*/

[geosite]

Hold the press: I think I FINALLY succeeded.

I just figured out how to make foreign keys with SQLyog. So I used phpMyAdmin to make two brand new tables, with no data in them, then used SQLyog to connect the primary and foreign keys.

I haven't tested the tables yet, but it looks like they're connected. Now, if it takes me another three days to figure out each of the other parts of this operation, I'll be up and running by July!