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

Using inner join, Using INNER JOIN INNER JOIN is used to retrieve the ... Using INNER JOIN INNER JOIN is used to retrieve the data from all tables listed based on a condition of equality listed after keyword ON. If the condition is not meet, rows ar

Keyword and parameter description - if statement, Keyword and Parameter Des... Keyword and Parameter Description: boolean_expression: This is an expression which results the Boolean value TRUE, FALSE, & NULL. It is related with a series of statement

Parameter modes - pl sql, Parameter Modes: You do not require to speci... Parameter Modes: You do not require to specify a parameter mode for the input bind arguments (those used, for illustration, in the WHERE clause) as the mode defaults to IN. Th

Implicit rollbacks, Implicit Rollbacks Before execute the INSERT, UPDA... Implicit Rollbacks Before execute the INSERT, UPDATE, or DELETE statement, the Oracle marks an implicit savepoint . When the statement fails, the Oracle rolls back to the save

Sql outer join, SQL outer join SELECT * FROM IS_CALLED NATURAL LEFT... SQL outer join SELECT * FROM IS_CALLED NATURAL LEFT JOIN IS_ENROLLED_ON Note that adding LEFT to an invocation of CROSS JOIN has no effect unless the right-hand operand

Insert command in sql, INSERT Command in SQL Loosely speaking, INSERT... INSERT Command in SQL Loosely speaking, INSERT takes the rows of a given source table and adds them to the specified target table, retaining all the existing rows in the targ

Write a pl-sql program using the implicit cursor, Question: a) Given th... Question: a) Given the following relation: Location(loc_id, bldg_code, room, capacity) The underlined field is a primary key. (i) Write a PL/SQL program using the impl

Inner join, Inner Join We have learned how to retrieve data from one t... Inner Join We have learned how to retrieve data from one table by using SELECT statement. But, as we have learned, normalized relational databases mean the data is spread betw

Package standard, Package STANDARD The package named STANDARD defines t... Package STANDARD The package named STANDARD defines the PL/SQL atmosphere. The package specification globally declares the exceptions, types, and subprograms that are available

Exit statement - syntax, EXIT Statement   You can use the EXIT stateme... EXIT Statement   You can use the EXIT statement to exit a loop. The EXIT statement has 2 forms: the conditional EXIT WHEN and the unconditional EXIT. With the either form, you