Re: modifying relationships using DAO?

In Access 2003 this

DBEngine(0)(0).Execute _
"ALTER TABLE [Order Details] " _
& "ADD CONSTRAINT " _
& "OrderParent " _
& "FOREIGN KEY (OrderID) " _
& "REFERENCES Orders (OrderID) " _
& " ON UPDATE CASCADE " _
& " ON DELETE CASCADE"

gives me a Syntax Error in the Constraint Clause.

I note this:

Foreign keys

When dealing with foreign keys, the concept of a fast foreign key may
be useful. A fast foreign key is a foreign key that has no index.
Although this may seem counter-intuitive, there is a valid explanation
for it. By default, when a foreign key is defined, an index based on
the column(s) in the foreign key is created automatically. In many
instances this enhances performance when executing operations that
maintain referential integrity. However, if there are many duplicated
values in the foreign key field, the foreign key index will affect
performance when records are added and deleted from the table. To
prevent the automatic creation of indexes on foreign keys, use the NO
INDEX keywords in the declaration of the foreign key.

ALTER TABLE tblInvoices
ADD CONSTRAINT FK_tblInvoices
FOREIGN KEY NO INDEX (CustomerID) REFERENCES
tblCustomers (CustomerID)
ON UPDATE CASCADE
ON DELETE CASCADE

Note The fast foreign key statement can only be executed through
the Jet OLE DB provider and ADO. It will return an error message if
used through the Access SQL View user interface. Also note that to drop
a fast foreign key, you must issue the DROP CONSTRAINT statement
through the Jet OLE DB provider and ADO.

Another example of a situation where a fast foreign key would be useful
is in an order entry database application. Assume that there is a table
called CustomerTypes that identifies what type of customers are being
tracked, a Customer table, and an Orders table. Assume that there are
10 rows in the CustomerTypes table, 100,000 rows in the Customer table,
and 350,000 rows in the Orders table. A good choice for the Customers
table would be a fast foreign key that references the primary key in
the CustomerTypes table. This is because there is a maximum of 10
unique values out of 100,000 rows. An index here has little value for
retrieving data and would be a drag on concurrency and inserts,
deletions, and updates in the CustomerType column.

On the other hand, the fast foreign key would probably not be useful
when applied to the CustomerID column in the Orders table, because
those values are likely to be unique, since each represents a different
customer. In this instance having the foreign key indexed in the
regular manner is very advantageous because it is used in joins and
other lookup criteria.
---
As ADO always works, I always use it.

.

Relevant Pages

  • Re: Best way to represent a many to many with optionality?
    ... DELETE Rules of the foreign key to enforce the above mentioned "business rules" without getting the "circularity/default" errors? ... SQL Server lets you specify any of the following rules to apply to either INSERT/UPDATE or DELETE: ... CustomerID bigint NOT NULL identity ... AddressTypeID tinyint NOT NULL ...
    (microsoft.public.sqlserver.programming)
  • Re: SKIP is not clear...
    ... Why not just say SKIP CustomerID?? ... in front of attribute Movie. ... CREATE MINING STRUCTURE myst ... How is Movie a foreign key? ...
    (microsoft.public.sqlserver.datamining)
  • Re: SKIP is not clear...
    ... Why not just say SKIP CustomerID?? ... in front of attribute Movie. ... CREATE MINING STRUCTURE myst ... How is Movie a foreign key? ...
    (microsoft.public.sqlserver.datamining)
  • Re: how do i add primary key in an existing database access 2003
    ... Customers and Orders and you add autonumber primary key columns CustomerID ... you'll also need to add a foreign key CustomerID column to ... You then have to fill the foreign key CustomerID in Orders with the values ... CustomerID combo box on the Orders form in the sample Northwind database ...
    (microsoft.public.access.gettingstarted)
  • Re: modifying relationships using DAO?
    ... Didn't know about the Fast Foreign Key. ... ALTER TABLE w/ ON UPDATE CASCADE works thru DAO. ... the concept of a fast foreign key may ... FOREIGN KEY NO INDEX (CustomerID) REFERENCES ...
    (comp.databases.ms-access)