Thanks for the details, Isaac... Rick
-----Original Message----- From: S.Isaac Dealey [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 30, 2005 9:57 AM To: CF-Talk Subject: RE: Database Model Suggestions - Elections > Thanks for the explanation, Joe... > Can you expand your explanation to include > the code that accomplish the constraint? Constraints also affect updates and deletes indicentally, so that you couldn't delete a color from the color table in his example without first making sure that there weren't any users with that color assigned as their favorite color. Creating a foreign key constraint isn't terribly difficult -- it can be accomplished in several ways. The two ways to create a foreign key constraint in a SQL script are while creating a table (my preference -- actualy iirc there are two ways to do it in a create table script) and independently after the table has been created. Example: create table user ( userid nvarchar(35) primary key, favoritecolorid nvarchar(35) references color(colorid) ) appending "references table(column)" to the end of column definition in the create statement creates a foreign key between the two tables. Note that for all foreign keys the referenced column in the matching table must be a primary key, otherwise you'll just get an error and won't be able to create the constraint. Here's an example of the alternate create-table syntax from the SQL Server BOL: CREATE TABLE order_part (order_nmbr int, part_nmbr int FOREIGN KEY REFERENCES part_sample(part_nmbr) ON DELETE NO ACTION, qty_ordered int) Alter table syntax is similar ALTER TABLE order_part ADD CONSTRAINT [nameofconstraint] FOREIGN KEY (part_nmbr) REFERENCES part_sample(part_nmbr) With SQL Server the column name between "foreign key" and "references" can be omitted if it matches the references column name and the column name at the end can be omitted if it's a single-column primary key. I'm not sure if that's according to the SQL-92/2000 standard or if it's an MS-ism off the top of my head. You can probably also include a name for the constraint in the same place in the create table statement. I do know there are some variations between databases for these -- a good number of which have been accounted for in the onTap framework, but as I have the tool now I don't have to remember what those variances are. :) DB tools like SQL Server Enterprise Manager typically have a GUI (or two) to create constraints also. SQL Server has a pretty pleasant (to use) feature in its diagramming tool. Just create a diagram with your desired tables, then drag the foreign-key column onto the primary-key, glance at the dialog to make sure the reference is correct and click OK. The constraints are created when you save the diagram. SQL Server also will let you manage constraints on an individual table from the context-menu on the table, although I can't remember the last time I used that tool. Mostly I use the create table syntax. s. isaac dealey 954.522.6080 new epoch : isn't it time for a change? add features without fixtures with the onTap open source framework http://macromedia.breezecentral.com/p49777853/ http://www.sys-con.com/author/?id=4806 http://www.fusiontap.com ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:200774 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
