Do you have a question? Post it now! No Registration Necessary. Now with pictures!
- Posted on
- adding contraints FK refs to existing schema
April 20, 2006, 3:25 am
rate this thread
"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
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
and can save me the trouble ? I'm open to suggestions and tips.
Re: adding contraints FK refs to existing schema
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.
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. :)
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.
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
"An expert is someone who has made every mistake."
- » Americanas.com SALDO de TVs Com at 80% de Desconto (29798)
- — Newest thread in » MySQL Database Forum
- » ssh on command line: force using a group size (prime size) of 1024 (and no...
- — The site's Newest Thread. Posted in » Secure Shell Forum