| Author: D.Jeya kumar(JK) 28 Aug 2008 | Member Level: Diamond | Rating:  Points: 5 |
Hi,
Check constraint means it will allow you to check the values inserting into a column.It will limit the values
For ex:
if you want to specify a range for the salary within 1000 and 100000 then you can not insert below of more than the specific values.
ALTER TABLE <Table_name> ADD CONSTRAINT <cons_name>
CHECK (Columnname >= 1 AND Columnname <= 5)
Example
Alter table Test add constraint check_nos check (no >100 and no < 1000)
Regards
JK
|
| Author: Legend 29 Aug 2008 | Member Level: Silver | Rating: Points: 6 |
Constraints can be actually viewed as equations: in both cases, variables are related by properties, and solving a set of equations amounts to finding which assignment of values to variables meets all the equations. Mathematical equations can be solved if appropriate methods are known, and the same happens with constraints. But constraint tools usually provide domains which are not commonly treated by classical mathematics; or, at least, constraint systems for which solving methods are not a central point of the usual mathematical background.
Using the appropriate domain for each problem is essential: constraint domains have specific characteristics and solving methods which make them more appropriate than others for some problems. Fortunately, deciding which constraint system has to be used is often not difficult: in most cases the problem itself strongly suggests which constraint system to use. In general, the process of solving a problem is a combination of propagation (a general term to refer to equation solving) and search, when an incomplete solution is found.
But looking at constraints as a kind of extended equations does not allow the perception of the whole scenario: equations (even in their extended constraint-like version) suffer from the same drawbacks as OR: lack of modularity (the whole problem is a big set of interrelated equations), lack of dynamic creation of equations, sometimes lack of power to solve completely the equation system proposed, or the solution, as returned by the solver (assignments of values to variables) not coming out in the appropriate format (which, for example, might have to be shared with other tools).
Solutions to these problems can be worked out by coupling constraints and programming.
|
| Author: Legend 29 Aug 2008 | Member Level: Silver | Rating: Points: 6 |
CHECK Constraints
CHECK constraints enforce domain integrity by limiting the values that are accepted by a column. They are similar to FOREIGN KEY constraints in that they control the values that are put in a column. The difference is in how they determine which values are valid: FOREIGN KEY constraints obtain the list of valid values from another table, and CHECK constraints determine the valid values from a logical expression that is not based on data in another column. For example, the range of values for a salary column can be limited by creating a CHECK constraint that allows for only data that ranges from $15,000 through $100,000. This prevents salaries from being entered beyond the regular salary range.
You can create a CHECK constraint with any logical (Boolean) expression that returns TRUE or FALSE based on the logical operators. For the previous example, the logical expression is: salary >= 15000 AND salary <= 100000.
You can apply multiple CHECK constraints to a single column. You can also apply a single CHECK constraint to multiple columns by creating it at the table level. For example, a multiple-column CHECK constraint can be used to confirm that any row with a country/region column value of USA also has a two-character value in the state column. This allows for multiple conditions to be checked in one location.
CREATE TABLE CheckTbl (col1 int, col2 int);
GO
CREATE FUNCTION CheckFnctn()
RETURNS int
AS
BEGIN
DECLARE @retval int
SELECT @retval = COUNT(*) FROM CheckTbl
RETURN @retval
END;
GO
ALTER TABLE CheckTbl
ADD CONSTRAINT chkRowCount CHECK (dbo.CheckFnctn() >= 1 );
GO
The CHECK constraint being added specifies that there must be at least one row in table CheckTbl. However, because there are no rows in the table against which to check the condition of this constraint, the ALTER TABLE statement succeeds.
CHECK constraints are not validated during DELETE statements. Therefore, executing DELETE statements on tables with certain types of check constraints may produce unexpected results. For example, consider the following statements executed on table CheckTbl.
INSERT INTO CheckTbl VALUES (10, 10)
GO
DELETE CheckTbl WHERE col1 = 10;
|
| Author: Sabu C Alex 29 Aug 2008 | Member Level: Gold | Rating: Points: 5 |
Hai narendra
A constraint is a property, that can be assigned to a column or columns in a table that prevents certain types of inconsistent data values from being placed in the column(s). This ensures the accuracy and reliability of the data in the database.
PRIMARY KEY
UNIQUE
FOREIGN KEY
CHECK
NOT NULL
A CHECK constraint is used to limit the values that can be placed in a column.
|
| Author: Athira Appukuttan 29 Aug 2008 | Member Level: Diamond | Rating: Points: 6 |
Use a constraint to define an integrity constraint—a rule that restricts the values in a database. Oracle Database lets you create six types of constraints and lets you declare them in two ways.
The six types of integrity constraint are described briefly here and more fully in "Semantics ":
A NOT NULL constraint prohibits a database value from being null.
A unique constraint prohibits multiple rows from having the same value in the same column or combination of columns but allows some values to be null.
A primary key constraint combines a NOT NULL constraint and a unique constraint in a single declaration. That is, it prohibits multiple rows from having the same value in the same column or combination of columns and prohibits values from being null.
A foreign key constraint requires values in one table to match values in another table.
A check constraint requires a value in the database to comply with a specified condition.
A REF column by definition references an object in another object type or in a relational table. A REF constraint lets you further describe the relationship between the REF column and the object it references.
You can define constraints syntactically in two ways:
As part of the definition of an individual column or attribute. This is called inline specification.
As part of the table definition. This is called out-of-line specification.
NOT NULL constraints must be declared inline. All other constraints can be declared either inline or out of line.
|
| Author: Swaminathan 29 Aug 2008 | Member Level: Gold | Rating: Points: 3 |
Primary key constraint-Identification key(not null and unique) for master table
Foreign key constraint are used to relate master table to other table
Check constraint is used to limit the range of data inside column of database table.
|
| Author: http://venkattechnicalblog.blogspot.com/ 31 Aug 2008 | Member Level: Diamond | Rating: Points: 3 |
Conditions enforced on the column is referred to as constraints.
Checking the data on the table before dml/ddl operations can be considered as check constraints.
Regards,
Venkatesan Prabu .J
http://venkattechnicalblog.blogspot.com/
|
| Author: Sriram 05 Sep 2008 | Member Level: Gold | Rating: Points: 5 |
constraint Provides the Mechanisam it provides integrity in the database
To Create a tabel before to check the conditions like
Domain Integrity constraints (Not null,check)
entity Integrity Constraints(Primary Key,Unique Key)
Referential integrity constraints
check constrain:
To check condition for Particular Table
create table employee (empid int constraint pk_emp check (empid=100))
|