DODS FAQs - How does DODS work with Foreign Keys?
dods.gif (11357 bytes)The 4.x release of DODS allows tables like the following:
    create table person (
	id	integer,
	name	char(10),
	primary key(id)
    );

    create table employee (
	id	integer,
	salary	integer,
	primary key(id),
	foreign key(id) references person(id)
    );

    create table manager (
	id	integer,
	bonus	integer,
	primary key(id),
	foreign key(id) references employee(id)
    );

By having the PK of employee be a FK referencing the PK of person, a one-to-one relationship is established. A given employee is one and only one person. An employee "is-a" person.

By having the PK of manager be a FK referencing the PK of employee, a one-to-one relationship is established. A given manager is one and only one employee. A manager "is-a" employee.

So for a given manager, the manager.id = employee.id = person.id.

Now you can create an updatable join view:

    create view full_manager as
	select	p.id		id,
		p.name		name,
		e.salary	salary,
		m.bonus		bonus
	from person p, employee e, manager m
	where	p.id = e.id
	and	e.id = m.id;

You can then create a FullManagerDO for that view, and use it to interrogate and modify the name, salary, and bonus for a given manager through a single object. That is, you can update values in multiple tables using a single DO.

Data objects that utilize an updatable join view are an elegant replacement for the "derived Data Object" scheme currently provided by DODS.

This technique only works for databases that support updatable join views.

To verify that the columns returned by a view are updatable, the following query can be run (in Oracle):

	select column_name, updatable, insertable, deletable
	from user_updatable_columns
	where table_name = 'full_manager'

Notes on "key-preserved" tables:

Columns in a view are updatable if the table they come from is key-preserved. A table is key-preserved in a join view if every key of the table (whether or not it is included in the select clause of the view) would still be a valid key following a change to the columns seen in the view. Only key-preserved tables can be updated through the view.

When tables joined in a view are connected via one-to-one relationships (as in the person/employee/manager example above) the database knows that columns in various tables can be updated without affecting integrity constraints.

But if, instead of an "is-a" relationship, the manager table had a "has-a" relationship with the employee table

    create table manager (
	id	integer,
	empinfo	integer,	/* each manager "has" personal employee info */
	bonus	integer,
	primary key(id),
	/* 
	   manager primary key is not foreign key to employee primary key;
	   the database assumes this is a many-to-one relationship
	*/
	foreign key(empinfo) references employee(id)
    );

then only the bonus column of the full_manager view would be updatable.

The "has-a" relationship was the best that could be achieved using the oid/version scheme.

Now that DODS supports arbitrary primary keys in tables, correct relationships can be established, and elegant solutions for object-relational mappings are available.

The old "derived Data Object" scheme supported by DODS is hereby deprecated.

For all the latest information on DODS, please refer to http://dods.enhydra.org
Questions, comments, feedback? Let us know...