> 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
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Find out how CFTicket can increase your company's customer support
efficiency by 100%
http://www.houseoffusion.com/banners/view.cfm?bannerid=49
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:200689
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=89.70.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
