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.

Posted Date: 1/18/2013 7:16:19 AM | Location : United States

Ask an Expert

Related Discussions:- Example of add constraint in sql, Assignment Help, Ask Question on Example of add constraint in sql, Get Answer, Expert's Help, Example of add constraint in sql Discussions

Write discussion on Example of add constraint in sql
Your posts are moderated

Write your message here..

Related Questions

In packages - subprograms, In Packages The Forward declarations also g... In Packages The Forward declarations also group logically related subprograms in the package. The subprogram specifications go in the package specification, & the subprogram b

An active database in pl-sql, Consider the following set of database tables... Consider the following set of database tables (same tables from Assignment 6-1). Please take note of foreign keys (most of them carry the same names as the corresponding primary ke

%rowtype - cursors, %ROWTYPE: This attribute gives a record type which ... %ROWTYPE: This attribute gives a record type which represents a row in the database table or a row fetched from a formerly declared cursor. The Fields in the record and corresp

Iterative control:exit statements, EXIT The EXIT statement forces a loop... EXIT The EXIT statement forces a loop to done unconditionally. Whenever an EXIT statement is encountered, the loop is done immediately and controls the passes to the next statem

Custom ms access database designed, I would like to have a custom MS Access... I would like to have a custom MS Access database designed and coded that would help me schedule my customer's orders and that would help me track my employees production output and

Effect of anonymous columns, Effect of Anonymous Columns Now, recall t... Effect of Anonymous Columns Now, recall that a VALUES expression denotes a table with undefined column names. If an initial value is to be specified when a base table is creat

Aggregate assignment-declarations in sql, Aggregate Assignment The %ROWT... Aggregate Assignment The %ROWTYPE declaration cannot include an initialization clause. Though, there are two ways to assign values to all fields in a record at once. At First, t

Restriction in sql, Restriction in SQL Syntax: SELECT DISTINCT... Restriction in SQL Syntax: SELECT DISTINCT StudentId FROM IS_CALLED WHERE Name = 'Boris' The WHERE clause operates on the result of the FROM clause in analogo

Methods in pl/sql, Methods: In normal, a method is a subprogram declar... Methods: In normal, a method is a subprogram declared in an object type specification using the keyword MEMBER or STATIC. The method cannot have similar name as the object typ

Implicit cursor attributes, Implicit Cursor Attributes The Implicit cur... Implicit Cursor Attributes The Implicit cursor attributes returns the information about the execution of an INSERT, DELETE, UPDATE, or SELECT INTO statement. The cursor attribu