foreign-keys

Google AppEngine JDO Persistence FK Arrays

I'm hoping someone's seen this. I've found no clues on Google. I'm using Google AppEngine with JDO to persist my objects. I have two objects, Parent and Child. Each Parent has n Child objects. I initially stored the Child objects in an ArrayList data member in the Parent class. I got the exception "java.lang.UnsupportedOperationExcep...

Creating a foreign key in MySQL produces error:

I'm trying to create a foreign key on a table in MySQL and I'm getting a strange error that there seems to be little info about in any of my searches. I'm creating the key with this (emitted from mysql workbench 5.2): ALTER TABLE `db`.`appointment` ADD CONSTRAINT `FK_appointment_CancellationID` FOREIGN KEY (`CancellationID` ) REFE...

Postgresql constraint

I cannot seem to get this right, I am trying to modify a field to be a foreign key, with cascading delete... what am i doing wrong? ALTER TABLE my_table ADD CONSTRAINT $4 FOREIGN KEY my_field REFERENCES my_foreign_table ON DELETE CASCADE; ...

List of Django model instance foreign keys losing consistency during state changes.

I have model, Match, with two foreign keys: class Match(model.Model): winner = models.ForeignKey(Player) loser = models.ForeignKey(Player) When I loop over Match I find that each model instance uses a unique object for the foreign key. This ends up biting me because it introduces inconsistency, here is an example: >>> def print...

Drop all foreign keys in a table

I had this script which worked in sql server 2005 -- t-sql scriptlet to drop all constraints on a table DECLARE @database nvarchar(50) DECLARE @table nvarchar(50) set @database = 'dotnetnuke' set @table = 'tabs' DECLARE @sql nvarchar(255) WHILE EXISTS(select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where constraint_catalog = @datab...

Get all referencing columns to a referenced column in linq

Say you have a Table named Sales with a SalesOrderId. Then you have two tables SalesOrderHeader and SalesReport that reference SalesOrderId. Starting from the Sales Tables and SalesOrderId, is there any way to figure out those two table reference it using linq? I want to add a Debug.Assert to my code so that I can update that piece of co...

Postgresql: Implicit lock acquisition from foreign-key constraint evaluation

So, I'm being confused about foreign key constraint handling in Postgresql. (version 8.4.4, for what it's worth). We've got a couple of tables, mildly anonymized below: device: (id, blah, blah, blah, blah, blah x 50)… primary key on id whooooole bunch of other junk device_foo: (id, device_id, left, right) Foreign key (...

Foreign Keys with SchemaExport in Fluent NHibernate using SQLite

I am attempting to create a simple database application which keeps track of loans of various types of equipment using Fluent NHibernate and SQLite. However, when I try to generate the database structure with SchemaExport for use in unit testing, foreign keys for one-to-many relationships aren't created. Here is my Equipment entity: pu...

The best way to structure this database?

At the moment I'm doing this: gems(id, name, colour, level, effects, source) id is the primary key and is not auto-increment. A typical row of data would look like this: id => 40153 name => Veiled Ametrine colour => Orange level => 80 effects => +12 sp, +10 hit source => Ametrine (Some of you gamers might s...

django: How to make one form from multiple models containing foreignkeys

I am trying to make a form on one page that uses multiple models. The models reference each other. I am having trouble getting the form to validate because I cant figure out how to get the id of two of the models used in the form into the form to validate it. I used a hidden key in the template but I cant figure out how to make it work i...

MySQL case sensitive foreign key for textual data

I have ISO 639-2 language codes (eng, fre, hin, etc.) in english as primary key in my master table. This column is foreign key in may other tables. Now my issue is even though my master have only lower case values, due to human error some values were added in other tables with language id in mixed cases. Even though there was foreign key...

Hibernate second level cache and ON DELETE CASCADE in database schema

Our Java application has about 100 classes mapped to a database (SQL Server or MySQL). We are using Hibernate as our ORM (with XML mapping files). We specify FOREIGN KEY constraints in our database schema. Most of our FOREIGN KEY constraints also specify ON DELETE CASCADE. We've recently started enabling Hibernate 2nd level caching (fo...

How to find all foreign keys?

I'd like to find all referencing tables in my db that have a foreign key that points to a specific referenced table. Is there a query that I can run to do this? Not sure if the question is confusing. Let me know if it is and I can try to explain it in more detail. ...

using references in MYSQL

i got an answer to another question here: http://stackoverflow.com/questions/3094495/db-schema-for-chats/3094915#3094915 it's a great answer, but i am not understanding the bit about references. i can do sql statements but i have never used references. what are they used for? how are they used? give an example please ...

foreign keys in activerecord migrations vs. schema.rb

I'm running activerecord 3.0.0beta. I know you can create columns with foreign keys like create_table "my_things" do |t| t.reference "other_thing_id" end but I forgot and just made it a plain integer. Now, I've added a migration like execute("alter table my_things add constraint fk_other_thing foreign key (other_thing_id) reference...

MongoDB DBRef ON DELETE CASCADE

Is there a way in MongoDB to have a foreign key with a 'ON DELETE CASCADE' functionality? I know you can use DBRef as a sort of foreign key but when the item in a collection where the reference points to is removed, the reference returns null. But i want that the item where the reference belongs to gets removed. How do i do this? Or do...

MySQL: Insert if foreign key exists

I have an excel sheet with around 2.000 rows that i want to insert into my database. The problem is that the table i want to insert the 2.000 rows into has a field that references to a foreign key in another table. Unfortunately a lot of queries fail, since the given foreign key does NOT exist. I know that I can ignore foreign key chec...

Filter ForeignKey by Boolean value in django

I have these models: class Client(models.Model): is_provider = models.BooleanField() class Billing(models.Model): client = models.ForeignKey(Client) I want to limit the choices of ForeignKey to show only the clients with is_provider=True. Is there something like: limit_choices_to = {'is_provider': True} Or anything I can u...

Removing parent and all children

Hi, I have a problem removing the parent entity from the database. The code looks like this: public class Parent implements Serializable { @Id @GeneratedValue(strategy = GenerationType.TABLE) private Long id; @Cascade(org.hibernate.annotations.CascadeType.DELETE_ORPHAN) @OneToMany(cascade = CascadeType.ALL, fetch = FetchTy...

How can you check for foreign key references for a list of records before attempting to delete any of these records in MySQL?

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