Your second SQL statement is fine in itself, but might not in fact be the one
you want. I'll come back to why I think that's the case later in this
message, and for the moment assume it is the correct statement.
The problem is that there is no unique invoice in the referenced
InvoicedOrder table on the OrderID and productID columns. So before
executing the SQL statement you need to create the index. These two columns
are a candidate key, and normally in this context would be made the primary
key, which you can do with:
CREATE INDEX PrimaryKey
ON InvoicedProduct
(OrderID, ProductID)
WITH PRIMARY;
If the table already has a primary key such as an autonumber column then
you'd create a unique index, called OrderProduct in this example, on the two
columns with:
CREATE UNIQUE INDEX OrderProduct
ON InvoicedProduct
(OrderID, ProductID);
When you add a constraint with the ALTER TABLE statement you are doing via
the DDL (Data Definition Language) what you would do in the relationships
window if you created a relationship between the two tables on these columns.
So if you open the relationships window and show the two tables after
executing your ALTER TABLE statement you should find that the relationship on
the two columns is now shown between them. Creating this enforced
relationship (CONSTRAINT) is a means of ensuring data integrity because you
can then only insert a row into the OrderedProduct table if one already
exists in InvoicedProduct.
That brings me to the sting in the tail! I would expect the constraint to
be the other way round, i.e. that you can only insert a row into the
InvoicedProduct table if one already exists in OrderedProduct, it seeming
more logical for the order to exist before the invoice. So it may well be
that the OrderedProduct table is already indexed uniquely on the two columns
and that your ALTER TABLE statement simply had the tables the wrong way
round, and OrderedProduct should be the referenced table. I'll leave you to
consider that as you'll know the context of these tables in the overall
database schema better than I. If I'm right on this (and I suspect I am) you
seem to already have a primary key column PriKey in OrderedProduct, so you
might need to execute a CREATE UNIQUE INDEX on the two columns if they are
not already indexed uniquely. To see if they are select View | Indexes while
in table design view.
One other point I'll mention while considering these tables is that the
tables have singular nouns as their names. The usual convention is to use
plural or collective nouns as table names. So names like InvoicedProducts
and OrderedProducts would, I think be better, Conversely column names are
generally single nouns. If this convention is followed it makes SQL writing
(and reading) more intuitive as it becomes closer to plain English, e.g.
SELECT employeeID, firstname, lastname
FROM Employees
WHERE salary > 50000;
I've used another convention here; proper case for table names, lower case
for column names. Some developers favour this strongly, but I'm not dogmatic
on this point.
Ken Sheridan
Stafford, England
