adding contraints FK refs to existing schema

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View
I've seen the syntax in the J. Stephens/C. Russell book and believe the
"alter table" would be:
  [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:
Quoted text here. Click to load it

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.

Quoted text here. Click to load it

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.  :)

Quoted text here. Click to load it

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.

Quoted text here. Click to load it

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."

Bill K.

Site Timeline