Codechef4u is a community for computer professionals,by computer professionals,just like you; who loves sharing and helping each others,Join them
Share your post

Difference between Primary Key and Unique Key

Primary key and unique key seems identical, but reality is that both are different in features and behaviours.

In this article I will share key differences between unique key and primary key.

Primary Key:

The PRIMARY KEY uniquely identifies each record in a database table; main goal is to prevent duplicate values for columns and provides unique identifier to each column.

Unique key:

Unique key enforces uniqueness or prevent duplicate data on columns.
Single null value is allowed with unique key

Differences :

Difference

Primary Key

Unique Key

Limit

We can have only one Primary key in a table

We can have more than one unique key in a table.

Null values

Primary Key can't accept null values.  

Unique key can accept only one null value.

Index

By default, Primary key is clustered index.

Unique key is a unique non-clustered index.

Columns

Only single unique column.

Combination of column values allowed.

Foreign key

Primary key can be made foreign key into self and another table.

With sql server, Unique key can be made foreign key into another table.

Auto Increment

Primary key supports Auto Increment value.

Unique Constraint doesn't support Auto Increment value.

 

SQL Server constraints interview questions and answers

SQL Server interview questions on data integrity constraints

In this article I will share some frequently asked data integrity constraints related interview questions and answers in SQL server.

1. What is table constraint?

 Constraints are used to specify rules on data to ensure quality data stored in table.

2. What are the Different Types of constraints?

=> Primary key constraint.

=> Foreign Key constraint.

=> Unique Key constraint.

=> Not Null constraint.

=> Check constraint

=> Default constraint

3. What is default constraint?

The DEFAULT constraint provides a default value to a column when an INSERT statement doesn't explicitly assign a particular value.

More info.. http://codechef4u.com/post/2015/05/04/default-constraint-in-sql-server

4. Explain primary key and foreign key constraints?

Foreign key is a column or field in current table and primary key of another table, the foreign key points to another table using primary key of that table.

The PRIMARY KEY constraint uniquely identifies each record in a database table; main goal is to prevent duplicate values for columns and provides unique identifier to each column.

More info.. http://codechef4u.com/post/2015/04/20/how-to-create-primary-key-constraint

http://codechef4u.com/post/2015/05/03/foreign-key-constraints

5. Explain PRIMARY KEY, FOREIGN KEY and Unique key?

PRIMARY KEY:

The PRIMARY KEY uniquely identifies each record in a database table; main goal is to prevent duplicate values for columns and provides unique identifier to each column.

FOREIGN KEY:

Foreign key is a column or field in current table and primary key of another table, the foreign key points to another table using primary key of that table.

Unique key:

Unique kay enforces uniqueness or prevent duplicate data on columns.

Single null value is allowed with unique key.

6. What is UNIQUE KEY Constraint?

Unique constraints are rules that enforce uniqueness or prevent duplication on column data.

More info.. http://codechef4u.com/post/2015/05/05/unique-constraint-in-sql-server

7. What is CHECK Constraint?

CHECK constraints are rules used to limit values that are accepted by one or more columns.

For example, Products with unit price between INR 5000 to INR 100000 are allowed only. This prevents product unit price from being entered beyond the regular price range.

   The logical expression would be the following: UnitPrice>= 5000 AND UnitPrice <= 100000

More info.. http://codechef4u.com/post/2015/04/24/check-constraints-in-sql-server

8. What is NOT NULL Constraint?

 

9. What is the difference between primary key and unique key?

Primary Key:

Primary Key can't accept null values.  

By default, Primary key is clustered index.

We can have only one Primary key in a table

Unique Key:

Unique key can accept only one null value.

We can have more than one unique key in a table.

Unique key is a unique non-clustered index.

10. What is data integrity and list types of data intigrity?

Data integrity is the consistency and accuracy of data which is stored in database.

For data integrity RDBMS follows ACID rules, enforcing data integrity ensures the quality of the data in the database.

Types:

Entity Integrity (Unique constraints, primary key)

Domain Integrity (Check constraints, null constraints, default constraints, foreign key)

Referential Integrity (Foreign key and check constraints)

User-Defined Integrity (all column- and table-level constraints in CREATE TABLE, stored procedures, and triggers).

11. Can you add constraint on table that has already some data?

Yes we can, you require to modify data in some cases for example if you want to add null constraint and

Table contains some null value in that’s case you have to add some data in column.

12. What is User-Defined Integrity?

User-defined integrity allows you to define specific business rules that do not fall into one of the other integrity categories. All of the integrity categories support user-defined integrity (all column- and table-level constraints in CREATE TABLE, stored procedures, and triggers).

13. What is referential Integrity?

Referential integrity preserves the defined relationships between tables when records are entered or deleted. In SQl server, referential integrity is based on relationships between foreign keys and primary keys or between foreign keys and unique keys (through FOREIGN KEY and CHECK constraints).

14. What is domain Integrity?

Domain integrity is the validity of entries for a given column. You can enforce domain integrity by restricting the type (through data types), the format (through CHECK constraints and rules), or the range of possible values (through FOREIGN KEY constraints, CHECK constraints, DEFAULT definitions, NOT NULL definitions, and rules).

15. What is entity Integrity?

Entity integrity defines a row as a unique entity for a particular table. Entity integrity enforces the integrity of the identifier column(s) or the primary key of a table (through indexes, UNIQUE constraints, PRIMARY KEY constraints, or IDENTITY properties).

16. While creating foreign key you don’t want to check referential integrity, which keyword you have to use?

You required WITH NOCHECK in such case.

17. Can we apply unique constrains on multiple columns?

Yes that is possible.

18. How many primary keys are possible with single table?

Only one.

subquery interview questions and answers

In this article I will share some frequently asked subquery related interview questions and answers in SQL server.

Sub-query questions and answers:

1. What is subquery? Explain the Properties of a Subquery?

Select Query inside another query is a sub query, Select statement embedded in DML statement or nested in outer query.

You can use sub queries in SELECT, INSERT, UPDATE, DELETE whenever expressions are used.

Properties:

1.       Sub query cannot include compute or for browse clause.

2.       Must include sub query in parentheses.

3.       Subquery can include where ,group by, having clauses these are optional.

4.       Up to 32 levels sub query can be nested.

5.       You can use order by clause only when top clause included.

2. What are different types of subquery?

1.   Self contained sub query
2.   Co-related sub query

3. What is Self contained sub query?

Sub query is completely independent and do not require any input from outer query called self contained sub query.

4. Explain different types of Self contained sub query?

There are three types of self contained sub queries

1. Self contained scalar sub query.

2. Self contained multi valued query.

3. table-valued sub query.

 

5. What is self contained scalar sub query?

A self-contained sub query that returns single value called self-contained scalar sub query.

 More info http://codechef4u.com/post/2015/04/09/self-contained-sub-query

 

6. What is self contained multi valued query?

Self contained multi-valued sub query still return a single column but it may produce multiple values for the column.

 More info http://codechef4u.com/post/2015/04/10/self-contained-multi-valued-sub-query

7. What is table-valued sub query?

Sub query returns a whole table; multiple columns, multiple rows.

  More info http://codechef4u.com/post/2015/04/09/table-valued-sub-query

8. What is Co-related sub query?

Sub query depends on the outer query for its values. This means that the sub query is executed repeatedly, once for each row that might be selected by the outer query.

More info http://codechef4u.com/post/2015/04/09/correlated-sub-queries

9. How can we Rewrite Sub-Queries into Simple Select Statements or with Joins?

Yes we can write using Common Table Expression (CTE). A Common Table Expression (CTE) is an expression that can be thought of as a temporary result set which is defined within the execution of a single SQL statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query.


10. Exists predicate is possible with subquery? If yes explain?

T-SQL supports predicate like EXISTS with sub query, Exists accept sub query as input returns true if any rows exist in sub query else return false.

More info http://codechef4u.com/post/2015/04/09/exists-predicate-with-sub-query

 

11. What happens to null value with EXISTS using subquery?

If sub query returns null value, exists return true, I was surprised with result but its fact.
Exists statement resolves null value with True.

More info http://codechef4u.com/post/2015/04/09/exists-predicate-with-sub-query

 

12. Explain the subqueries with comparison operators?

Comparison operators can be used (like <, >, =, !> etc). Sub queries used with comparison operators must return a single value rather than a list to avoid error. Hence the nature of the database must be knows before executing such sub queries.

Example:

SELECT Productid FROM Products WHERE categoryId= (SELECT top 1 Id FROM Categories WHERE CategoryTiltle=’Electronics’ and DeletedDate is null)

13. Explain Subqueries with IN and NOT IN?

In this example subquery returns products from Products table where discount>1000. Using IN first all Products are selected and compared to each row of the sub-query.

Not in is opposite to IN (Vice versa)

IN example:

SELECT * FROM Products WHERE Id in(select productid from Discounts Where Discount_amt >1000); 

Not IN example:

SELECT * FROM Products WHERE Id not in(select productid from Discounts Where Discount_amt> 1000);