Primary key on a column.

Question

Hi All,

I have a table with 2 columns and 2 records. Column1 will never change but the Column2 might have chances that it will change but table will have only 2 records.

Column1

Missing

Invalid

Column2

\\sqlserver\destination\missing

\\sqlserver\destination\invalid

I am little confused here about the primary key that i wanna put on this table as there is no Id column. so which column i should have primary key? or do i have to add one more column with identity and put primary key on that?

Thanks

Answer 1

Yes. The PK column can never contain duplicates. It doesn't have to be an integer however, but it needs to be a unique non-null column.

Answer 2

IMHO, you should "always" put an id key that then becomes the primary key.

"always" is in quotes because it's possible to argue for cases when it's not required, but generally this is the way to go, and certainly it's safe to say it is the default approach, and any deviation from it should be investigated rigorously for its benefits.

There is an argument for "natural" keys; that is to say you put the primay key on the field that is guaranteed to be unique and never change. But, in my experience, almost everything does end up changing, so it's safer to go with an inbuilt default auto-incrementing ID.

Answer 3

You don't need a primary key on a table that has only two records. Primary key is meant for increasing query speed; with 2 records you will hardly see any difference.

Edit: In response to the comments, I'd like to point out that no mainstream DB vendor enforces the use of primary keys. There is a reason for them being optional: unless the primary key is required by functionality, it doesn't belong there; YAGNI.

Answer 4

On a table that small the creation of an index would actually slow things down. Indexes are stored as a binary tree so a lookup on this small of a table, unless it's clustered index will cause more reads than the table scan. A clustered index would actually "be" the table, but again on such a small table the cost/benefit is "moot".

I like always having unique rows, but in this case just leaving the table unindexed (know as a "heap") might actually be the most efficient. I'd wouldn't throw an index on the table unless you need to enforce constraints. Indexing it for query performance isn't going to do anything for you with this small of a table.

If you are required to put a PK on the table for some other reason, then I would say put it on the first column as it is the shortest, less likely to chance and it looks like this table is basically just used as a look-up anyways...

Answer 5

The criteria for choosing candidate and primary keys are:

uniqueness, irreducibility, stability, simplicity and familiarity

From what you have written, Column1 is definitely a candidate key. It has all 5 of the above criteria.

Column2 might be a candidate key if the two values in the table must always be unique. However, it is not stable so Column1 is a better key to choose for foreign key references to the table (primary key).

You could create a 3rd numeric column. Since you constrain the table to 2 rows, it makes little difference whether the new column has a system-maintained sequence (identity attribute).

Column1 has familiarity and the new column would not. At a logical level of discourse, both Column1 and this new column are equally simple. Physically, a 7 character string is at least as large as a 64-bit number so a 32-bit number occupies less space.

However, if you choose to add a new column due to physical size, I would consider a char(1) column with 'M' for missing or 'I' for invalid, which would still have all 5 criteria while occupying less physical space in referencing tables.