Re: How referential integrety maintained internally

On Friday 14 Mar 2003 9:30 am, Deepa K wrote:
> Hi All,
> Can any one tell me, how refrential integrity is maintained
> internally in postgresql. This will help in design my database
> accordingly. I have the doubt that reference to a column in a table
> means a pointer to the referenced column. So that even the value is
> repeated several times in the side table, that will only be referencing
> the main table or all the datas are maintained separately in the side
> table. What mechanism has been followed to maintain the integrity.
> Can any one help me.

Well, referential integrity covers a lot of things. Foreign keys (using the
REFERENCES keyword) basically set up triggers that manage modifications to
the tables concerned.

Example: table "project" is referenced by table "task" so that a task is
"owned" by a project. If you add a new task, PG will check that task has a
valid project-id. If you modify a task it will do the same.
If you delete a project, or modify it's project-id then you can get PG to
either:
a. Prevent the change if any tasks rely on this project id
b. Propogate the change to dependant tasks so that they are automatically
deleted/modified as required.

Now, what this means is that if you insert 1000 tasks, PG has to make 1000
separate checks to make sure that the project id is valid for each.

If you need more complicated integrity checks (e.g. no project can have more
than 10 associated tasks) then you can write your own triggers in one of the
procedural languages (see the manuals for details).

Is that any help?

--
Richard Huxton