Do I Need Constraints

Question

We created a database template that gets executed during the installation of a web application (that we also created).

After the application ran in a live scenario for a while, it inherited CONSTRAINTS without our intervention, meaning if we export the database, there are now constraints below some of the table creation blocks.

We want to give this application to someone else, and it requires the database template so they can install it (which we no longer have), and I would like to know if it is safe to remove these constraints (that we did not physically create), and give them a clean template, with only table creations?

Here is an example of one of them, there are roughly 30 tables and now roughly 10 mysterious constraints.

--
-- Table structure for table `blocked_period_rooms`
--

CREATE TABLE IF NOT EXISTS `blocked_period_rooms` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `blocked_period_id` int(11) NOT NULL,
  `room_type_id` int(11) NOT NULL,
  `quantity` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `blocked_period_id` (`blocked_period_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

--
-- Constraints for table `blocked_period_rooms`
--

ALTER TABLE `blocked_period_rooms`
  ADD CONSTRAINT `blocked_period_rooms_ibfk_1` FOREIGN KEY (`blocked_period_id`) REFERENCES `blocked_periods` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

Here is another example.

ALTER TABLE `guest_ip_addresses`
  ADD UNIQUE KEY `guest_id` (`guest_id`,`ipaddress`),
  ADD KEY `for_foreignkey_of_guest` (`guest_id`);

ALTER TABLE `guest_ip_addresses`
  ADD CONSTRAINT `foreignkey_guest` FOREIGN KEY (`guest_id`) REFERENCES `guests` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

I'm not a database expert and don't fully understand how/why we're ending up with code in the database like this, any help is VERY appreciated.

Answer 1

You definitely want to keep database constraints. They guard data integrity at the database level, not allowing for inconsistent data situations.

An example: your database and application are about students and lockers. So you have two tables: Students and Lockers, and the Locker table has a column linking/pointing/referencing/assigning a locker to a student. Say your application allows to submit a new student AND at the same time he can choose a locker. That means two database actions and an email to the student: 1. Insert a new student with a new ID. 2. Update the Locker table record linking assigning it to the new student.

Logically, we would first insert the new Student and then update the Locker record, but your programmer is not so experienced and codes it the other way round, not very logical but nobody sees the illogical code and hey it works perfectly while he demo's it.

Now in the real world, as Murphey dictates, when a real user is using the app, halfway the process the web server crashes (database timeout, deadlock, etc...). So the locker record has been updated pointing it to the new Student ID (and the email got sent out), but the Student record never got inserted. Oops, so you've ended up with a locker assigned to a non-existing student.

You want those database constraints to guard database integrity.