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` (
  `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`)

-- 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`

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.

  • Foreign key constraints required to enforce master-detail relationships, unique constraints - uniqueness. If your application uses any ORM, it will very likely generate CREATE CONSTRAINT statements when you define tables with relationships.
    – a1ex07
    Commented Sep 10, 2018 at 21:32

1 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.

  • Thank you for the answer, but I still don't understand how the constraints got there in the first place if we didn't create them, see why I'm confused? If it wasn't part of our original design, who/what decided they were important?
    – Kodara
    Commented Sep 10, 2018 at 21:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.