Example of add constraint in sql, PL-SQL Programming

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







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
Related Questions
UNION without CORRESPONDING - SQL The use of UNION without CORRESPONDING. Example is merely by omitting CORRESPONDING, but only because the operands have identical SELECT clau

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

GOTO Statement The GOTO statement branches to a label unconditionally. The label must be exclusive within its scope and should precede an executable statement or a PL/SQL block.

Redeclaring Predefined Exceptions Keep in mind that, the PL/SQL declares predefined exceptions globally in the package STANDARD; Therefore you need not declare them yourself.

Advantages of Exceptions Using the exceptions for the error handling has many benefits. Without an exception handling, every time you issue a command, you should ensure for th

Change Sql file into CSV for product registration on Magento Project Description: I have a set of files that are in Sql format and could like for a developer to help me with

I need SQL , WP SQL Expert Project Description: Expert required to modify WP SQL query. Skills required are MySQL, SQL, PHP, Wordpress

PRIMARY KEY: PRIMARY KEY  indicates that the table is subject to a key constraint, in this case declaring that no two rows in the table assigned to ENROLMENT can ever have the

UNION and OR - SQL SQL supports UNION explicitly but differently from the way it supports JOIN explicitly. As we have seen, JOIN is used exclusively within the FROM clause, su

Packaging Cursors   You can split a cursor specification from its body for placement in a package. In that way, you can change the cursor body without changing the cursor spec