Idea for updating foreign key values in queries

--===============0803687447==
Content-Type: multipart/alternative; 
	boundary="----=_Part_1594_1282617.1123151601336"

------=_Part_1594_1282617.1123151601336
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

Hi all,
actually foreign key values in queries are read-only. In MS-Access they are=
=20
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=20
primary key=3Dthe new foreign key value. Just the values of the columns the=
=20
requeried part should be updated.

b) execute the original query with additional condition, where the primary=
=20
keys match the old ones except the primary key referred by the updated=20
foreign key should have the new value.

Example:
select * from orders, client, products where client.clientid =3D=20
orders.clientid and products.productid =3D orders.productid

Now a row is changed, with client.clientid=3D1, orders.orderid=3D2 and=20
products.productid=3D3. The orders.productid is changed from 3 to 4. The ro=
w=20
values should be updated with the values of the query:
select * from orders, client, products where (client.clientid =3D=20
orders.clientid and products.productid =3D orders.productid) AND (
client.clientid=3D1 AND orders.orderid=3D2 and product.productid=3D4)

Although updating the whole should be easier (solution b) may be solution a=
=20
is more convenient to enable insertion of rows in JOINS. This is because, a=
s=20
a foreign key is updated (a foreign key is one that is related to a prmary=
=20
key of another table, using a relation), the field values of the referred=
=20
table can be filled automagically.=20

When a query includes at least one table/query with no primary key, the=20
whole is not updateable.

Best regards,
Erny

------=_Part_1594_1282617.1123151601336
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

Hi all,<br>
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?<br>
<br>
Two alternatives:<br>
a) do a select * from table to which points foreign key value with the
primary key=3Dthe new foreign key value. Just the&nbsp; values of the
columns the requeried part should be updated.<br>
<br>
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.<br>
<br>
Example:<br>
select * from orders, client, products where client.clientid =3D orders.cli=
entid and products.productid =3D orders.productid<br>
<br>
Now a row is changed, with client.clientid=3D1, orders.orderid=3D2 and
products.productid=3D3. The orders.productid is changed from 3 to 4. The
row values should be updated with the values of the query:<br>
select * from orders, client, products where (client.clientid =3D
orders.clientid and products.productid =3D orders.productid) AND
(client.clientid=3D1 AND orders.orderid=3D2 and product.productid=3D4)<br>

<br>
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. <br>
<br>
When a query includes at least one table/query with no primary key, the who=
le is not updateable.<br>
<br>
Best regards,<br>
Erny<br>
<br>

------=_Part_1594_1282617.1123151601336--

--===============0803687447==
Content-Type: text/plain; charset="us-ascii"
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

_______________________________________________
Rekall-devel mailing list
[email protected]
http://www.mailman.a-i-s.co.uk/cgi-bin/mailman/listinfo/rekall-devel
--===============0803687447==--