1) What are constraints?
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.
2)What are the different types of data integrity?
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.
3)What are the different types of constraints available in sql server
❑ PRIMARY KEY constraints
❑ FOREIGN KEY constraints
❑ UNIQUE constraints (also known as alternate keys)
❑ CHECK constraints
❑ DEFAULT constraints
❑ Rules
4)What is a Primary Key?
Primary keys are the unique identifiers for each row. They must contain unique values (and hence cannot
be NULL). Because of their importance in relational databases, primary keys are the most fundamental of
all keys and constraints.
5)How many types of primary keys exist?
There are two types of Primary Keys:
1.1) Simple Primary Key ==> Defining primary key on a single column is called Simple Primary Key.
1.2) Composite Primary Key==> Defining Primary Key on more than one column is called Composite Primary Key.
6)What is an unquie key? Name the different types of Unique keys?
A Unique Key in a table uniquely identifies each and every row and allowing Nulls per column Combination. There are two types of Unqiue Keys:
1.1) Simple Unique Key ==> Defining Unique key on a single column is called Simple Unique Key.
1.2) Composite Unique Key==> Defining Unique Key on more than one column is called Composite Unique Key.
7) What is Check Constraint?
Check constraint specifies a condition that is enforced for each row of the table on which the constraint is defined. Once constraint is defined, insert or update to the data within the tables is checked against the defined constraint.
8) What is Rule?
A rule specifies the acceptable values that can be inserted into a column. This is similar to CHECK constraint.
9)What is foreign Key?
Foreign keys are both a method of ensuring data integrity and a manifestation of the relationships between tables. When you add a foreign key to a table, you are creating a dependency between the table for which you define the foreign key (the referencing table) and the table your foreign key references (the referenced table).
10)What is candidate key?
A Candidate Key can be any column or a combination of columns that can qualify as unique key in database. There can be multiple Candidate Keys in one table. Each Candidate Key can qualify as Primary Key.
11) What is the difference between Primary Key and Unique Key?
1)Primary Key by definition cannot be null, where as unique key can accept null values but if the unique key is defined on a column which is not null , then this unique key can also be used as an alternate primary key functionality to identify unique rows in a table.
2)By definition you can have only one primary key defined on a table where as you can have multiple unique keys defined on a table
3)Also by default Primary key is created as clustured index and unique key is created as non clustered index
12)What is the difference between RULE and Check Constraint?The major difference between rule and Check is reusability. Check constraint is associated with columns in a Table. So these can't be re-used. Rules are defined with in a database and can be applied to any number of columns.
13)How to create a relationship between two tables? Using Foreign Keys. Create a Foreign Key on child table referencing Unique Key or Primary key of Parent table.
14)What is the table name,that contains the Primary Key, Unique Key and Foreign Key Information?
INFORMATION_SCHEMA.TABLE_CONSTRAINTS, where CONSTRAINT_TYPE column stores the information of Constraint Type.
15)Can we create a Foreign Key with out Primary Key?
Yes. If the table has Unique Key then it is posible to create a Foreign key constraint.
16) Can we have RULE and Check Constraint on the same column?YES
17) Can we apply Integrity Constraints on Computed Columns?
YES
18) Can you drop a Parent Table with out affecting its child tables?
No. First you need to drop all the Foreign Key relationships and then only you can drop Parent Table.
19) How to disable and Enable the constraints?
You need to use ALTER TABLE statement to disable constraint.
ex: ALTER TABLE ACCOUNT NOCHECK CONSTRAINT CHECK_IN_AMOUNT;
20) What is the order of Constraints execution?
There is no predefined order. All the constraints on the column gets executed.
21)Name the data integrity enforced by the sql server constraints?
1)The primary key constraints are used to enforce entity integrity.
2)The unique key constraints are used to enforce entity integrity as the primary key constraints
3)The check constraints are used to enforce domain integrity.
4)The not null constraints are used to enforce domain integrity, as the check constraints.
