Use of table expressions - expressing constraint conditions, PL-SQL Programming

Use of Table Expressions - Expressing Constraint Conditions

With the exception of key constraints, the examples in the theory book all explicitly reference at least one relvar and thus involve invocations of relational operators or aggregate operators. Assuming support for CREATE ASSERTION, we can always derive SQL counterparts of these examples using table expressions and truth-valued operators, but when that assumption does not hold we need to look for alternative solutions using table constraints. In most cases these will entail the use of subqueries and even that technique is prohibited by many implementations. In some cases special syntactic constructs are available, as we shall see, but there are several for which no SQL solution is available unless the implementation supports CREATE ASSERTION or subqueries in table constraints.Now, the reason usually given for lack of support for subqueries in constraints is that in general such expressions can require the DBMS to examine the entire content of possibly very large tables.

If database updates are expected to occur frequently-and are perhaps required to occur very frequently indeed- then declaration of such constraints would give rise to an intolerable slowing down of the updating process. Of course this is an extremely valid concern and we have to admit that integrity might occasionally have to be compromised for performance reasons, but consider the user with a small database that is subject to comparatively infrequent updating but nevertheless has strong integrity requirements. Might not such a user feel unfairly treated by a system that prohibits the declaration of required constraints? Defenders of the status quo respond to this argument by holding that language constructs that can give rise to disappointment for performance reasons, to such an extent as to militate against their use in common practical situations, should be banned. But sometimes users resort to implementing constraints, as best they can, in application code when they wish to enforce a constraint that is not supported by the DBMS but nevertheless does not adversely impair performance. The DBMS could almost certainly enforce such constraints much more efficiently and much more reliably. We can also point to various other SQL constructs that might be subject to similar concerns but are supported nonetheless. For example, if tables T1, T2, and T3 each contain 100,000 rows, then SELECT * FROM T1, T2, T3, when evaluated, delivers a table containing a quadrillion rows.

Posted Date: 1/18/2013 7:24:32 AM | Location : United States







Related Discussions:- Use of table expressions - expressing constraint conditions, Assignment Help, Ask Question on Use of table expressions - expressing constraint conditions, Get Answer, Expert's Help, Use of table expressions - expressing constraint conditions Discussions

Write discussion on Use of table expressions - expressing constraint conditions
Your posts are moderated
Related Questions
Selecting Objects: Suppose that you have run the SQL*Plus script below that creates object type Person and object table persons, and that you have settled the table: CREATE

Project Description: I want to write some SQL statements. The things I need are between pages 5-7. The only problem is that i want it till tomorrow. Skills required is SQL

LEVEL You use the LEVEL with the SELECT CONNECT BY statement to categorize rows from a database table into a tree structure. The LEVEL returns the level number of a node in a

Use of COUNT in SQL It describes and discusses various general methods of expressing constraints, eventually noting that support for "=" with relation operands is sufficient f

SQL Database: So, an SQL database is one whose symbols are organized into a collection of tables. Now, shows an SQL table as the current value of an SQL variable, ENROLMENT, b

Structure of an Object Type: Similar to package, an object type has 2 parts: the specification and the body. The specification is the interface to your applications; it declar

I want someone to write a TSQL function that returns the name of the ODBC DSN. I will use the queries below, to get information about the connection, but none of these return th

Data Types in SQL - Character CHARACTER or, synonymously, CHAR, for character strings. When this type is to be the declared type of something (e.g., a column), the permissible

Iterative Control: LOOP  Statements The LOOP statement executes a series of statements multiple times. There are 3 forms of LOOP statements: LOOP, WHILE-LOOP, & FOR-LOOP. LOOP

Creating a SQL file 1. Open a new file in Notepad++ and save it to the location c:\mysql\bin, with the name lab8script.sql (the file extension should be .sql ). Add a MySQL co