473,320 Members | 1,896 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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.

Apr 20 '06 #1
1 1272
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.
Apr 20 '06 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Ted | last post by:
How do I grant all privileges for a schema that has a large number of existing tables, procedures, functions, etc to a newly created role, without having to issue a grant statement for each object...
0
by: Wundercat | last post by:
I have a data-oriented project, in which the main data component is an XML catalogue of items, supplemented by a number of other XML files that specify additional information about some of the...
3
by: _DS | last post by:
The two obvious methods for ref'ing assemblies are: Add a reference and 'Browse' for the actual DLL OR Add existing project to the solution, then add a ref to 'Project'. 1: I'd like to...
6
by: John Salerno | last post by:
Ok, this might look familiar. I'd like to use regular expressions to change this line: self.source += '<p>' + paragraph + '</p>\n\n' to read: self.source += '<p>%s</p>\n\n' % paragraph ...
1
by: donnie.hale | last post by:
Question: What's the "canonical" way to import an existing XSD schema file into VS2005 in such a way that I can use standard C# object / property techniques to create content of that schema type...
0
by: AboutJAV | last post by:
I created a crystal report with the report.rpt reportdata.xsd I created the a new dataset with the reportdata myreportdata = new reportdata(); That automatically created a new dataset...
3
by: John Nagle | last post by:
Are weak refs slower than strong refs? I've been considering making the "parent" links in BeautifulSoup into weak refs, so the trees will release immediately when they're no longer needed. In...
0
by: daokfella | last post by:
Hi all, I added a dataset to my project and dragged over a table from server explorer. I modified the dataadapter to use existing stored procedures for the Get, Insert, Update and Delete. I've...
0
debasisdas
by: debasisdas | last post by:
The following script is used to create a read only schema of any existing schema in the same database. The readonly schema is granted only read permisions on the tables and views of the existing...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.