foreign-keys

Foreign keys - temporarily bypass?

Hi, I have just started to learn about the pros of foreign keys in database design (mySQL / innoDB) and I wonder if there's any way to temporarily bypass the foreign key when doing a specific delete query, to just delete in the parent table, and not from the linked child tables. Thanks ...

Three level database - foreign keys

I have a three level database with the following structure (simplified to only show the primary keys): Table A: a_id Table B: a_id, b_id Table C: a_id, b_id, c_id So possible values for table C would be something like this: a_id b_id c_id 1 1 1 1 1 2 1 1 3 1 2 1 1 2 2 2 ...

Hibernate mapping to object that already exists

I have two classes, ServiceType and ServiceRequest. Every ServiceRequest must specify what kind of ServiceType it is. All ServiceType's are predefined in the database, and ServiceRequest is created at runtime by the client. Here are my .hbm files: <hibernate-mapping> <class dynamic-insert="false" dynamic-update="false" mutable="true"...

Insert a record with a value partly from another table

I have two tables, Proteins and Species. Protein has Species.Id as foreign key constraint. When I insert the data, I know the name of the species that protein belongs to, but not the Id. So I do the following: PreparedStatement query = connection.prepareStatement( "SELECT Id FROM Species WHERE ShortName = ?"); query.setS...

Django: many-to-one fields and data integrity

Let's say that I have a Person who runs an inventory system. Each Person has some Cars, and each Car has a very large number of Parts (thousands, let's say). A Person, Bob, uses a Django form to create a Car. Now, Bob goes to create some Parts. It is only at the form level that Django knows that the Parts belong to some specific Car,...

foreignkey problem

Hey, Imagine you have this model: class Category(models.Model): node_id = models.IntegerField(primary_key = True) type_id = models.IntegerField(max_length = 20) parent_id = models.IntegerField(max_length = 20) sort_order = models.IntegerField(max_length = 20) name = models.CharField(max_length = 45) ...

linq to sql using foreign keys returning iqueryable(of myEntity]

I'm trying to use Linq to SQL to return an IQueryable(of Project) when using foreign key relationships. Using the below schema, I want to be able to pass in a UserId and get all the projects created for the company the user is associated with. DB tables: Projects Projid ProjCreator FK (UserId from UserInfo table) Companyid FK (Co...

django model relation definition

Hello, Let say I have 3 models: A, B and C with the following relations. A can have many B and many C. B can have many C Is the following correct: class A(models.Model): ... class B(models.Model): ... a = models.ForeignKey(A) class C(models.Model): ... a = models.ForeignKey(A) b = models.ForeignKey(B) Or is there a m...

SQL Server problems reading columns with a foreign key

I have a weird situation, where simple queries seem to never finish for instance SELECT top 100 ArticleID FROM Article WHERE ProductGroupID=379114 returns immediately SELECT top 1000 ArticleID FROM Article WHERE ProductGroupID=379114 never returns SELECT ArticleID FROM Article WHERE ProductGroupID=379114 never returns SELEC...

Delete all records that have no foreign key constraints

I have a SQL 2005 table with millions of rows in it that is being hit by users all day and night. This table is referenced by 20 or so other tables that have foreign key constraints. What I am needing to do on a regular basis is delete all records from this table where the "Active" field is set to false AND there are no other records in ...

What are the repercussions of not checking existing data when adding a foreign key?

I've inherited a database that doesn't exactly strive for data integrity. I am trying to add some foreign keys to change that, but there is data in some tables that doesn't fit the constraints. Most likely, the data won't be used again so I want to know what problems I might face by leaving it there. The other option I see is to move it ...

Foreign Key Relationships and "belongs to many"

edit - Based on the responses below, I'm going to revisit my design. I think I can avoid this mess by being a little bit more clever with how I set out my business objects and rules. Thanks everyone for your help! -- I have the following model: S belongs to T T has many S A,B,C,D,E (etc) have 1 T each, so the T should belong to each...

What is an easy way to list the foreign key contraints in an MDB?

What is an easy way to list the foreign key contraints in an MDB? Is there a system table that can be queried in order to list this information? Specifically, I need to know whether any foreign key contraints exist in the MDB. ...

MySQL "ERROR 1005 (HY000): Can't create table 'foo.#sql-12c_4' (errno: 150)"

Hi, I was working on creating some tables in database foo, but every time I end up with errno 150 regarding the foreign key. Firstly, here's my code for creating tables: CREATE TABLE Clients ( client_id CHAR(10) NOT NULL , client_name CHAR(50) NOT NULL , provisional_license_num CHAR(50) NOT NULL , client_...

Filtering foreign keys with AJAX in Django admin

I have most of this figured out already. I have AJAX returning the region/state/province when a country is selected. The correct foreign key is saved to the database, however, when the record is viewed afterwards the selected state is not shown in the select nor are any states for the selected country. I understand why this is happening ...

How to export primary keys on data-dump?

Hello, When I export my database with doctrine:data-dump, I encounter 2 problems: * the primary keys are not exported * instead of foreign keys columns correct name, it uses the name of the foreign table. For example, here are my tables: # schema.yml Planet: connection: doctrine tableName: planet columns: planet_id: ty...

MySQL/PHP: How to insert logged in user id into another table that is gathering data from a form that the user is inputting.

For the first time I am needing to join information from two tables and am quite nervous about doing it without any advice first. Basically, I am building a secure site that is accessed by authorised users. I have my login table with user_id, username, password Once the user is on the site, they have the option of inputting data into ...

Cannot mySQL share a foreign key between tables?

Hi everyone, I get error 1005 when inserting my tables. There's multiple tables referencing to the tables in my design made up in mySQL workbench, but doesn't this work? - a shared foreign contraint among several tables? Table one is called languages. It has a id column for each language in the database. Then we have productDescription...

Which Table Should be Master and Child in Database Design

I am quickly learning the ins and outs of database design (something that, as of a week ago, was new to me), but I am running across some questions that don't seem immediately obvious, so I was hoping to get some clarification. The question I have right is about foreign keys. As part of my design, I have a Company table. Originally, I...

Django: UserProfile with Unique Foreign Key in Django Admin

Hi, I have extended Django's User Model using a custom user profile called UserExtension. It is related to User through a unique ForeignKey Relationship, which enables me to edit it in the admin in an inline form! I'm using a signal to create a new profile for every new user: def create_user_profile(sender, instance, created, **kwargs):...