I am using TransactionScope to add a object's data to one database. pseudo code: using (TransactionScope trx = new TransactionScope()) { SqlConnection con = DL.GetNewConn(); int newParentRecordID = InsertParentIntoTableA(object parent, con); foreach(obj child in parent.childrenObjects) { child.ParentID = newParentRe...
Assume that you are writing a simple app. The model is that a 'project' has a 'category'. The project's category can be edited by choosing from a drop down list that contains all possible categories. The user creates the following Categories: C1, C2, C3. The user creates the following Projects, Category associations: [P1, C1], [P2, C2]...
Hi, My question is regarding referential integrity concept in MySQL Database. Due to some kind of restrictions from our DBA we are not allowed to use referential integrity features of MySQL and so my question is "How can we implement Foreign Key concept in MySQL when we do not have referential integrity features in MySQL ?" Thanks. ...
Lets say I have a table with "Groups of Questions" GroupID | value --------+------ 42 | How often do you 9071 | Other question ... And, for each group of questions I have "questions" and "possible answers" Group | QuestionID | value ------+------------+------ 42 | 5 | ... brush your teeth? 42 | 89 | .....
At present we have very little referential integrity, as well as having a number of tables that self-join (and indeed would perhaps better be represented as separate tables or views that joined). The knowledge of how these tables relate to each other is implicit in the logic of the stored procedures rather than explicit in the schema. W...
In the past, when I implemented my own authentication mechanisms I would have a user table with relationships to other tables in my application's MySQL database. However, now that I'm considering using ActiveDirectoryMembershipProvider, I see no way to create similar relationships between AD users and those tables. What's the normal wa...
I am developing an archive module for an application using Dotnet and SQL Server as back end. From multiple approaches of archiving we've decided to build a custom application to archive the complete database up-to a chosen threshold to another mirrored database and then removing the archived items from source DB. This has to be done fro...
I am new to CouchDB and learning about it. I did not come across CouchDB support for referential integrity. Can we create a foreign key for a field in the CouchDB document? For e.g. Is it possible to ensure a vendor name used in a order document is available in the vendor database? Does CouchDB support referential integrity? And Is i...
Hi, I have two databases in a php / mysql application. One for user data and the other for content. I have recently had to make use of inno db tables in order to enforce some referential integrity. Obviously, the user tables will be required to participate in these relationships. Is there a way to keep the data separated whilst sti...
My application involves using submitting data (the "request") from a form into an SQL Server 2005 database, for later review and approval by a supervisor. Users should have permission to insert a new request, but not be able to modify the ones they have already submitted. With a single table, this is straightforward: grant them the INSE...
I understand the need to have referential integrity for limiting specific values on entry or possibly preventing them from removal upon a request of deletion. However, I am unclear as to a valid use case which would exclude this mechanism from always being used. I guess this would fall into several sub-questions: When is referential i...
Do you see database triggers / referential integrity rules being used in a way that changes actual data in the database (changing row w in table x causes a change in row y in table z)? If yes, How does this tie-in with the increasing popularity of in-memory caching (memcache and friends)? After all, these actions occur inside the databa...
I'm attempting to create a database model for movie classifications, where each movie could have a single classification from each of one of multiple rating systems (e.g. BBFC, MPAA). This is the current design, with all implied PKs and FKs: TABLE Movie ( MovieId INT -- PK ) TABLE ClassificationSystem ( ClassificationSystem...
Hello, I'm designing a few applications that will share 2 or 3 database tables and all of the other tables will be independent of each app. The shared databases contain mostly user information, and there might occur the case where other tables need to be shared, but that's my instinct speaking. I'm leaning over the one database for all...
Hi all, I am planning on doing a database deployment script for sql server 2005. Currently we have a tool that will run all of the tables, foreign keys, indexes and then data, each of which is located in a separate file with a certain extension (eg. tab, .kci, .fky) and the tool just runs *.tab, *.kci, *.fky into the db etc. Could I p...
I'd like to get the message number and severity level information from SQL Server upon execution of an erroneous query. For example, when a user attempts to delete a row being referenced by another record, and the cascade relationship is "no action", I'd like the application to be able to check for error message 547 ("The DELETE stateme...
My situation may be a bit abnormal, but I have foreign keys defined in my MySQL database, while enforcing referential integrity in the Zend_Db_Table classes. The tables use the InnoDB storage engine. When deleting a record, the Zend Framework will properly identify immediate children via the $_referenceMap in the table model and delet...
I have a table with 3 columns: ID, PARENT_ID, NAME PARENT_ID has a foreign key relationship with ID in the same table. This table is modeling a hierarchy. Sometimes the ID of a record will change. I want to be able to update a record's ID, then update the dependent records' PARENT_ID to point to the new ID. The problem is, when I ...
The following table definition: CREATE TABLE Customers( id INT NOT NULL PRIMARY KEY, name [varchar](50) ) CREATE TABLE Orders ( id INT NOT NULL PRIMARY KEY, customer INT FOREIGN KEY REFERENCES Customers(id) ON DELETE CASCADE ) CREATE TABLE OrderDetails ( id INT NOT NULL ...
Is there a way, when you have a list of records, to check if each of these records have foreign key references before you attempt to delete any of these records? As an example, if I have a list of borrowers and a list of books, you should not be able to delete a borrower from the system if he still has books on loan. (My actual system i...