Example of add constraint in sql, PL-SQL Programming

Assignment Help:

Example of ADD CONSTRAINT in SQL

Example: Alternative formulation for MAX_ENROLMENTS

ALTER TABLE IS_ENROLLED_ON

ADD CONSTRAINT MAX_ENROLMENTS

CHECK ((SELECT COUNT (*) FROM IS_ENROLLED_ON) <= 20000);

Explanation

ALTER TABLE IS_ENROLLED_ON announces that an alteration to the definition of base table IS_ENROLLED_ON is being specified.
ADD CONSTRAINT MAX_ENROLMENTS states that the alteration in question is the addition of something SQL calls a table constraint, and its name is MAX_ENROLMENTS. A table constraint is a condition that is required to be satisfied by every row appearing in the base table for which that constraint is defined. Thus, in general, it is an open expression of the kind that can appear as the condition of a WHERE clause. In this example the condition is in fact a closed expression-it contains no reference to a column of IS_ENROLLED_ON-and thus if it is satisfied by one row of that table, then it is satisfied by all of them.
A table constraint is one that must be satisfied by every row of the applicable table, it is always satisfied when the applicable table is empty-there is no row for which the constraint fails. In the case at hand, this is not a problem, because obviously, when IS_ENROLLED_ON is empty, then its cardinality-zero-does not exceed 20,000. However, suppose a constraint was required to the effect that IS_ENROLLED_ON must never be empty. That could be achieved by changing <= 20000 to > 0 , but the same change to Example would be ineffectual: when IS_ENROLLED_ON is empty, it contains no row that fails to satisfy the constraint. That is why SQL is incomplete with respect to database integrity when support for CREATE ASSERTION is absent.

Related Discussions:- Example of add constraint in sql

Explicit cursor attributes, Explicit Cursor Attributes The cursor varia...

Explicit Cursor Attributes The cursor variable or each cursor has four attributes: %FOUND, %ISOPEN, %ROWCOUNT, and %NOTFOUND. When appended to the cursor or cursor variable, th

Sql script to create and populate the tables, Create the four tables and po...

Create the four tables and populate them with the given data. Answer the following queries in SQL. 1. Get all part-color/part-city combinations. Note: Here and subsequently, the

Positional notation, Positional Notation The first procedure call uses...

Positional Notation The first procedure call uses the positional notation. The PL/SQL compiler relates the first actual parameter, account, with the first proper parameter, ac

Using delete - collection method, Using DELETE This process has three ...

Using DELETE This process has three forms. The DELETE removes all elements from the collection. DELETE(n) removes the nth element from the nested table. When n is null, then D

Using for update, Using FOR UPDATE If you declare a cursor which will ...

Using FOR UPDATE If you declare a cursor which will be referenced in the CURRENT OF clause of an UPDATE or DELETE statement, you should use the FOR UPDATE clause to obtain an

Lob types in pl/sql, LOB Types The large object (LOB) datatypes like BF...

LOB Types The large object (LOB) datatypes like BFILE, BLOB, CLOB, and NCLOB store the blocks of unstructured data (like graphic images, text, video clips, and sound waveforms)

Calling constructors in pl sql, Calling Constructors: The Calls to a c...

Calling Constructors: The Calls to a constructor are allowed wherever the function calls are allowed. Similarly to the functions, a constructor is called as a section of an ex

Write a stored procedure, a. Create a table odetails_new. It has all the a...

a. Create a table odetails_new. It has all the attributes of odetails and an additional column called cost, whose values are the product of the quantity and price of the part bein

Comparison operators - sql operators, Comparison Operators Usually, yo...

Comparison Operators Usually, you use the comparison operators in the WHERE clause of a data manipulation statement to form the predicates, that compare one expression to anot

Using aliases-declarations in sql, Using Aliases The Select-list items f...

Using Aliases The Select-list items fetched from a cursor related with the %ROWTYPE should have simple names or, if they are expressions, should have aliases. In the example bel

Write Your Message!

Name

Email id

Message

Verfication Code

User Account

All Pages