Saturday, November 20, 2010

Do we need database constraints?

I noticed that the DB2 system belong to one project I am working on does not have any constraints set up. One of my colleagues thought it is too bad that the database does not have constraints like foreign key etc. Do we need database constraints in our system excepts the primary key, and index constraints? Well, I think there is different answers for different people who plays different roles in a software development life cycle.

According to my understanding, the constraints in relational database is constraints set up by Database Designer for Application Developer. Database designer set up the foreign key constraints, null constraints, check constraints etc. Then, developer must follow the design to avoid break constraints.

Properly setting up constraints will be helpful if developer want to use some OR mapping tools to automatically generating database abstract layer. Or, it is useful if the application will depends on RDBMS to cascade delete records. However, I do agree that it could be harmless to remove constraints like foreign key in the product environment if the application takes care of natural key very well to avoid having orphan records. I think it could pay us some performance increasing and computer resource usage decreasing as return. Probably this is the reason for MySQL did not implement foreign key constraints when it is first invented and it still become widely used?

No comments:

Post a Comment