We help IT Professionals succeed at work.

Is it ok to have a 'keyless' table in a database?

Fej
Fej asked
on
Medium Priority
835 Views
Last Modified: 2012-05-04
Hello,

I am structuring a database for the first time, and have been doing a little reading on 'good' database design.  

I am under the impression that all 'good' database tables should have a unique key for each record.  Is this correct?  Or is it ok do have a table where the records don't have a unique key?

Thanks,

Fej
Comment
Watch Question

Commented:
There's nothing physically wrong with this.  Databases allow for it.  But here's the effect...

With no unique key, you might have trouble deleting the records you want.  (DELETE myTable where ???)
THis also means you can't really refer to a record from another table.  Foreign Keys depend on Primary (or at least unique) Keys in the table to which they refer.

What you're describing is a valid database, it's just not really Relational, since you can't define relations.

If you want to reverse-engineer a Key, there are mechanisms on virtually every DBMS for artificially creating keys.  On Oracle, you use sequences in combination with triggers; on SQL Server, you use the identity column.  On DB2, there's also an identity. On mySQL, use "AUTO_INCREMENT".  On Interbase, use Generators.

Does that help?

If you want to be pedantic - the definition of a database (or at least one) includes that every row is unique - to guarentee this you must have a unique constraint on every table.
Or you could say that behind the scenes the database engine itself will hold row identifiers (physical location if nothing else).

Doesn't really matter - if you need a unique constraint then add one. Sometimes (e.g. for import tables) you don't want any constraints as it could cause problems with the import and you check the data and move it to production tables later.

In general - every table that doesn't just hold transient data would have a unique constraint - otherwise there will be no way of distinguishing dupicate rows (i.e. processing them individually).
Fej

Author

Commented:
I should add that the 'keyless' table has another table which has a 'one-to-many' relationship with it.  Does that keep it 'relational'?  And referential blah blah is enforced.

Thanks,

Fej
If this is one to many then you should put a unique index on the one field or have some means of preventing duplicates.
Fej

Author

Commented:
Thanks for the help both of you.  To bad I can't split up the points.  So I gave it to the first answer since both were good enough.

Cheers,

Fej