Why we use a primary key if we manage uniqueness & auto-increment from code?

Question

I know we use primary key to Identity each row uniquely, but if we manager all from code then? What exactly use of primary key?

have a look at this post https://stackoverflow.com/questions/831850/should-a-database-table-always-have-primary-keys — Esteban P., Jun 14 '17 at 07:26
Then you needn't PK in DB. I saw rather big applications managing everything internaly, no PK, no FK on the DB server side. Just create some indices to speedup the queries — Serg, Jun 14 '17 at 07:30
Three terrible responses; none of them addresses your (duplicated) question. The primary reason? Because code is written by people who make mistakes and mistakes are made all the time. Learn from history - your courses should have covered the history of data management and how relational theory developed to solve the problems of inconsistent data. — SMor, Jun 14 '17 at 13:17

score 0 · Answer 1 · answered Jun 14 '17 at 07:29

0

By defining primary key your retrieval of data through key would be much faster.

answered Jun 14 '17 at 07:29

Muhammad Anas

score 0 · Answer 2 · answered Jun 14 '17 at 07:30

0

if you are using identity on id column you dont need to use primary key at the same column

answered Jun 14 '17 at 07:30

Well, I think using an identity on an id column doesn't necessarily make that column a primary key column, in the case of composite key in a table, where I can have multiple columns as my primary key but have an identity on a column – Dixon Jun 14 '17 at 08:03

score 0 · Accepted Answer · answered Jun 14 '17 at 07:31

Without an index, user deletion of a parent row from the database would force the SQL Server query engine to scan the child table referenced in the foreign key to ensure that data integrity is not compromised. Consider a child table with hundreds of thousands of rows; an index can speed this lookup considerably.
The performance of the foreign key CASCADE options (ON DELETE CASCADE, ON UPDATE CASCADE) can be improved dramatically with the use of an index since the engine performs a query to search for the rows that should be automatically deleted or updated.
The performance of JOINs between the parent and child tables on the foreign key column is greatly improved. It's a natural assumption that tables that are related may be queried together to produce result sets. Consider the earlier ORDERHEADER/LINEITEM example. It would be natural for queries to be executed that would require not only elements of the LINE ITEMs but also of the ORDERHEADER (e.g. order date, the CSR who entered the order)
When developing and maintaining data models, examine where your model can benefit from a foreign key relationship
Ensure your foreign key columns are indexed to prevent table scanning during data deletion, cascading actions, and query JOINs.

3 Answers3