adding contraints FK refs to existing schema

I've seen the syntax in the J. Stephens/C. Russell book and believe the
"alter table" would be:
ALTER TABLE tbl
[CONSTRAINT name] FOREIGN KEY (column_list)
REFERENCES tbl_name (column_list)
[ ON DELETE options ]
[ ON UPDATE options ]

the Syntax is really not a problem, and I'm pretty sure I won't be
violating any FK contraints right off the bat. If so, I expect the
Alter Table command will fail.

I've maintained this logically up to now, with emphasis on thought and
good design.
I'm ready to let the DB take over some of the heavy lifting where
things could go wrong.
Though I shy away from most "[visual] tools" I'm almost going to need
something to help me manage this schema. ( any open-source tools ? )

btw, I'm using MySQL 5.0.17

I'm not a DBA, but I play one, when my boss is feeding me $$.
Any one had this experience? been in this dilemna? have a few battle
scars ?
and can save me the trouble ? I'm open to suggestions and tips.

Re: adding contraints FK refs to existing schema

awebguynow wrote:
> I've seen the syntax in the J. Stephens/C. Russell book and believe the
> "alter table" would be:
> ALTER TABLE tbl
> [CONSTRAINT name] FOREIGN KEY (column_list)
> REFERENCES tbl_name (column_list)
> [ ON DELETE options ]
> [ ON UPDATE options ]
>
> the Syntax is really not a problem, and I'm pretty sure I won't be
> violating any FK contraints right off the bat. If so, I expect the
> Alter Table command will fail.

I would recommend testing this for yourself. Create a parent & child
table in your "test" database (every MySQL installation has a test
database by default) and insert a few values that would violate such a
constraint. Then create the constraint. Does it fail?

In general, it's best to rely on yourself and do a small test to prove
that a given feature is going to work how you expect. People on
newsgroups can be wrong, and even the documentation can be wrong.

> I've maintained this logically up to now, with emphasis on thought and
> good design.
> I'm ready to let the DB take over some of the heavy lifting where
> things could go wrong.

Excellent! Keep in mind that in MySQL, foreign key constraints are
accepted but ignored if you use MyISAM tables. Only InnoDB tables
enforce constraints. Oh, and BDB tables, but nobody uses them. :)

> Though I shy away from most "[visual] tools" I'm almost going to need
> something to help me manage this schema. ( any open-source tools ? )

MySQL AB offers a pretty nice GUI tool for schema design. It's called
MySQL Workbench. It's still in beta, but it works fairly well, as long
as you're on Windows.
http://dev.mysql.com/downloads/workbench/1.0.html

> I'm not a DBA, but I play one, when my boss is feeding me $$.
> Any one had this experience? been in this dilemna? have a few battle
> scars ?
> and can save me the trouble ? I'm open to suggestions and tips.

My tips are to read a lot, and make sure you keep a database where you
can experiment with new features before trying to apply them to your
real database.

"An expert is someone who has made every mistake."

Regards,
Bill K.