When are constraints checked - sql, PL-SQL Programming

Assignment Help:

When Are Constraints Checked

Under the model constraints are conceptually checked at all statement boundaries (and only at statement boundaries). By default the same is true of SQL. However, SQL does not support the "multiple assignment" concept, described in the theory book, for database updates. For that reason it has to include an alternative method of addressing the problems that multiple assignment addresses. SQL does so by allowing the checking of specified constraints to be temporarily deferred and reinstated later-but never across a transaction boundary. As a result, it is possible for the database to appear to be inconsistent, but only to the user whose as yet uncommitted transaction has given rise to that state of affairs. As a consequence of deferred constraint checking, SQL code that depends on consistency with declared constraints is obviously exposed to that assumption of consistency being false when the code is executed while checking is deferred.

For example, the table expression SELECT Name FROM IS_CALLED WHERE StudentId = 'S1' might be expected never to result in a table containing more than one row, thanks to the key constraint applying to IS_CALLED; thus it might be used in a scalar subquery. However, if the checking of that key constraint is temporarily deferred and two or more rows with StudentId equal to 'S1' temporarily appear in that table, then the scalar subquery will give rise to a run-time exception. Fortunately, SQL does allow a constraint to be declared as NOT DEFERRABLE, and that is the default option.


Related Discussions:- When are constraints checked - sql

Running the pl/sql wrapper, Running the PL/SQL Wrapper To run the PL/SQ...

Running the PL/SQL Wrapper To run the PL/SQL Wrapper, go through the wrap command at your operating system prompt by using the syntax as shown: wrap iname=input_file [oname=

Negation - sql, Negation (NOT, ¬) - SQL There are three rows instead o...

Negation (NOT, ¬) - SQL There are three rows instead of just two. As you can see, ¬ p is defined as in two-valued logic (2VL) when p is either true or false, but ¬ (unknown) i

Using not null-declarations in sql, Using NOT NULL Besides assigning an ...

Using NOT NULL Besides assigning an initial value, the declarations can impose the NOT NULL constraint, as the example below shows: acct_id INTEGER(4) NOT NULL := 9999; You ca

Difference between 9i & 10g, Difference between 9i & 10G When Oracle r...

Difference between 9i & 10G When Oracle releases any new databases then it are having some discrepancy with them. But 10G is having much difference than oracle 9i has. Oracle

First step at defining type sid in sql, First Step at defining type SID in ...

First Step at defining type SID in SQL CREATE TYPE SID AS ( C VARCHAR(5) ) ; Explanation: TYPE SID announces that a type named SID is being defined to the syst

Theory of biogenesis, THEORY OF BIOGENESIS - This theory explains th...

THEORY OF BIOGENESIS - This theory explains that the existing living organisms originated from pre-existing living beings not from non living entities. This concept of or

Varrays versus nested tables, Varrays versus Nested Tables The Nested ta...

Varrays versus Nested Tables The Nested tables are differing from varrays in the following ways: 1)  Varrays have a maximum size, while nested tables do not. 2)  Varrays are

Raise_application_error, Raise_application_error -  procedure of package D...

Raise_application_error -  procedure of package DBMS_STANDARD , allows to issue an user_defined error messages by stored sub-program or database trigger.

Mysql developer/programmer, Project Description: I want somebody who can...

Project Description: I want somebody who can help me with an idea that I have been working on for a few months now. The person will require extensive knowledge of warcraft 3 PvP

Autonomous versus nested transactions, Autonomous versus Nested Transaction...

Autonomous versus Nested Transactions Though an autonomous transaction is started by the other transaction, it is not a nested transaction for the reasons shown below: (i)

Write Your Message!

Captcha
Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd