3

Currently our DBA's design our databases without defining foreign keys, this is to let the dev team create unit tests for CRUD targeting each table individually.

We want to let the database team start using foreign keys in the actual DB, but we would also like to keep our ability to test each operation without having to create the parent rows.

Is there a way to disable foreign key constraints without having alter table permissions?

How do you handle this situation where you are?

BoxOfNotGoodery
  • 1,001
  • 1
  • 7
  • 24
  • We dont disable FK's, it prevents us from doing something stupid, or having to do with corrupt data. – Nix Apr 18 '11 at 16:33
  • Are you looking for something like http://stackoverflow.com/questions/159038/can-foreign-key-constraints-be-temporarily-disabled-using-t-sql ? – a1ex07 Apr 18 '11 at 16:38

2 Answers2

1

We require that every test sets up the environment it expects. This means that, if we must hit a DB, each test sets up it's environment first. We do use some helper methods to fill in common parts of the database, but we don't mess with the schema itself, since that is part of what is being tested in many cases.

We also make this a bit easier by using in-memory databases for unit testing so each test is even more independent.

cdeszaq
  • 30,869
  • 25
  • 117
  • 173
0
   > ... we would also like to keep our ability to test each operation without 
   > having to create the parent rows.
   > Is there a way to disable foreign key constraints without having alter 
   > table permissions?

No, You cannot disable foreign key constraints but maybe you can have Dummy-Parent-Objects in the database with id "-1" that are used in you tests. The child-Items-parents then can referece the "-1" dummy items

k3b
  • 14,517
  • 7
  • 53
  • 85