Question { Thinksoft, 24939 }

What is data integrity? Explain constraints?

Answer

A constraint is a property assigned to a column or the set
of columns in a table that prevents certain types of
inconsistent data values from being placed in the column
(s). Constraints are used to enforce the data integrity.
This ensures the accuracy and reliability of the data in
the database. The following categories of the data
integrity exist:


Entity Integrity
Domain Integrity
Referential integrity
User-Defined Integrity

Entity Integrity ensures that there are no duplicate rows
in a table.
Domain Integrity enforces valid entries for a given column
by restricting the type, the format, or the range of
possible values.
Referential integrity ensures that rows cannot be deleted,
which are used by other records (for example, corresponding
data values between tables will be vital).
User-Defined Integrity enforces some specific business
rules that do not fall into entity, domain, or referential
integrity categories.

Each of these categories of the data integrity can be
enforced by the appropriate constraints. Microsoft SQL
Server supports the following constraints:


PRIMARY KEY
UNIQUE
FOREIGN KEY
CHECK
NOT NULL

A PRIMARY KEY constraint is a unique identifier for a row
within a database table. Every table should have a primary
key constraint to uniquely identify each row and only one
primary key constraint can be created for each table. The
primary key constraints are used to enforce entity
integrity.

A UNIQUE constraint enforces the uniqueness of the values
in a set of columns, so no duplicate values are entered.
The unique key constraints are used to enforce entity
integrity as the primary key constraints.

A FOREIGN KEY constraint prevents any actions that would
destroy link between tables with the corresponding data
values. A foreign key in one table points to a primary key
in another table. Foreign keys prevent actions that would
leave rows with foreign key values when there are no
primary keys with that value. The foreign key constraints
are used to enforce referential integrity.

A CHECK constraint is used to limit the values that can be
placed in a column. The check constraints are used to
enforce domain integrity.

A NOT NULL constraint enforces that the column will not
accept null values. The not null constraints are used to
enforce domain integrity, as the check constraints.

Question { 8744 }

Why we need a group by clause?

Answer

We use GroupBy clause to analyze data in table,by means of
various sql server aggregate functions like SUM(), AVERAGE
() etc., according to some domain.

For eg., If we want to know the sales done by a every
employee, then we would use somewhat this kind of query:

/**
select employeeid, count(*)
from orders
groupby employeeid
order by employeeid
**/

Question { Wipro, 33577 }

What is the difference between constraints and triggers?

Answer

I dont' think Triggers are performanc wise more superior to
constraints. Constraints are isolated to table level
whereas triggers are flexible to do the validation on any
number of tables.

Question { 5466 }

Say if we have a table that contains only a single column ,
say OrderID, which has IDENTITY attribute defined on it. So
how can we insert data in this table.

I am reframing my question, that how can we make the table
to increment the column "OrderID" value several times???

Answer

Madhu, actually the answer which u have given is quite
common. Now i m gonna tell u a new one. The above question
has one more solution that is stated below:


"Insert into Table_name DEFAULT values"


that's it.