Hi, My colleague mentioned that our client DBA proposed the removal of all foreign key constraints in our project Oracle DB schema. Initially I did not agree with the decision. I am a developer not a DBA. So later realized that there could be some reasons behind the decision. So I am trying get the pros and cons of this decision. Proj ...
So, I just came across the fact that rails does not support referential integrity regarding foreign keys and was fairly surprised. So, what's the best way to manage this? Is there a "rails" way for dealing with referential integrity? Ideally the app should not have to deal with all this; the db should. I was looking at plugins like fore...
I am getting the error: Cannot add or update a child row: a foreign key constraint fails (mydb/requests, CONSTRAINT requests_ibfk_5 FOREIGN KEY (fixture_id) REFERENCES fixtures (fix_id) ON UPDATE CASCADE ON DELETE CASCADE) I have the following table structure: CREATE TABLE IF NOT EXISTS `requests` ( `request_id` int(11) unsigned NOT...
Frameworks like Rails have encouraged moving a lot of the logic, even stuff like constraints and foreign keys, off the database - in my opinion. for the better, as it's more manageable and easy to change. Even so, some operations are easier faster, or just plain only possible in SQL. The recent explosion in popularity of noSQL databa...
Applications have bugs or get bugs when updated, some hidden that they get detected months or years later, producing orphaned records, keys pointing nowhere etc. even with proper test suites. Allthough Rails doesn't enforce referential integrity on the database level - and for some good reasons discussed elsewhere it will stay like that...
I'm wanting to consolidate numerous MySQL databases, all with the same schema definition, into a single database. If I have a dump file from each database, how will I import them all into the same database without their primary keys and foreign keys clashing? Is there a fairly simple way to do this, or will I need to write some custom c...
Maybe I'm not seeing the forest for the trees, but here it goes: I'm "designing" an XML document and have so far come up with something like the following: <element key="root"> <data>...</data> <elements> <element key="foo"> <data>...</data> </element> <element key="bar"> <data>.....
Maybe i'm going about this wrong but my working on a database design for one of my projects. I have an entity with a classification column which groups up entities into convenient categories for the user. These classifications are predefined and unchangeable by the user (at least thats the current design). I'm trying to decide if I sh...
Hi All, In my process, I do something like: SET FOREIGN_KEY_CHECKS = 0; LOAD DATA INFILE '/path/to/mytable.txt' INTO TABLE mytable; SET FOREIGN_KEY_CHECKS = 1; Now, I need to check that the data after this import is not breaking the referential integrity. I would like to do something like check database all foreign_keys; Is a simi...
I need to figure out some clever MySQL snippet that will allow me to easily see two tables, the ids in the table if they exist or NULL or empty if they don't exist. I have a users table and a legacy table and outside of manual comparison I can't figure out how to make them appear in a table together so I can compare. What I would love ...
Should items like Foreign Keys, Constraints, Default Values, etc be handled by the Database management system (in this case, MS Sql 2005) or the Application? I have heard opinions from both sides and I'm honestly not sure which way to go. I originally was going to build it into the database, however I have discovered this is not always ...
Hi, If you had to build a relational data warehouse of biblical proportions using SQL Server 2008, would you use foreign keys to enforce data integrity, or would you use some other means? I like foreign keys because you only need to get them right once, and they are always there to protect integrity. I was thinking of going the disable...
In the following table, is there a way to ensure PreviousID always references an ID in a row with a matching ParentID, or, if ParentID is null, it is also null in the referenced row? CREATE TABLE MyTable ( ID int not null identity(1,1) primary key, ParentID int null foreign key references MyTable (ID), PreviousID int null foreign ...