foreign-keys

Update: How to implement Foreign Key concept in MySQL when we do not have referential integrity features in MySQL ?

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

mysql ( innodb )foreign key constraints problems

Hello I am running into a couple of issues while trying to generate foreign keys for my tables in MySql(Innodb). Can you please help me with them ? Referenced tables : *create table entity { PID INT(20) auto inc not null, ENTITYID INT(20) not null, details VARCHAR(100) not null, primary key(PID,ENTITYID) } create table user { ...

MySQL InnoDB CASCADE?

Hi, I am starting to experiment with using InnoDB in web applications. I've setup some tables with a foreign key, but they are not behaving as expected. Here are my table CREATE statements: CREATE TABLE sections ( section_id INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(section_id), title VARCHAR(30), created_at int(10) NOT NULL, upda...

Doctrine PHP Question

I was wondering if you can specify the order in which table definitions and data fixtures are loaded using the CLI. My problem is that I have two tables, each having a foreign key constraint pointing to the other, so one of the constraints needs to be added after a record has been added. Or maybe there's a better way of doing this...I'm ...

Problem with foreign key constraint

I am getting error #1005 - Can't create table (errno: 150). I have been through the checklist of: both tables are InnoDB the columns are the same type (INT) attributes are the same (UNSIGNED NOT NULL) the collation is the same I have tried with indexes on the foreign keys, it still doesn't work (and they shouldn't be needed for MySQL ...

Bidirectional foreign key constraint

I'm thinking of designing a database schema similar to the following: Person ( PersonID int primary key, PrimaryAddressID int not null, ... ) Address ( AddressID int primary key, PersonID int not null, ... ) Person.PrimaryAddressID and Address.PersonID would be foreign keys to the corresponding tables. The obvious proble...

Django ORM - select_related and order_by with foreign keys

I have a simple music schema: Artist, Release, Track, and Song. The first 3 are all logical constructs while the fourth (Song) is a specific instance of an (Artist, Release, Track) as an mp3, wav, ogg, whatever. I am having trouble generating an ordered list of the Songs in the database. The catch is that both Track and Release have a...

Entity Framework Updating with Related Entity

I'm using the EF to try to update an entity with ASP.NET. I'm creating an entity, setting it's properties then passing it back to the EF on a separate layer with the ID so the change can be applied. I'm doing this because I only store the ID of the entity when it's been bound to the UI controls. Everything works for standard properties,...

Doctrine migration foreign keys

In PHP Doctrine, is it possible to create one migration class that creates a table and creates a foreign key on that table? For some reason, I can't get the foreign key to work ... class Migration_001 extends Doctrine_Migration_Base { public function up() { $this->createTable('table_name', array(...)) $this->createFo...

What are Sql Browser with foreign key / references support?

I now want to create foreign keys for all of my mew DBs. So far i have been using sqlite and MyIasm DBs so i havent used a DB that supported it. I am thinking i should install and use a MySql server with InnoDb (connected via C#). My question is what can i use to actually see the FKs relations? I would like to see it by using a sqlite d...

SQL Server Foreign Key constraint benefits

We're designing a database in which I need to consider some FK(foreign key) constraints. But it is not limited to formal structuring and normalization. We go for it only if it provides any performance or scalability benefits. I've been going thru some interesting articles and googling for practical benefits. Here are some links: http:/...

What is the problem with foreign key cascade multiple paths and cycles?

In MSSQL 2005 I just struck the infamous error message: Introducing FOREIGN KEY constraint XXX on table YYY may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints. Now, StackOverflow has several topics about this error message, so I've already got the...

How worth is it to assign an FK Index?

I'm a normal "would be DBA" level developer. I've been handling some databases with a few million records. A lot goes around importing data between database and its clone and then using that clone in the web-app environment. Well, I've known that keeping PK indexes automatically and so it helps speedup the data access. Now, from this di...

hibernate foreign key

i have entity A that has foreign key to entity B, does entity B need to have foreign key back to entity A? if yes, how? coz entity B, id already used for primary key entity A --> id, entity_a_name, foreign_key_entity_B entity B ---> id, entity_b_name ...

Hibernate foreign key mapping?

I have an entity A that has a foreign key of entity B: entity A --> id, entity_a_name, foreign_key_entity_B When I call return session.createCriteria(EntityA.class).list(); I get the property of entityB inside entity A as well. How do I make it lazy load so it will not load enityB if not needed? ...

delete entity that map with another entity

entity A --> id, entity_a_name, foreign_key_entity_B entity B ---> id, entity_b_name when i delete entity B record, it suppose to auto delete entity A that has foreign_key_entity_B ? Error: ORA-02292: integrity constraint (schema.FKA630D2FB5F0F1180) violated - child record found ...

Dynamic Foreign Keys - How To Implement?

I have 4 tables (appointed, class, elected, status) that I want to cross reference into a single table's (members) column. The values of the of 4 tables are time sensitive based off a history table (members_history). The desired result is that the query should output all members and the current appointed position or current elected posit...

hibernate how to create createalias left join?

@Entity @Table(name = "BOOKTEST") @NamedQueries({@NamedQuery(name = "Booktest.findAll", query = "SELECT b FROM Booktest b"), @NamedQuery(name = "Booktest.findById", query = "SELECT b FROM Booktest b WHERE b.id = :id"), @NamedQuery(name = "Booktest.findByBookName", query = "SELECT b FROM Booktest b WHERE b.bookName = :bookName")}) public ...

Speeding up SQL query when sorting on foreign keys

This is more of a generic SQL problem but I'm using Firebird 2.5 if anyone knows of a Firebird/Interbase specific optimization. First, the following is a simplified example schema to illustrate the issue I'm trying to solve: CREATE TABLE users ( id INTEGER PRIMARY KEY, name VARCHAR(16) ); CREATE TABLE data_set ( id INTEGER PR...

How do I know if record from an SQL database is being used elsewhere?

Is there a way to know that a record is being used by another record in the database? Using deleting as an example: When I create an SQL statement trying to delete a group in dbo.group I get this error: The DELETE statement conflicted with the REFERENCE constraint "FK_MyTable". The conflict occurred in database "MyDB", table "dbo.U...