foreign-keys

How to show the tablenames with foreign key = myId

I am trying to figure out where my primary keys of a table with translation per language resides as a foreign key. This is what i already have ... SELECT * FROM ( SELECT TM.seqtrans, T.trans, CASE T.seqlang WHEN 1 THEN 'NL' WHEN 2 THEN 'FR' ...

Disable Constraints on Bulk Update in SQL Server

I need to change the values of a PK/FK (add 10000) on 2 tables. How do I tell the two tables involved that they should not care about referential integrity during the update, but to care after. I don't want to have to drop and recreate the relationships if I don’t have to. ...

how to handle deletion of many objects from a certain table

We have a user table which contains all the users from the company. As an administrator you have the permission to delete user entries. The UI shows all the users with a checkbox per row to indicate if it needs to be deleted. It's quite possible that other tables have foreign key relationship(s) with the user table and hence would preven...

Update a foreign key in Entity Framework

Hi! I have created a partial class of an entity for getting a foreign key property on it. public partial class Artikel { public int WarengruppenID { get { if (WarengruppeReference.EntityKey == null) return 0; return (int)WarengruppeReference.EntityKey.EntityKeyValues[0].Value; } ...

PRIMARY and INDEX keys for one FOREIGN column in MySQL

I used MySQL Workbench to prepare a database layout and exported it to my database using phpMyAdmin. When looking at one table, I got the following warning: PRIMARY and INDEX keys should not both be set for column gid gid is a foreign index which is the primary key of a different table, and which is also part of the primary key of ...

Is it possible to have foreign key enforced without object-to-object mapping?

Assuming the following mappings are provided: <class name="A" table="a_table"> <id name="id"/> <many-to-one name="entityB" column="fk_B" not-null="false" unique="true"/> </class> <class name="B" table="b_table"> <id name="id"/> </class> Java class: public class A { private long id; private B entityB; // getters and se...

Basic Database design. Use a another table or colum

I have a table that holds information about a particular Object, Say Item and has columns ItemID, ItemName, price, ItemListingType.....LastOrderDate One of the bits of information, ItemListingType could be one of 10 different types such as: private, gov, non-gov, business... etc (strings) and could be extended to more types in future....

Inherited abstract model, can't save foreignKey fields

Hi In Django application I have these models: class DLL(models.Model): prev = models.ForeignKey('self', related_name = 'prevItem', blank = True, null = True) next = models.ForeignKey('self', related_name = 'nextItem', blank = True, null = True) class Meta: abstract = True class SomeData(DLL): name = models.TextField() The pr...

Strategy to enforce foreign key at service layer instead of db layer

Example: in my data model I have a many-to-many relationship between Role and Order, via a Permission table that have an extra column for the name of the permission. E.g., the 'admin' role can view and modify orderA, but only view orderB. The permissions' names are, of course, 'view' and 'modify'. In classic db design fashion, the forei...

importing geonames database into postgres for django project - problem with foreignkey constraints

The geonames database has two models that refer to each other. In a postgres database, reference is provided by foreign key relationships. The problem is writing data to the database - writing to one table without having the referenced id in the other table violates a foreign key contraint. Class Geoname id = IntegerField(primary_...

Can this Postgresql table be converted to a MySql table?

Given a Postgresql table schema: create table thing ( id serial primary key, key text, type int references thing, latest_revision int default 1, created timestamp default(current_timestamp at time zone 'utc'), last_modified timestamp default(current_timestamp at time zone 'utc') ); $for name in ['key', 'type',...

How to setup Hibernate @ManyToMany association with cascades on both foreign keys?

I'm trying to map a @ManyToMany association using hibernate. But so far I only managed to have cascade on one of the foreign keys. My source code goes like this: @Entity public class Airplane { @Id @GeneratedValue(strategy=GenerationType.AUTO) private Long id; @OnDelete(action=OnDeleteAction.CASCADE) @ManyToMany(m...

database design question

Hi all, I am building a database as a simple exercise, it could be hosted on any database server, so I am trying to keep things as much standard as possible. Basically what I would like to do is a 'code' table that get referenced by other entities. I explain: xcode id code r role p property code r admin r staff p title .... then I ...

duplicated foreign key constraints - reasons for or against

I've just come across a table in production which has 4 foreign key constraints. Two of the constraints are exact duplicates of the other two. ie fk1(a_id) references a(id) fk2(a_id) references a(id) fk3(b_id) references b(id) fk4(b_id) references b(id) I have never seen this before ... it strikes me as being quite wrong and my gut ...

How can I enforce referential integrity for optionally present uniquekeys?

We have multiple offices, and within each office there are multiple departments (some departments have employees in multiple offices). We have two existing systems that identify employees in different ways: in one, employees are identified by IDA; in the other employees are identified by IDB. In the cases where an employee is identifie...

Is there a neat workaround to the lack of foreign key identity columns in Entity Framework 3.5?

I'm having to downgrade a site from .NET 4 beta 2 to .NET 3.5. The biggest hurdle is all the foreign key identity values I reference/lookup, as this isn't supported natively in EF 3.5. Does anyone know of a reasonable work-around for this? An example of what I mean is: contacts.Where(contact => contact.TypeGuid == guid) [TypeGuid] ...

.NET / SQLite. Inserting parent/child record; how do I get the foreign key?

I use VS 2008 (C#) and SQLite via ADO.NET 2.0 Provider (SourceForce project). The database used by application contains an "employees" (parent) and "employmentHistory" (children) datatables. "eploymentHistory" datatables is supposed to contain all the working contracts for any given employee from day 1 (e.g. a promotion will generate a ...

MySQL: Two foreign keys in one table referring to another table

Hi, I've come across something that seemed simple before but has me scratching my head again. I have a table for users: user_id (PK) | username| email | something ... and a table for "views" for when one user has viewed another user: view_id (PK) | viewer_id | viewed_id | view_date The "viewer_id" and "viewed_id" are both user_ids,...

Exception about foreign key

Here is the inner exception: InnerException: System.Data.UpdateException Message=Entities in 'OrganizerDBEntities.Assignments' participate in the 'CourseId' relationship. 0 related 'Course' were found. 1 'Course' is expected.` I have three tables (Folder, Assignment, Course). The Assignment table has a primary key called Assignm...

Database Design - NULL Foreign Keys

Hello, and thanks for reading. I'm making a DB for a puppy shop. I have a table for puppies and a table for owners. A puppy can have one owner, owners can own more than one puppy, but not all puppies are owned. What's a good way to handle this situation? Do I use a FK in the puppy table that is NULL if the puppy doesn't have an owne...