Application Development: Should I check for a primary-key on a table or assume it should be there?

When building an application and you are using a table that has a primary key, should you check to see if the table has a primary key or does not have duplicate IDs?

I ran into some code I'm maintaining that is checking to ensure no duplicate ids are in t开发者_运维百科he result set. But the id that is being checked is a primary key. So to me this check is not needed since you cannot have a primary keys with the same value.

But... should this be checked in case a DBA disabled the primary key on the table for any reason or assume the primary key should always be there?

Make sure that the query is actually returning data only from the table with the primary key. If this table is joined to another table in the query, and it isn't a one-to-one relationship, it could cause multiple rows to be returned which have the same ID in the primary table. In this case, the code checking for duplicates may actually be doing something valuable.

As long as this isn't the case, remove the code that checks for duplicates. It's a waste of CPU cycles and memory to verify that the database is doing its job.

I always leave it to the DB to manage this rule as it's best at doing that. But I have been bitten when people have dropped the primary key for various reasons - but it's always best to tackle that separately as it is usually an indication of another issue (such as a lack of training or care)

I think it would be a bad idea to have to confirm that the schema is correct in application code. That would be an ugly mixing of concerns. In fact, the application shouldn't care about the schema at all- it should be dependent on an abstracted data model.

Validation is another issue. You should check proactively for duplicates on primary and unique-keyed inserts rather than relying a database exception to indicate a duplicate.

I believe it should be checked - but not by the application. You probably don't run a virus check, test if there's enough space left in the DB, get hard disk health status, ... from your application, either.

Even if you did check for PKs from your application - how do you know, that this doesn't change during runtime? The existence of PKs should be ensured by the database deployment process, and permissions be restrictive enough, that this can't be changed (too easily) outside of that process.

If it is a primary key the constraint is enforced by the sql server and you don't need to verify it. So normally you cannot insert records with duplicate primary keys. This being said you can temporary deactivate this constraint and perform the insertion but in normal circumstances this cannot happen.

I wouldn't check it, this is a pretty basic RDBMS principle. I don't think it's unreasonable for your code to give strange answers if someone violates it.

In my humble opinion, checking for duplicates on a primary key is redundant. Dumb, too, but that's impolite, so let that go.

No DBA should be disabling the primary key.

It seems to me like you've already answered your question. If you have full control of your database structure, checking that the PK works is pointless work.

If it's likely that someone is going to go around in your db breaking things, then it's relevant to check that your db structure is intact.

If the DBA has dropped the primary key on the table and the product team is not aware of it to make appropriate code chages, then this is not a shortcoming of the code. I am sure that no DBA would possibly do that. So, to check if the table has a PK or not is absolutely redundant.

It usually makes sense to check the schema version at install time rather than runtime. One way to do that is to store a schema version number in the database and at install time check it is the one the app is expecting. Schema upgrade is potentially part of the installation anyway.

For a production app it is also usual to implement a change management process so that any changes (whether app or database) are regression tested before being released.