foreign-keys

If I have multiple types of an object, when should object.type be a string and when should it be a foreign key?

Let's say I have books which can be romance, fiction, or mystery. I have 2 realistic options to store this data. One is to have a type column in my books table that is a string with the value "romance", "fiction", or "mystery". The other is to create a book_types table and store the types in there. Then my books would have a type_id ...

Entity Framework EntityKey / Foreign Key problem.

As the result of a form post, I'm trying to save a new Brand record. In my view, Gender is a dropdown, returning an Integer, which is populated from ViewData("gender") I've setup my link as follows: gID = CInt(Request.Form("Gender")) Brand.GenderReference.EntityKey = New EntityKey("DB_ENTITIES.Gender", "Id", gID) TryUpdateModel(Brand) ...

How are foreign keys and Guids dealt with in LINQ to Entities?

Hi, Just using this as an example... Here are the columns in my UserProfile table: ProfileID (Primary key) UserID (Foreign key) Address PhoneNumber now, when I want to add a new user to the database using LINQ to Entities, here is what I'm doing: UserProfile profileToAdd; profileToAdd.ProfileID = 0; profileToAdd.Address =...

Is it possible to add a logic Constraint to a Foreign Key?

Hi folks, I've got two tables and I've added a foreign key constraint. Kewl - works great. Now, is it possible to further constrain that relationship against some data in the parent table? Basically, I have animals in the parent table, and for the child table wishto only contain data where the parent data are .... um .. mammals. eg. ...

Table-agnostic Foreign Keys?

So, a simple question. First, I did read this StackOverflow question, so no need to point me towards it. I'm working on a similar problem right now. Specifically, I have a database with an Auditing table that is used to store auditing info about other tables within the db. The basic form of this table is: ID, EntityID, EntityTypeID, Ac...

Delete foreign keys in sybase 12.5

Hi, I have two tables: CREATE TABLE dbo.country ( cntry_id VARCHAR(2) NOT NULL, name VARCHAR(50) NOT NULL, CONSTRAINT pk_country PRIMARY KEY (cntry_id) CREATE TABLE dbo.city ( city_id VARCHAR(3) NOT NULL, name VARCHAR(50) NOT NULL, cntry_id VARCHAR(2) NOT NULL, CONSTRAINT pk_city PRIMARY KEY (city_id), FO...

How to add data to two tables linked via a foreign key?

If I were to have 2 tables, call them TableA and TableB. TableB contains a foreign key which refers to TableA. I now need to add data to both TableA and TableB for a given scenario. To do this I first have to insert data in TableA then find and retrieve TableA's last inserted primary key and use it as the foreign key value in TableB. I t...

Why does my datagridviewcolumn not accept changes at this point?

I'm bringing down a strongly typed dataset table into a datagridview. then adding a column of combo boxes to let the foreign keys be selected by name instead of ID. Perhaps there's an easier way to do this. can I transform the typeID column into a comboboxcolumn while still being able to cast the datasource back to my strongly typed da...

Should I use a Foreign Key to Show Tree Relationship in SQL

I am trying to model a tree relationship in a table. For instance, there are "Categories" and categories can themselves be inside a Parent category. My schema is: id int PRIMARY KEY, parent_id int, name My question is, should I label the parent_id column as a Foreign key? Foreign implies "outside" and not self-referencing. Is ther...

LinqToSql and Views don't have foriegn keys/associations?

Hi folks, I've got some tables in my Linq2Sql designer (in Visual Studio 2008). Works great. Now, I just tried dropping in a View onto the designer. It dropped on there ok, but there's NO foreign keys/associations. hmm. Ok, so then I tried to manually add an association between the View and a parent table. That worked .. but when i ...

Algorithms for Updating Relational Data

What algorithms are known to perform the task of updating a database by inserting, updating, and deleting rows in the presence of database constraints? More specifically, say that before images of rows to be deleted, after images of rows to be inserted, and both images of rows to be updated are in memory. The rows might be for several t...

MySQL: Creating table with FK error (errno 150)

I've tried searching on this error and nothing I've found helps me, so I apologize in advance if this is a duplicate and I'm just too dumb to find it. I've created a model with MySQL Workbench and am now attempting to install it to a mysql server. Using File > Export > Forward Engineer SQL CREATE Script... it outputs a nice big file fo...

SQL: How to make sure any primary key in any table "belongs to" a known Account ID (also PK) at the top of the hierarchy?

Problem as follows: I'm in charge of a creating a web services interface to a system with a huge database (hundreds of tables). The top level table in the database is "Accounts", with primary key Account_Id. Each row in any table can ultimately be traced back to a single account. Each account should have access to their own account v...

How do I define table relations in a circular reference?

In my current project I have data about colors. Each color is either a real color (with RGB values) or is a "container color" that exist out of multiple layers of colors. This potentially creates circular references which will have to be caught at application level (but that's another question) So I have Colors ------ + (PK) Id Color...

Best way to link a table to 2 different keys ?

Hi, I'm designing a mySQL DB and I'm having the following issue: Say I have a wall_posts table. Walls can belong to either an event or a user. Hence the wall_posts table must references either event_id or user_id (foreign key constraint). What is the best way to build such a relationship, considering I must always be able to know who ...

Multiple FKs with ON DELETE CASCADE to the same table (MSSQL2008)

I'm running into the problem described by KB321843 and this question. Both only talk about MS SQL Server 2005 (or older), while I'm running 2008. I'd really had hope that this is fixed in recent versions, but it doesn't seem like it is. Could someone confirm this (or, better tell me how I could get it to work)? ...

How can I get around this foreign key constraint having to have a unique name?

I'm not sure why these have to be unique, but from reading the MySQL forums it appears that they do. However, I think it has something more to do with the INDEX name. I have two tables that have foreign key constraints referencing the same primary key on a third table. If it helps, I'm using MySQL workbench to design the schema. I usua...

Django Model Inheritance And Foreign Keys

Basically, I have a model where I've created a superclass that many other classes share, and then each of those classes has some unique features that differ from each other. Let's say class A is the superclass, and class B, C, and D are children of that class. Both class B and class C can have multiples of class D, however I've seen tha...

Django: How to follow ForeignKey('self') backwards

class Achievement(MyBaseModel): parent_achievement = models.ForeignKey('self', blank=True, null=True, help_text="An achievement that must be done before this one is achieved") # long name since parent is reserved I can do : Achievement.objects.get(pk="1").parent_achievement which is great. But how do I get all the children? Ach...

Does CakePHP handle FKs on the code level or should I be adding FKs to my db too?

Will I suffer consequences later if I add FKs with ON DELETE CASCADE and such? If not, what naming convention should I be using for FKs within MySQL for CakePHP? ...