Can we have a functionality of Primary Key without creating a Primary Key? [Resolved]

Posted by Neerajprasadsharma under Sql Server on 5/19/2017 | Points: 10 | Views : 90 | Status : [Member] | Replies : 1
Is it possible to have a functionality of Primary Key without creating a Primary Key?

I write technical articles mainly on SQL Server Query Optimizer for more information look at my BIO.


Responses

Posted by: Neerajprasadsharma on: 5/19/2017 [Member] Starter | Points: 50

Up
1
Down

Resolved

We are posting a series of questions and answers, on different topics, the idea is to combine all the related questions and
answer together to help the community.
this question is part of the series, everybody is welcome to contribute in it, you can ask question on the topic or answer.
Back to the question again.
Before jumping directly to the answer first go back and try to remember what are the functionality of the Primary Key:

1. Uniquely identifies the row (s) : Primary Key is a constraint which identifies a row uniquely in the table. A key value cannot
duplicate if the Primary Key constraint is the column.

2. Not Null : Primary Key cannot be NULL, Primary Key data cannot contains NULL values. For more details you can look
the link below:
http://www.dotnetfunda.com/forums/show/22479/why-primary-key-doesnt-accept-null-values
3 Allow Referential integrity : in simple words, we can create a parent child relationship using the Primary Key and foreign key, for more details
you can see the below link:
http://www.dotnetfunda.com/articles/show/3512/beginners-guide-foreign-key-in-sql-server
4 By default creates Clustered Index : Primary Key by default create clustered index on the table, which helps in fast retrieval of the data.

So all are the above main functions of the Primary Key, so on the other hand question is can we have functionality without creating the Primary Key?
The answer is YES.

For users perspective NOT NULL Clustered Index provide all the functions as provided by Primary Key.

1 it is unique .
2 Can be NOT NULL .
3 Can act as a parent with referential integrity.
4 can create clustered index on it.
Example:

Create Table Uniquek (ID INT NOT NULL UNIQUE CLUSTERED)

Create Table ChildTbl (ID INT CONSTRAINT FKUK FOREIGN KEY REFERENCES Uniquek(ID) , Name varchar(10) )


The above Uniquek table is unique,NOT NULL and Clustered on the ID column, and ChildTbl table is the child table of Uniquek table.




Other Related Question in series:
Can We Create Primary Key with Clustered Index in SQL Server?
How to create a Primary Key without Clustered index?
Why SQL Server by default creates clustered index on the Primary Key?
Can we create a Primary Key without clustered index?
Can we create Primary Key without any index?
Can we create Primary Key on more than one column?
Can we have a functinality of Primary Key without creating a Primary Key?
Why Primary Key doesn`t accepts null values?
What is the need of Primary Key?
Is it mandotary to have a Primary Key in the table?

We will try to answer all the above questions and link all of them to each other,
We welcome everybody to contribute to this question answer series.

I write technical articles mainly on SQL Server Query Optimizer for more information look at my BIO.

Neerajprasadsharma, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response