Darren Duncan wrote:
>I think the real problem here is that SQL allows you to have non-distinct
>rows in a table, when all rows should be distinct.
SQLite's implicit "RowID" does not allow non-distinct values (except for NULL,
but this is documented behavior and only maintained for backwards
compatability. It might change in a future version. The sooner, the better,
IMHO).
>Working within SQL's flaws, the solution here is for every table to have a
>unique constraint on one or more table columns. Then applications just use
>that to uniquely identify the row.
This is exactly the concept of "RowID". Nothing wrong with that. My only
criticism is that this concept can be rendered non-functional by redefining the
"RowID" so that it violates the uniqueness constraint. Example:
CREATE TABLE x (
RowID TEXT);
Now the implicit unique RowID is no longer accessible via the "RowID" column.
Workarounds are "_rowid_" or "OID", but they can be overwritten as well:
CREATE TABLE x (
RowID TEXT,
_rowid_ text,
oid text);
For this table, it is no longer possible to access the implicit, unique RowID.
General database applications (GUI managers, for example) can no longer (re-)
identify a particular record!
>Rows should be identifiable by user-visible data, not hidden data, since a
>database is supposed to model reality and people identify things based on
>their someway-visible attributes.
This is what INTEGER PRIMARY KEY is for: It works as a duplicate for the
implicity "RowID":
CREATE TABLE x (
ID INTEGER PRIMARY KEY);
For this table, the visible "ID" and the implicit "RowID" access the same
unique data. This is the recommended usage and poses no problems. Problems only
arise if "RowID" is re-defined differently as demonstrated above!
>On a separate note, it is best for one to be able to name a table or column
>et al anything one wants, with all the choice of names as you can store in
>a text column for user data. Reserved words aren't an issue as long as
>entity names are referred to with an unambiguously different syntax, such
>as quoted identifiers as SQL does support. Then database users don't have
>to worry about implementation details and can name tables and columns
>whatever they want; saying they can't name their column "RowID" is a leaky
>abstraction.
Sure we all dislike restrictions. Can you suggest an alternative to a single
reserved name to represent the column which uniquely identifies a database
record under any and all circumstances?
Ralf
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
