Hacker News Reader

Comment by mkleczek | Hacker News Reader

> The surrogate key uniquely identifies a row in your database, which is an entity just as real and significant as the car or the employee or what-have-you. Don't confuse the two!

But the DBMS already maintains a row identifier (called rowid or ctid or whatever depending on the DBMS). Why do you need an explicit one?

View on HN

Be careful with those. SQLite has a rowid concept, but it's not guaranteed to be stable - running a VACUUM against a table can reassign the rowids for every row!

https://sqlite.org/rowidtable.html says:

> If the rowid is not aliased by INTEGER PRIMARY KEY then it is not persistent and might change. In particular the VACUUM command will change rowids for tables that do not declare an INTEGER PRIMARY KEY. Therefore, applications should not normally access the rowid directly, but instead use an INTEGER PRIMARY KEY.

In the DB we use[1] the internal row id is not stable:

The value returned by the function is not necessarily constant between queries as various operations performed on the database may result in changes to the row identifiers of a table.

So, users should refrain from using the ROWID function in ordinary situations; retrieval by primary key value should be used instead.

[1]: https://infocenter.sybase.com/help/index.jsp?topic=/com.syba...

Because every DB can and will shift those as needed. They reference the physical location on disk for a given tuple. They are not meant for general consumption.

It may be useful if you have data that originates from another source or if something outside of our system references your entity. In that case you need to keep some form of an externalRef, so it's usually easier to just use an id that you can control, for referencing both internally and externally.

Reply on news.ycombinator.com