Ernesto Revilla
2005-08-04 10:33:21 UTC
Hi all,
actually foreign key values in queries are read-only. In MS-Access they are
not. So what are semantics when a a foreign key is updated?
Two alternatives:
a) do a select * from table to which points foreign key value with the
primary key=the new foreign key value. Just the values of the columns the
requeried part should be updated.
b) execute the original query with additional condition, where the primary
keys match the old ones except the primary key referred by the updated
foreign key should have the new value.
Example:
select * from orders, client, products where client.clientid =
orders.clientid and products.productid = orders.productid
Now a row is changed, with client.clientid=1, orders.orderid=2 and
products.productid=3. The orders.productid is changed from 3 to 4. The row
values should be updated with the values of the query:
select * from orders, client, products where (client.clientid =
orders.clientid and products.productid = orders.productid) AND (
client.clientid=1 AND orders.orderid=2 and product.productid=4)
Although updating the whole should be easier (solution b) may be solution a
is more convenient to enable insertion of rows in JOINS. This is because, as
a foreign key is updated (a foreign key is one that is related to a prmary
key of another table, using a relation), the field values of the referred
table can be filled automagically.
When a query includes at least one table/query with no primary key, the
whole is not updateable.
Best regards,
Erny
actually foreign key values in queries are read-only. In MS-Access they are
not. So what are semantics when a a foreign key is updated?
Two alternatives:
a) do a select * from table to which points foreign key value with the
primary key=the new foreign key value. Just the values of the columns the
requeried part should be updated.
b) execute the original query with additional condition, where the primary
keys match the old ones except the primary key referred by the updated
foreign key should have the new value.
Example:
select * from orders, client, products where client.clientid =
orders.clientid and products.productid = orders.productid
Now a row is changed, with client.clientid=1, orders.orderid=2 and
products.productid=3. The orders.productid is changed from 3 to 4. The row
values should be updated with the values of the query:
select * from orders, client, products where (client.clientid =
orders.clientid and products.productid = orders.productid) AND (
client.clientid=1 AND orders.orderid=2 and product.productid=4)
Although updating the whole should be easier (solution b) may be solution a
is more convenient to enable insertion of rows in JOINS. This is because, as
a foreign key is updated (a foreign key is one that is related to a prmary
key of another table, using a relation), the field values of the referred
table can be filled automagically.
When a query includes at least one table/query with no primary key, the
whole is not updateable.
Best regards,
Erny