I have a question regarding databases which made me stuck in my recent interview.
How we can identify unique records in any table without using any primary key or unique key ?
Plz share your ideas...
Where the interviewers referring to natural keys? Or composite keys? Thats a unclear question.
If you don't know already, a natural key is a column in a table that uniquely identified the records but is part of the natural data, say, a social security number or employee number, something that dircetly relates to the row in the way that a primary key doesn't but is still distinct among the records.
A composite key is a key made up of several fields from the table, the result of which is a unique key e.g. someone's phone and email combined.
Or they could have been after GUIDs...?
Hope that helps.
Interviewer said you need not to use any key in the table either that is a unique key or any natural key.
So nothing explicitly defined in the creation table statement, correct?
Maybe the question refers to implict keys like ROWID (available on Oracle & SQLite), or OIDs (Object Identifier in PostgreSQL) which are set by default by the database. However these are not available in all databases, like for example in MySQL.
yes hmm that may be possible for this case.so we can use RowID for uniquely identifying the records right ?
Can we use indexes instead of using keys for identifying unique records or can declare trigger for each unique record?
so we can use RowID for uniquely identifying the records right ?
Depends how much uniqueness needs to be extended: on SQLite it will be unique over the table, in Oracle it will be unique over the database and in PostgreSQL is not encouraged because:
The oid type is currently implemented as an unsigned four-byte integer. Therefore, it is not large enough to provide database-wide uniqueness in large databases, or even in large individual tables. So, using a user-created table's OID column as a primary key is discouraged. OIDs are best used only for references to system tables.
Can we use indexes instead of using keys for identifying unique records
An index can be defined by a primary key or by a unique key, both can be based by single or multiple columns. So no, at least at my knowledge and in reference to MySQL, you cannot define an index without defining which keys will be part of it.
or can declare trigger for each unique record?
A possible but expensive task, at least in MySQL, because it would require a full scan of the table each time you need to insert or update something. An index will speed up most of these operations.