Re: multi-table integrity constraints

In article <toqt2m210kvm50_at_news.demon.nl>, "Sybrand says...
>
>
>"christopher lambert" <cjlambert_at_llnl.gov> wrote in message
>news:1932d1c8.0108290722.37d52446_at_posting.google.com...
>> how do you enforce integrity constraints over multiple tables? SQL
>> has a feature called ASSERTION, but oracle doesn't support it.
>
>You can do that by means of triggers.
>With 'SQL' you probably mean the sql in sqlserver.

i think he means sql99 spec actually in this case...

>Just FYI: the language in Oracle is SQL and is SQL92 compliant, as that
>standard has several levels. Most vendors including Oracle only implemented
>the lowest level of standardisation: Sybase and Microsoft didn't.
>

i'll clarify that statement a bit about the "lowest level".

Starting with the standard, we will find that the SQL/92 standard has four levels:

o Entry-level ? This is the level to which most vendors have complied. This level is a minor enhancement of the predecessor standard, SQL/89. No database vendors have been certified higher and in fact the National Institute of Standards and Technology (NIST), the agency that used to certify for SQL-compliance, does not even certify anymore. I was part of the team that got Oracle7.0 NIST-certified for SQL/92 entry-level compliance in 1993. An entry level compliant database has the feature set of Oracle7.0.

o Transitional ? This is approximately 'halfway' between entry-level and intermediate-level as far as a feature set goes.

o Intermediate ? this adds many features including (not by any means an exhaustive list):
Dynamic SQL
Cascade DELETE for referential integrity DATE and TIME data types
Domains
Variable length character strings
A CASE expression
CAST functions between data types

o Full ? Adds provisions for (again, not exhaustive): Connection management
A BIT string data type
Deferrable integrity constraints
Derived tables in the FROM clause
Sub-queries in CHECK clauses
Temporary tables

The entry-level standard does not include features such as outer joins, the new inner join syntax, and so on. Transitional does specify outer join syntax and inner join syntax. Intermediate adds more, and Full is, of course all of SQL/92.

Most books on SQL/92 do not differentiate between the various levels leading to confusion on the subject. They demonstrate what a theoretical database implementing SQL/92 FULL would look like. It makes it impossible to pick up a SQL/92 book, and apply what you see in the book to just any SQL/92 database. For example, in SQL Server the 'inner join' syntax is supported in SQL statements, whereas in Oracle8i it is not (but 9i does). Then again, Oracle7 supports derived tables in the FROM clause (inline views) which is part of FULL but others do not.

But, they are both SQL/92-compliant databases. You can do inner joins and outer joins in Oracle, you will just do it differently than in SQL Server. The bottom line is that SQL/92 will not go very far at the entry-level.

All databases implement many of the features of the higher levels but do not fully comply with the higher levels.

this statement:
>standard has several levels. Most vendors including Oracle only implemented
>the lowest level of standardisation: Sybase and Microsoft didn't.

is not true at all -- no one has certified at above entry level. Most have entry level certification and all have various bits and pieces of the "higher" levels.

>Hth,
>
>Sybrand Bakker,
>Senior Oracle DBA
>
>
>Hth,
>
>Sybrand Bakker,
>
>

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp