February 15, 2015

Difference between primary key and unique key,Find Primary key,Index help,Not Null in SQL

Difference between primary key and unique key

A table can have only one primary key but multiple unique keys.
unique key may have null value which is not allowed in primary.
simply primary key is unique + not null where as unique key Field contains any number of NULL values.

Find primary key, index_name ,index key  using sp_Help command in sql

How to find primary key, index_name and index_key in the same query

For a particular table and also want to group by index_name
use sysindexes table to get the output you want

use the stored procedure sp_helpindex [object name]

sp_help 'table name'

Null and Not Null value in Primary and unique keys in sql

Primary key should contain unique value and not null because if attribute value is null than how should maintain the uniqueness..

one more think i want to add that Primary key is a type of constraint and foreign key is also a type of
constraint and pk is null then u can't use the null value as a foreign key.
So, Primary key should be a unique value and not null.

Well first of all null is not a value its a state, null is undefined , so we cannot have null as primary key as we do not know its value also value of one null is different from another null, so if we allow null values each null is unique in a sense , but since we do not know what it stands for we do not allow it in primary key.

Though in case of foreign key we do allow null value reason being
a child(foreign) cannot point towards an non existent parent(primary key) but a child can be orphan it self hence we can allow null in foreign key.

As primary key column is the candidate column in the table where the data is going to be identified by this column itself so it should have a value and the unique value .

Primary key is a unique key which should not accept any null values.& it doesn't allow any duplicates also.whenever table is created the first record will be filled with null values. whenever i create a primary key constraint on table internally an unique index should be created.which doesn't allow any nulls.Null is a missing or unknown value