Thursday, December 30, 2010
A unique identity in each row in a table is called Primary key. Primary key does not allowed null values and duplicate values. If table have duplicate values and you are applying primary key, it will return you an error message. You can modify and delete the primary key. You cannot change the data type of a column if primary key constraint exists.
Here’s is a query.
SELECT TC.TABLE_NAME AS [TABLE name] ,
CCU.COLUMN_NAME AS [column name] ,
TC.CONSTRAINT_NAME [constraint name]
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CCU
ON Tc.CONSTRAINT_NAME = CCU.CONSTRAINT_NAME
AND TC.CONSTRAINT_TYPE = 'PRIMARY KEY'
- There is no chance of duplicate values.
- Primary key column can not contain null value.
- Other candidate keys are functionally dependent because of primary key constraint.
- It is easy to update a record.
- There is chance of problem in merge replication.
- When then table is full scan it sort the table make the query slow.
Note: If you have null or duplicate values in the existing table and you are trying to create a primary key constraint, it will always return you error message as shown below.
Msg 8111, Level 16, State 1, Line 1
Cannot define PRIMARY KEY constraint on nullable column in table '[TABLE NAME]'.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.