5 ms·

> 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

> 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?

unchar1 2y ago
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.
magicalhippo 2y ago
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.sybase.help.sqlanywhere.12.0.1/dbreference/rowid-function.html https://infocenter.sybase.com/help/index.jsp?topic=/com.syba...
simonw 2y ago
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 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.
sgarland 2y ago
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.

unchar1 2y ago

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.

magicalhippo 2y ago

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.sybase.help.sqlanywhere.12.0.1/dbreference/rowid-function.html https://infocenter.sybase.com/help/index.jsp?topic=/com.syba...

simonw 2y ago

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 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.

sgarland 2y ago

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.