Issues with foreign key constraints and mysql

[Lars Ræder Clausen-2]

I'm trying to make a MySQL database, but keep running into some
strangeness with foreign keys.  I made a small example to show the
problem: A database of people and cars, where each person can own
multiple cars, but each car has but one owner.  I *think* I got the
diagram syntax right, but the resulting MySQL seems broken to my fairly
inexperienced eyes -- starting with comment marks and ending with 'for
mysql' and no semicolon?  Dia file and generated SQL file is attached.
If I'm modeling the objects wrongly, please correct me, as I'm not that
well versed in the correct DB jargon.

Thanks,
-Lars


[test.sql]

---

-- ================================================================================
--   mysql SQL DDL Script File
-- ================================================================================


-- ===============================================================================
--
--   Generated by:      tedia2sql -- v1.2.12
--                      See http://tedia2sql.tigris.org/AUTHORS.html for tedia2sql author information
--
--   Target Database:   mysql
--   Generated at:      Wed Nov  8 21:58:31 2006
--   Input Files:       test.dia
--
-- ================================================================================



-- Generated SQL Constraints Drop statements
-- --------------------------------------------------------------------
--     Target Database:   mysql
--     SQL Generator:     tedia2sql -- v1.2.12
--     Generated at:      Wed Nov  8 21:58:31 2006
--     Input Files:       test.dia

-- drop constraint carownership for mysql


-- Generated Permissions Drops
-- --------------------------------------------------------------------
--     Target Database:   mysql
--     SQL Generator:     tedia2sql -- v1.2.12
--     Generated at:      Wed Nov  8 21:58:31 2006
--     Input Files:       test.dia




-- Generated SQL View Drop Statements
-- --------------------------------------------------------------------
--     Target Database:   mysql
--     SQL Generator:     tedia2sql -- v1.2.12
--     Generated at:      Wed Nov  8 21:58:31 2006
--     Input Files:       test.dia



-- Generated SQL Schema Drop statements
-- --------------------------------------------------------------------
--     Target Database:   mysql
--     SQL Generator:     tedia2sql -- v1.2.12
--     Generated at:      Wed Nov  8 21:58:31 2006
--     Input Files:       test.dia

 drop table if exists person ;
 drop table if exists address ;


-- Generated SQL Schema
-- --------------------------------------------------------------------
--     Target Database:   mysql
--     SQL Generator:     tedia2sql -- v1.2.12
--     Generated at:      Wed Nov  8 21:58:31 2006
--     Input Files:       test.dia


-- person
create table person (
  personid                  int not null,
  name                      varchar(120),
  age                       int,
  constraint pk_Person primary key (personid)
) ;

-- address
create table address (
  carid                     int not null,
  model                     varchar(100),
  price                     int,
  ownerid                   int,
  constraint pk_Address primary key (carid)
) ;




-- Generated SQL Views
-- --------------------------------------------------------------------
--     Target Database:   mysql
--     SQL Generator:     tedia2sql -- v1.2.12
--     Generated at:      Wed Nov  8 21:58:31 2006
--     Input Files:       test.dia




-- Generated Permissions
-- --------------------------------------------------------------------
--     Target Database:   mysql
--     SQL Generator:     tedia2sql -- v1.2.12
--     Generated at:      Wed Nov  8 21:58:31 2006
--     Input Files:       test.dia



-- Generated SQL Insert statements
-- --------------------------------------------------------------------
--     Target Database:   mysql
--     SQL Generator:     tedia2sql -- v1.2.12
--     Generated at:      Wed Nov  8 21:58:31 2006
--     Input Files:       test.dia



-- Generated SQL Constraints
-- --------------------------------------------------------------------
--     Target Database:   mysql
--     SQL Generator:     tedia2sql -- v1.2.12
--     Generated at:      Wed Nov  8 21:58:31 2006
--     Input Files:       test.dia

-- alter table address add constraint carownership
  foreign key (ownerid) references
  person (personid) for mysql

---

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

[Mike Ginou]

Well, you're right there is a problem. The entire constraint section
should be commented out.

MySQL does not support foreign keys (note however that newer versions of
MySQL use the InnoDB format by default, so you may wish to to use '-t
innodb').

Hope that's helpful,
Mike

Lars Clausen wrote:

> I'm trying to make a MySQL database, but keep running into some
> strangeness with foreign keys.  I made a small example to show the
> problem: A database of people and cars, where each person can own
> multiple cars, but each car has but one owner.  I *think* I got the
> diagram syntax right, but the resulting MySQL seems broken to my fairly
> inexperienced eyes -- starting with comment marks and ending with 'for
> mysql' and no semicolon?  Dia file and generated SQL file is attached.
> If I'm modeling the objects wrongly, please correct me, as I'm not that
> well versed in the correct DB jargon.
>
> Thanks,
> -Lars
>
>
> ------------------------------------------------------------------------
>
> -- ================================================================================
> --   mysql SQL DDL Script File
> -- ================================================================================
>
>
> -- ===============================================================================
>
>
> ------------------------------------------------------------------------
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@...
> For additional commands, e-mail: users-help@...

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

[Mike Ginou]

Here's the quick patch that solves the error (not all lines of the alter
table command are properly commented).

There is another possible issue, that is that since innodb is now the
default for mysql, that perhaps the innodb settings should be used when
'-t mysql' is specified.

I tested the patch against the foreign-keys.dia example, but I didn't
run it against all of the database types. I also didn't actually run the
script against any database. Really though, the change is so minor that
I think it's pretty much good to go.

Cheers,
Mike

Lars Clausen wrote:

> I'm trying to make a MySQL database, but keep running into some
> strangeness with foreign keys.  I made a small example to show the
> problem: A database of people and cars, where each person can own
> multiple cars, but each car has but one owner.  I *think* I got the
> diagram syntax right, but the resulting MySQL seems broken to my fairly
> inexperienced eyes -- starting with comment marks and ending with 'for
> mysql' and no semicolon?  Dia file and generated SQL file is attached.
> If I'm modeling the objects wrongly, please correct me, as I'm not that
> well versed in the correct DB jargon.
>
> Thanks,
> -Lars
>
>
> ------------------------------------------------------------------------
>
> -- ================================================================================
> --   mysql SQL DDL Script File
> -- ================================================================================
>
>
> -- ===============================================================================
>
>
> ------------------------------------------------------------------------
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@...
> For additional commands, e-mail: users-help@...

Index: tedia2sql
===================================================================
RCS file: /cvs/tedia2sql/tedia2sql,v
retrieving revision 1.68
diff -u -8 -p -r1.68 tedia2sql
--- tedia2sql 19 Dec 2004 03:44:42 -0000 1.68
+++ tedia2sql 9 Nov 2006 03:44:11 -0000
@@ -2831,18 +2831,18 @@ sub createForeignKey($$$$$$) {
  # My ERD won't compile because of this, my PKs can't be null,
  # so they can't be set null. If you are a DB2 guru and really
  # want this, contact me to let me know how it's done
  $return  = "alter table $className add constraint $constraintName" . $nl;
  $return .= $in . "foreign key ($keyColumns)" . $nl;
  $return .= $in . "references $refTable ($refColumns) $goCommand";
  } elsif ($opt_t eq 'mysql' || $opt_t eq 'ingres') {
  $return  = &sqlComment("alter table $className add constraint $constraintName") . $nl;
- $return .= $in . "foreign key ($keyColumns) references" . $nl;
- $return .= $in . "$refTable ($refColumns) for $opt_t";
+ $return .= &sqlComment($in . "foreign key ($keyColumns) references") . $nl;
+ $return .= &sqlComment($in . "$refTable ($refColumns) for $opt_t");
  }
 
  return $return;
 }
 
 
 # drop a foreign key (constraint)
 


---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

[Time Less]

Here's the quick patch that solves the error (not all lines of the alter
table command are properly commented).

I should note that I find myself too busy these days to maintain this project. If anyone needs admin (CVS commit?) access, let me know, I'll figure out how to get you into the project admins.

I think there are about 3 patches outstanding that could be applied to the project, but just haven't been because I haven't had time to install a CVS client and all that.

--
timeless(ness)

[Mike Ginou]

Well, I guess I could give it a whirl. There aren't really people
busting down the doors trying to submit patches ;)



Time Less wrote:

>
>     Here's the quick patch that solves the error (not all lines of the alter
>     table command are properly commented).
>
>
> I should note that I find myself too busy these days to maintain this
> project. If anyone needs admin (CVS commit?) access, let me know, I'll
> figure out how to get you into the project admins.
>
> I think there are about 3 patches outstanding that could be applied to
> the project, but just haven't been because I haven't had time to install
> a CVS client and all that.
>
> --
> timeless(ness)
>

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...