foreign-keys

referential integrity in rails

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

CakePHP associations messing up

Hi, I am new to cakephp and have been working through the Apress book "Beginning CakePHP from Novice to Professional" but have ran into a problem after the baking chapter. (If you've seen my other recent question, you'll know that I had to skip that chapter because I cant get the bake console working on my win7 machine). The problem i...

Can you upgrade sqlite3 version on your android?

Hi I'm writing an app which uses the Foreign Key Constraints which support was only just brought in for on version 2.2, and my phone is 2.1 Is there any way to upgrade the version of sqlite3 on the htc desire or any other android phone when your application installs? ...

How do I setup a Doctrine relation for a foreign key that could point to different tables, depending on context?

I am creating an application, built with PHP, MySQL and Doctrine v1.2, that deals with ordered groupings of items, which can contain different types of items. For example, the group My Last Vacation can have images, video and textual notes. These items would be sortable so that they appear in the order that the end user specifies. Eac...

symfony : Problem with the method 'embedRelation'

Hello, I have two classes with a relation one-to-many. And I want to make a nested form to enter an object and some of others which are linked to it. But when I save the form, the key wich references my main class isn't update with the key of main class. However the other keys are created. My schema : Enfant: connection: doctrine ...

root in tree like structure with auto-referencing foreign keys

Hi. My application uses some kind of "virtual folders" so I need to store the tree structure in the database. The table holding the data is quite simple, it has 3 columns: id_folder (int, primary key) id_parent (int, references id_folder) folder_name (string) My question is: which is the best way to implement the root? Making id_p...

Postgresql MATCH PARTIAL work around?

I'm trying to work around Postgresql 8.4's lack of MATCH PARTIAL. I have the following schema: [vehicles] lot_id | vin | source | year | make | model ... primary key ( lot_id, vin, source ) [pictures] picture_id | lot_id | vin | url | sha1 ... primary key ( picture_id ) Now, what I want is a compound FOREIGN KEY that REFERENCES t...

MySQL foreign keys' referenced table name always forced to lowercase

First, create these two tables: CREATE TABLE IF NOT EXISTS TAB_COMPANY ( ID INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, NAME VARCHAR(100) NOT NULL, PRIMARY KEY(ID), UNIQUE KEY(NAME) ) ENGINE=INNODB DEFAULT CHARSET=UTF8; CREATE TABLE IF NOT EXISTS TAB_DEPARTMENT ( ID INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, NAME VARCHAR(100) ...

MYSQL alter table - add INDEX + FOREIGN KEY give error 1005

what is wrong with this alter table command : ALTER TABLE `lp` ADD COLUMN `RuleId` INT(10) NOT NULL DEFAULT -111 AFTER `Weight` , , ADD CONSTRAINT `fk_1` FOREIGN KEY (`RuleId` ) REFERENCES `Rules` (`RuleId` ) ON DELETE NO ACTION ON UPDATE NO ACTION, ADD INDEX `fk_1` (`RuleId` ASC) ; if i drop the line ADD CONSTRAINT `fk_1` FORE...

Create foreign key without checking existing data

This is a 2 part question. Question 1: I am trying to create a foreign key on a table where I need to turn off the "Check Existing Data on Creation or Re-Enabling". I know theres an option visually but I'm looking for a way to do it programmatically. Is there anyway to do this? Question 2: I have a code table and two tables A and B tha...

JPA: Weird error when I try to persist an object

I got a OneToMany relation between User and Group Group.java @Entity public class Group { @Id @GeneratedValue(strategy = GenerationType.AUTO) private Long id; private String groupid; @ManyToOne @JoinColumn(name="USER_FK") private User user; ... } User.java @Entity public class User { @Id @GeneratedValue(strategy = Generat...

unconventional foreign keys in rails

I have a kind of legacy db structure with rails. It has a structure like: apples id: number: oranges apple_id: (links to apples.number) pears apple_id: (links to apples.id) Models like: Apple has_many :oranges, :foreign_key => ?, :primary_key => ? has_many :pears Orange belongs_to :apple, :foreign_key =>...

ManyToMany relationship using JPA with Hibernate provider is not creating primary keys

I've created two JPA entities (Client, InstrumentTraded) using Hibernate as a provider that have a ManyToMany relationship. After letting Hibernate generate the tables for MySQL it appears that the ManyToMany relationship table does not contain primary keys for the two foreign keys. This allows duplicate records in the many-to-many tab...

Using MySQL Index for foreign Keys with Hibernate

Hello everyone, Our current database (MySQL) already has Indexes for its foreign keys since these use InnoDB, which (afaik) automatically generates IndexTables for foreign keys. Hibernate wasnt used to create these tables. So my question is: Is MySQL using Indexes for their foreign keys automatically, when I use Hibernate to fetch Data ...

MySQL DROP all tables, ignoring foreign keys

Is there a nice easy way to drop all tables from a MySQL database, ignoring any foreign key constraints that may be in there? ...

Should you design created_by and last_update_by in User table?

Sometime back while designing something that included user management, I was required to have created_by and last_updated_by columns in the User table. To me it seemed a good idea to have a 1:1 relationship on User itself as it would serve as an additional check. The obvious issue is creating the first user who will have to create himse...

MySQL + PHP: fetching data using foreign keys

I have 2 tables (Users, Wall). The UserID in the Wall table is a foreign key. How would I go about fetching the users details using this? (I want to fetch the users Forename and Surname who posted the message.) Users Table: Wall Table: EDIT: I cannot figure out how to show the data. <?php include('config.php'); ?> <!DOCTYPE html PU...

MySQL + PHP: General Help Needed for Echoing Data from Foreign Key

I have 2 tables (Users, Wall). The UserID in the Wall table is a foreign key. How would I go about fetching the users details using this? (I want to fetch the users Forename and Surname who posted the message.) Users Table: Wall Table: EDIT: I cannot figure out how to show the data. <?php include('config.php'); ?> <!DOCTYPE html PU...

MySQL with Soft-Deletion, Unique Key and Foreign Key Constraints

Say I have two tables, user and comment. They have table definitions that look like this: CREATE TABLE `user` ( `id` INTEGER NOT NULL AUTO_INCREMENT, `username` VARCHAR(255) NOT NULL, `deleted` TINYINT(1) NOT NULL DEFAULT 0, PRIMARY KEY (`id`), UNIQUE KEY (`username`) ) ENGINE=InnoDB; CREATE TABLE `comment` ( `id` ...

Symfony schema works on my local server, but gives foreign_key constraint issues moving to Dreamhost

I'm using symfony 1.4 and doctrine. I've spent the last couple days playing with my schema, and I've gotten it to load up / build / behave properly but only on my local machine. When I copy the files to an account on Dreamhost, change the configuration to allow a connection to that database (and nothing else) I get the following error tr...