Hello Friend toady you will get what are constraints in Database and how to implement to these
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
Domain Integrity NULL or NOT NULL
Referential Integrity FOREIGN KEY
Constraints are check or We Can Say Constriants are Like Validations.
Primary Key(Row Level Constraint) Features
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
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