foreign-keys

Relational Database (H2, Java): How do I constrain a foreign key to NOT match another foreign key in the same table?

Simple question. Just wondering if this can be done without me having to enforce this constraint manually in my Java code. These two foreign keys (together in the same table) both refer out to another table, but for each row, they must not be allowed to point to the same foreign item. link text ...

MS-Access: What could cause one form with a join query to load right and another not?

Late breaking news! If I manually create the new record with SQL and then open the form in edit mode using the code below, it works. I would still like to know what the problem was before, but at least I can get on with my application now. Form1 Form1 is bound to Table1. Table1 has an ID field. Form2 Form2 is bound to Table2 joine...

How do I delete a foreign key in SQLAlchemy?

I'm using SQLAlchemy Migrate to keep track of database changes and I'm running into an issue with removing a foreign key. I have two tables, t_new is a new table, and t_exists is an existing table. I need to add t_new, then add a foreign key to t_exists. Then I need to be able to reverse the operation (which is where I'm having troubl...

One to One relationship in MySQL

I'm trying to make a one to one relationship in a MySQL DB. I'm using the InnoDB engine and the basic table looks like this: CREATE TABLE `foo` ( `fooID` INT(11) NOT NULL PRIMARY KEY AUTO_INCREMENT, `name` TEXT NOT NULL ) CREATE TABLE `bar` ( `barName` VARCHAR(100) NOT NULL, `fooID` INT(11) NOT NULL PRIMARY KEY, CONSTRAINT `c...

Does clustered index on foreign key column increase join performance vs non-clustered ?

In many places it's recommended that clustered indexes are better utilized when used to select range of rows using BETWEEN statement. When I select joining by foreign key field in such a way that this clustered index is used, I guess, that clusterization should help too because range of rows is being selected even though they all have sa...

Can you automatically create a mysqldump file that doesn't enforce foreign key constraints?

When I run a mysqldump command on my database and then try to import it, it fails as it attempts to create the tables alphabetically, even though they may have a foreign key that references a table later in the file. There doesn't appear to be anything in the documentation and I've found answers like this that say to update the file aft...

MySQL forgot about automatically creating an index for a foreign key?

After running the following SQL statements, you will see that, MySQL has automatically created the non-unique index question_tag_tag_id_tag_id on the tag_id column for me after the first ALTER TABLE statement has run. But after the second ALTER TABLE statement has run, I think MySQL should also automatically create another non-unique in...

Create automatically table that is composed by its primary key and 2 other foreign keys

I have table A with a primary id, a table B with a primary key id also and another table C with a primary key id and rows Aid and Bid where Aid and Bid are foreign keys of C and the primary keys of A and B respectively. One way of populating these DB would be populating table A, table B and table C separately. I would like to know if...

MySQL Frontend that displays foreign rows nested within a table's rows?

I once came across a MySQL Frontend app that displayed foreign linked rows within the parent row, if for instance the Client table has a foreign key to the Suburb table: Do you know of any Frontend that can do this? ...

Asp.Net, Dynamic Data Website, Foreign key One to One relationship, DropdownList, how to customise?

Hi, I'm using Asp.Net's Dynamic Data Website project type. I've created a db that has one to onerelationship between two tables specified using a foreign key, (this could be one to many but I've used the linq designer to change it to a one to one relationship). i.e. I have a table Invoice and table CreditNote that takes an Invoice_Id as...

Multiple foreign keys in one table to 1 other table in mysql

Hey guys, I got 2 tables in my database: user and call. User exists of 3 fields: id, name, number and call : id, 'source', 'destination', 'referred', date. I need to monitor calls in my app. The 3 ' ' fields above are actually userid numbers. now i'm wondering, can i make those 3 field foreign key elements of the id-field in table us...

Foreign keys in django admin list display

If a django model contains a foreign key field, and if that field is shown in list mode, then it shows up as text, instead of displaying a link to the foreign object. Is it possible to automatically display all foreign keys as links instead of flat text? (of course it is possible to do that on a field by field basis, but is there a gen...

Mysql show create constraint?

Hi, Is there a easy way to query a table for its constraints(foreignkeys specificaly) like show create table, but for the constraints only? thanks, pvgoddijn ...

Cascading Deletes in SQL Sever 2008 not working.

I have the following table setup. Bag | +-> BagID (Guid) +-> BagNumber (Int) BagCommentRelation | +-> BagID (Int) +-> CommentID (Guid) BagComment | +-> CommentID (Guid) +-> Text (varchar(200)) BagCommentRelation has Foreign Keys to Bag and BagComment. So, I turned on cascading deletes for both those Foreign Keys, ...

Phpmyadmin foreign key drop-down field help

Hey guys i'm using phpmyadmin (php & mysql) and i'm having alot of trouble linking the tables using foreign keys. I'm getting negative values for the field countyId (which is the foriegn key). However it is linking to my other table fine and it's cascading fine. So when I go to add data there will be a drop box for the CountyId and the...

DB Designer creates compound primary key

When adding relationships to a database model in DB Designer 4, a composite primary key is being created every time. So every foreign key I add, I get an extra key added to a composite primary key. I think I must have changed a setting as I don't remember it doing this in the past. Does anyone know how to turn off this feature as I pr...

django class with an array of "parent" foreignkeys issue

Let's say I have a class called Fruit with child classes of the different kinds of Fruit with their own specific attributes, and I want to collect them in a FruitBasket: class Fruit(models.Model):     type = models.CharField(max_length=120,default='banana',choices=FRUIT_TYPES)     ... class Banana(Fruit):     """banana (fruit type)""" ...

Many-to-many relations in RDBMS databases

What is the best way of handling many-to-many relations in a RDBMS database like mySQL? Have tried using a pivot table to keep track of the relationships, but it leads to either one of the following: Normalization gets left behind Columns that is empty or null What approach have you taken in order to support many-to-many relationsh...

Why is django admin not accepting Nullable foreign keys?

Here is a simplified version of one of my models: class ImportRule(models.Model): feed = models.ForeignKey(Feed) name = models.CharField(max_length=255) feed_provider_category = models.ForeignKey(FeedProviderCategory, null=True) target_subcategories = models.ManyToManyField(Subcategory) This class manages a rule for importing ...

Partitioning mySQL tables that has foreign keys?

Hi! What would be an appropriate way to do this, since mySQL obviously doesnt enjoy this. To leave either partitioning or the foreign keys out from the database design would not seem like a good idea to me. I'll guess that there is a workaround for this? Update 03/24: http://opendba.blogspot.com/2008/10/mysql-partitioned-tables-with-tr...