July 15, 2011 at 3:46 am
Dear Sir,
Why does SQL Server allow to create a Table Without a Primary key ?
What is the intention behind this ?
Kindly reply.
July 15, 2011 at 4:26 am
Why not?
What are the issues faced becuase of this?
Why do you think SQL Server should force you to have a primary key?
July 15, 2011 at 4:29 am
duplicacy issue .
July 15, 2011 at 4:54 am
If lack of primary/unique key constraint caused duplicate rows in your table,
you can delete the duplicate rows now and add a primary/unique key constraints to your existing table to fix the issue.
All the tables don't need primary/unique key. That is why SQL Server allows tables to be created without PK.
In some cases like transaction/merge replication, filesteam column, foriegn key, SQL Server forces you to have GUID/PK.
This is my 2 cents. Other may give some better explanation.
July 15, 2011 at 5:00 am
I just want to know why does SQL Server allow duplicate value. What is the
purpose behind it ?
duplicate value may create some issues.
July 15, 2011 at 5:08 am
It is database design issue. Not RDBMS issue.
I think no RDBMS (Oracle, SQL Server, MySQL, etc.) forces you to have PK.
I think forcing PK is against database design principle.
(Others may give you better explanation)
July 15, 2011 at 5:12 am
Subrata Bauri (7/15/2011)
I just want to know why does SQL Server allow duplicate value. What is thepurpose behind it ?
duplicate value may create some issues.
In most cases it is undesirable to not have a Primary Key on a Table.
But what if you got some raw data and it had duplicates but you wanted to load it into a table, delete duplicates and then define a Primary Key and or Unique Constraint. I like the flexibility of being able to create a table without a Primary Key.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 15, 2011 at 5:14 am
Subrata Bauri (7/15/2011)
Dear Sir,
Why not Dear Ma'am? 😀
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 15, 2011 at 5:25 am
Enforcing primary key on a table is purely a design decision , while sql doesn't force it; it allows suitable substitutes in columns such as identity columns , timestamps etc .
The role of the primary key is dual :-
To help uniquely identify each row in the table
To ensure duplicate rows are not inserted .
However the first can be achieved simply by adding a identity column while the second is driven by the kind of data being inserted into the table.
SQL will not intuitively decide one over the other for you.
July 15, 2011 at 5:35 am
Jayanth_Kurup (7/15/2011)
Enforcing primary key on a table is purely a design decision , while sql doesn't force it; it allows suitable substitutes in columns such as identity columns , timestamps etc .The role of the primary key is dual :-
To help uniquely identify each row in the table
To ensure duplicate rows are not inserted .
However the first can be achieved simply by adding a identity column while the second is driven by the kind of data being inserted into the table.
SQL will not intuitively decide one over the other for you.
Nicely stated but I'm sure that you know that by creating an Identity Column does not prevent duplicates.
If you select a surrogate key (Identity) as opposed to a natural key you increase the probability of duplicate records.
Respectfully,
WC
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 15, 2011 at 5:48 am
July 15, 2011 at 5:52 am
Jayanth_Kurup (7/15/2011)
Correct. I proposed identity column as primary key only to uniquely identify each row in the table.So the identity column itself will never have duplicates.
Yes the Identity value will be unique but that does not prevent entering a duplicate record.
For example if a user performs a typo on the name or there is already a record with a phone number this will result in a duplicate record.
Are we on the same page?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 15, 2011 at 6:03 am
Jayanth_Kurup (7/15/2011)
However the first can be achieved simply by adding a identity column while the second is driven by the kind of data being inserted into the table.
Identities alone don't prevent duplicates. I can insert a specific value into an identity column multiple times, SQL won't complain. To enforce uniqueness you need a primary key, unique constraint or unique index.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 15, 2011 at 6:12 am
Jayanth_Kurup (7/15/2011)
Enforcing primary key on a table is purely a design decision , while sql doesn't force it; it allows suitable substitutes in columns such as identity columns , timestamps etc .The role of the primary key is dual :-
To help uniquely identify each row in the table
To ensure duplicate rows are not inserted .
However the first can be achieved simply by adding a identity column while the second is driven by the kind of data being inserted into the table.
SQL will not intuitively decide one over the other for you.
Dear sir,
I know the role of the primary key in a Table very well.
I think design a Table with the Primary key(s) is logically correct compare to without Primary key concept.
Can you Show me that you are getting more advantage(s) from a non-primary Key Table compare to a Table which has primary key.
Note :- Suppose both the Table have more or less same structure.
July 15, 2011 at 6:18 am
The one case that comes to mind is a staging table. If I'm importing data from some other system and I know that it's got data integrity issues, I'll use a staging table with no keys at all. No benefits to having keys on the table and having them could cause the data import to fail.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 32 total)
You must be logged in to reply to this topic. Login to reply