July 19, 2011 at 4:32 am
I think in some transactional logging tables or even metric-only fact tables, you could well get (correctly) duplicate rows. In the past I've added a surrogate key in such case to artificially create a distinction, but that's purely a design decision; there's nothing that says it is absolutely required.
If your table feels like it needs a primary key, the chances are it does. So go ahead and create one. 🙂
July 22, 2011 at 10:02 am
Subrata Bauri (7/15/2011)
Why does SQL Server allow to create a Table Without a Primary key ?What is the intention behind this ?
Microsoft is just following the SQL standard. SQL Server is not an RDBMS, it's a SQL DBMS and the SQL standard supports tables with duplicate rows.
It would be possible for SQL Server to require all tables to have at least one uniqueness constraint (PRIMARY KEY or UNIQUE). In fact you can implement that rule using Policy Based Management if you like. Unfortunately that wouldn't solve the problem of duplicate rows because even if every table has a key your SQL queries can still return duplicates. To fix that you'd have to redesign the SQL language quite considerably - to the point where it wouldn't really be SQL any more.
Duplicate rows is one of the nastiest problems in SQL - a flaw that I would guess must have cost $millions for SQL customers. A few minutes Googling for examples of people with problems dealing with duplicate rows is enough to demonstrate that.
PaulB-TheOneAndOnly (7/16/2011)
If you are not planning to enforce Referential Integrity on the particular table then PK is not needed.
A key is still needed for data integrity purposes even when you don't have a foreign key referencing it. The principal purpose of keys is uniqueness, identification of data and enforcement of dependencies, not RI.
July 22, 2011 at 11:48 pm
David Portas (7/22/2011)
Subrata Bauri (7/15/2011)
Why does SQL Server allow to create a Table Without a Primary key ?What is the intention behind this ?
Microsoft is just following the SQL standard. SQL Server is not an RDBMS, it's a SQL DBMS and the SQL standard supports tables with duplicate rows.
It would be possible for SQL Server to require all tables to have at least one uniqueness constraint (PRIMARY KEY or UNIQUE). In fact you can implement that rule using Policy Based Management if you like. Unfortunately that wouldn't solve the problem of duplicate rows because even if every table has a key your SQL queries can still return duplicates. To fix that you'd have to redesign the SQL language quite considerably - to the point where it wouldn't really be SQL any more.
Duplicate rows is one of the nastiest problems in SQL - a flaw that I would guess must have cost $millions for SQL customers. A few minutes Googling for examples of people with problems dealing with duplicate rows is enough to demonstrate that.
PaulB-TheOneAndOnly (7/16/2011)
If you are not planning to enforce Referential Integrity on the particular table then PK is not needed.A key is still needed for data integrity purposes even when you don't have a foreign key referencing it. The principal purpose of keys is uniqueness, identification of data and enforcement of dependencies, not RI.
Appreciate this post.
Viewing 3 posts - 31 through 32 (of 32 total)
You must be logged in to reply to this topic. Login to reply