Constraint In MSSqlserver

Hello Friend toady you will get what are constraints in Database and how to implement to these

CONSTRAINTS

Data integrity controls help to guarantee the accuracy, validity, and correctness of data stored in a database. Data integrity controls can be thought of as rules regarding what is considered to be valid data. Data integrity can be enforced using stored procedures, or it can even be enforced outside of SQL Server in the calling application. However, data integrity is mainly enforced by constraints and triggers. Constraints are additional rules applied to a table that determine valid data. They are automatically enforced by the database engine. Triggers are special stored procedures that execute in response to events on the server. Data Manipulation Language (DML) triggers can respond to, or execute instead of insert, update, and delete events on a table or view. Default and rule objects are similar to constraints, but they are created as separate objects.

Data Integrity Categories

There are three categories of data integrity: entity integrity, domain integrity, and referential integrity. Categories refer to the area of the table where a data integrity rule is enforced.

Entity integrity refers to the uniqueness of rows.

Domain integrity refers to the values allowed in a column, and

referential integrity preserves the relationships between tables.

Procedural or declarative integrity can be used to enforce any of the data integrity categories  Entity integrity ensures that each row in a table can be uniquely identified by a column or set of columns. Entity integrity is enforced by using indexes to check for duplicate values. Primary key and unique constraints are both used to enforce entity integrity.

Domain integrity determines the allowable values for a column. The Datatype used for the column is one way that domain integrity is enforced. A Check constraint allows us to limit the values allowed in a column based on a logical expression. A Foreign Key constraint determines the values allowed in a column based on the contents of a primary key or unique constraint. Domain integrity can be enforced using procedural methods when constraints don’t offer enough flexibility.

Referential integrity preserves and enforces relationships within a database. Foreign key and Check constraints are declarative methods of enforcing referential integrity. Check constraints can reference multiple columns in the same table using an expression. A Foreign Key constraint references a primary key or unique constraint, usually in another table, to maintain the relationship. Referential integrity can also be enforced procedurally by using triggers. Triggers are useful for relationships that are too complex to model using foreign keys.

Entity Integrity                                       PRIMARY KEY

UNIQUE

Domain Integrity                                     NULL or NOT NULL

DEFAULT

CHECK

FOREIGN KEY

Referential Integrity                                                FOREIGN KEY

CHECK

Constraints  are check or We Can Say Constriants are Like Validations.

Primary Key(Row Level Constraint) Features

———————————————————

Not Null

Unique

Can’t be Multiple

Indexing is possible for fast retrieving of Data

Clustered Index(Always 1 in a table)

Non-clustered – 249 Indexes.

Size of Primary Key  900 Bytes

We Can alter the Clustered Index.

——————————————————–

Length of Foreign Key 253

Unique Key

May be multiple

Only 1 time null can occur

How Can Implement Constraints on a Table.

Remember Point :-

Foreign key Can be duplicate

FK may be null

Default for a table

Defaults for multiple tables

1)     How Can we Implement FK on a object.

create table tbcons (empno int primary key,ename varchar(50) unique,eadd varchar(50) default’no add’,default getdate(),esal int check(esal>0), sts char(1) check(sts in (‘Y’,’N’)))

2)     How can We Check Our Constraints

exec sp_helpconstraint tbcons

3) How Can We Insert Values on a Constrained Table

insert tbcons values(1,’Aryan’,DEFAULT,DEFAULT,100,’Y’)

4) How Can we give User Defined named to Constraints

create table tbcons1(empno int primary key,esal int constraint chk_esal check(esal>1000 and esal <=2000))

–Check Constraints have implemented on Your Table or Not

exec sp_helpconstraint tbcons1

–Insert Some Records in Your Table

insert tbcons1 values(2,1500)

–Select Data From Table

select *from tbcons1

1) How Can We Implement Table Level Constraints

create table tbcons2 (empno int not null,ename varchar(100),esal int ,hra int,constraint chk_hra_esal check(esal>hra))

————————————————————-

insert tbcons2 values(1,’Aryan’,2000,1500)

————————————————————-

select *From tbcons2

1) How can we create default

create default dd1 as 2000

————–

create default dd2 as getdate()

–This Default If You Want to Enter default date in Any Table

–These is a Default Constraint For Every Table

–How Can we Apply these default tables on a table

create table tbtest(empno int,esal int,doj date)

select *From tbtest

–How Can we Manuplate Default constraints on This Table’s Column

exec sp_bindefault ‘dd1’, ‘tbtest.esal’

exec sp_bindefault ‘dd2′,’tbtest.doj’

–Check Constraints have apply

exec sp_helpconstraint tbtest

–Insert some records in the table

insert tbtest values(1001,default,DEFAULT)

select *From tbtest

–How Can We Unbind default  constraint

exec sp_unbindefault ‘tbtest.esal’

exec sp_unbindefault ‘tbtest.doj’

–Check Your Constraints

exec sp_helpconstraint tbtest

1) How can we Make Common Rules

create rule rr1 as @es>0

–How can we Bind this rule to Table

exec sp_bindrule ‘rr1′,’tbtest.esal’

–INsert records in the table

insert tbtest values(12,-100,GETDATE())

insert tbtest values(12,100,GETDATE())

–How Can We Unbind Rule from a  table

exec sp_unbindrule ‘tbtest.esal’

–How Can We Drop a Rule

drop rule rr1

2) How Can We Make Realtion Between Two Tables

–Make a Master Table of Department

create table tbdep

(dno int primary key,dname varchar(50) unique)

insert tbdep values(10,’Acct’)

insert tbdep values(20,’Sales’)

insert tbdep values(30,’Purch’)

insert tbdep values(40,’copa’)

–Now Create a  Tbemp Table

create table tbemp (empno int not null,ename varchar(50),eadd varchar(100),esal int, edno int references tbdep(dno))

insert tbemp values(3,’Rryan’,’Patiala’,12000,20)

select *From tbemp

–***********************************************************************

Advertisements
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s