SQL Server Interview Questions -- Constraints

1) What is a Primary Key?
A Primary Key in a table uniquely identifies each and every row with out allowing Nulls. 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.

2) What is Unique Key?
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.

3) What is the difference between Primary Key and Unique Key?
3.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.
3.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.3)Also by default Primary key is created as clustured index and unique key is created as non clustered index.

4) 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.

5) What is RULE?
A rule specifies the acceptable values that can be inserted into a column. This is similar to CHECK constraint.

6) 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.

7) 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.

8) 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.

9) 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.

10) What is the table name, that contains Check Constraint Definition?INFORMATION_SCHEMA.CHECK_CONSTRAINTS, where CHECK_CLAUSE column stores the Definition of Check Constraint.

11) What is the table name, that contains RULE Constraint definition?
Syscomments is the table which contains the RULE definition.Use the below query to get the information.
select name as Rule_Name,sc.TEXT as Rule_Definition
from sysobjects so inner join syscomments scon so.id = sc.idwhere so.xtype='R'

12) Can we have RULE and Check Constraint on the same column?
YES

13) Can we apply Integrity Constraints on Computed Columns?
YES

14) Can you write a query to find out the Child Table Name and Parent Table Name and the Columns to join?
SELECT TC.CONSTRAINT_NAME AS FOREIGN_KEY_CONSTRAINT_NAME,TC.TABLE_NAME AS FOREIGN_KEY_TABLE_NAME, KCU.COLUMN_NAME, ORDINAL_POSITION AS COLUMN_POSITION,RC.UNIQUE_CONSTRAINT_NAME AS PARENT_PRIMARY_KEY_NAME, TC_PK.TABLE_NAME AS PARENT_TABLE_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC, INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU, INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC, INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC_PK
WHERE TC.CONSTRAINT_NAME = KCU.CONSTRAINT_NAME
AND TC.TABLE_NAME = KCU.TABLE_NAME
AND TC.CONSTRAINT_TYPE = 'FOREIGN KEY'
AND RC.CONSTRAINT_NAME = TC.CONSTRAINT_NAME
AND RC.UNIQUE_CONSTRAINT_NAME = TC_PK.CONSTRAINT_NAME

15) 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.

16) 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;

17) What is the order of Constraints execution?
There is no predified order. All the constraints on the column gets executed.

18) what is difference between Primary Key, Unique Key and Alternative Key?
Primary Key ==> Is used to uniquely identify records and doesn't allow NULL values.
Unique Key ==> Is also used to uniquely identify records but does alow NULL Values per column combination.
Alternate Key ==> Is another type of Unique key, which is used to identify each record uniquely. This is not a constraint. This is just a terminology.