0

hi guys,
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...

thanks

3
Contributors
5
Replies
24
Views
2 Years
Discussion Span
Last Post by cereal
2

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.

0

i tried to answer the question with this explanation but it didn't worked. Interviewer said you need not to use any key in the table either that is a unique key or any natural key.

1

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.

Edited by cereal

0

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?

1

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.

You wrote:

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.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.