Re: SELECT and RESTRICT statements

On Sat, 08 Oct 2005 04:49:03 -0700, Jenski wrote:

> Was just wondereding if anybody could clarify for me what the difference
> is between SELECT and RESTRICT is, in SQL ?

The two keywords are not really comparable.

As long as you stay in the SQL World, things are somewhat simple:

SELECT is part of SQL's data retrieval language subset; it determines which of the columns from the result you want in your result set.

RESTRICT is part of SQL's data definition language subset. It is used in some contexts (like when defining a foreign key) to state that an action may not propagate. E.g. you could add a foreign key constraint like this:

ALTER TABLE foo ADD CONSTRAINT foo_bar_fk FOREIGN KEY (bar_id) REFERENCES bar(bar_id) ON DELETE RESTRICT

• meaning: If someone tries to remove a row in bar which foo needs (for referential integrity), the action is denied (in stead of cascaded as a deletion in the foo table). See you DBMS's documentation for more on this.

RESTRICT can also be part of a command, like: DROP TABLE bar RESTRICT;

• meaning: Drop the "bar" table, unless the table is used as part of referential integrity constraints in other tables.

In both the above examples, RESTRICT is actually superfluous, as it's the default in all databases, as far as I know.

Now, if we expand our perspective beyond an SQL only World: To really complicate matters, "restrict" and "select" are synonyms in relational algebra. Things get really tricky when you consider that SQL's SELECT keyword is analogous to relational algebra's "project" operation. So we have the following table of operator names:

Relational algebra term   Relational algebra symbol      SQL keyword
=======================   =========================      ===========
restrict                  small sigma                    WHERE
select                    small sigma                    WHERE
project                   small pi                       SELECT

-- 
Greetings from Troels Arvin