foreign-keys

Defining foreign key relationships for Rails' models

I have a Comment class with a :foreign_key of post_id in the Post class. class Comment < ActiveRecord::Base belongs_to :post, :class_name => "Post", :foreign_key => "post_id", :counter_cache => true belongs_to :author, :class_name => "User", :foreign_key => "author_id" end But my CreateComments migration does not define a database...

Moving away from STI - SQL to break single table into new multi-table structure

I am moving old project that used single table inheritance in to a new database, which is more structured. How would I write a SQL script to port this? Old structure I've simplified the SQL for legibility. CREATE TABLE customers ( id int(11), ... firstName varchar(50), surname varchar(50), address1 varchar(50), address2 v...

How can I insert into tables with relations?

I have only done databases without relations, but now I need to do something more serious and correct. Here is my database design: Kunde = Customer Vare = Product Ordre = Order (Read: I want to make an order) VareGruppe = ehm..type? (Read: Car, chair, closet etc.) VareOrdre = Product_Orders Here is my SQL (SQLite) schema: CREATE ...

Are there any reasons you might not want to have a foreign_key in an association?

If you have Parent has_many :children Child Is there any reason a foreign key on Child (to Parent) and a corresponding belongs_to :parent might not be desirable? When, if ever, would you not want your Child to be able to access its parent? ...

How to find foreign key dependencies in SQL Server?

How can I find all of the foreign key dependencies on a particular column? What are the different alternatives (graphically in SSMS, queries/views in SQL Server, 3rd party database tools, code in .NET)? ...

Implement foreign key in database or application?

If I want to implement the relationship between Category and Classified, is a database-level nullable foreign key required or is it possible/advisable for an application to define this type of relationship without using a database constraint? [Note: Because the white dot indicates "optional" and the black dot "required", for each Categ...

Why do Rails migrations define foreign keys in the application but not in the database?

If I define a Customer and Order model in which a Customer "has many" Orders and the Order "belongs to" the Customer, in Rails we talk about Order having a foreign key to the Customer through customer_id but we don't mean that this is enforced in the database. Because Rails does not define this as a database-level constraint, there is ...

How can I display a *foreign* field value in a text box?

How do I bind a text box with a field, which doesn't belong to form's "Record Source" table, through the Design View? Example: I have "Order.cust_id" (Record Source=Order) and I want to display "Customers.name". I believe it is trivial but I have no experience with MS Access. I tried to use the text box "Control Source" property but no l...

Getting the Foreign Key constraints of a table programatically

I'm trying to use a DataTable to get the schema of a SQL Server DB. But, when try to detect the ForeignKeys, the constraints collection only brings the UNIQUE constraints. Private Sub ShowConstraints(ByVal tableName As String) Dim table As DataTable = New DataTable(tableName) Using connection As SqlConnection = New SqlConnectio...

cakephp - Creating fixtures, how to handle foreign keys?

Hey all - I'm trying to create some fixture data for my unit tests in CakePHP (via SimpleTest) and I have no idea how to handle my foreign key relationships. Here's a sample of the fixture code: <?php class SpecialtyFixture extends CakeTestFixture { var $name = "Specialty"; var $import = "Specialty"; var $records = array( ...

Foreign key optimization in SQLite

I know that SQLite does not enforce foreign keys natively, but that's not my primary concern. The question is: If I declare CREATE TABLE invoice ( invoiceID INTEGER PRIMARY KEY, clientID INTEGER REFERENCES client(clientID), ... ) will sqlite at least use the information that clientID is a foreign key to optimize queries and au...

SubSonic isn't generating MySql foreign key tables

I two tables within a MySql 5.1.34 database. When using SubSonic to generate the DAL, the foreign-key relationship doesn't get scripted, ie; I have no Parent.ChildCollection object. Looking inside the generated DAL Parent class shows the following; //no foreign key tables defined (0) I have tried SubSonic 2.1 and 2.2, and various MySq...

Create LINQ Association without Foreign Keys

Is it possible to have something like ContactAddress.Contact in LINQ without create a foreign key relationship in SQL Server between those two (which would by Contact.Id <-> ContactAddress.ContactId)? Thanks :) ...

Foreign key creation issue in MySQL

I have followed this article: http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html to create a foreign key between two tables. Every single attempt fails. Is there anything that I am missing?! It's really frustrating and I never expected to encounter this issue at all! Thanks. ...

Can SQL/Access display foreign key rows as columns?

I have an MS Access table that has a many-to-one key to another one, and I would like a query to display those items in one row. For example, Foo, Bar and Baz link to the row with ID 1, so I would like to display: 1 | Foo | Bar | Baz instead of 1 | Foo 1 | Bar 1 | Baz ...

MySql Foreign Key index

Hi, I am using a Mysql table products which has a foreign key category_id on the categories table the foreign key constraint is defined (Innodb engine). I noticed that when I run EXPLAIN SELECT * from products where category_id=1; it uses the foreign key, so I see type=Range and Key: my_foreign_key But when I run EXPLAIN SELECT * f...

Django Relational database lookups

I cant figure out how to do relationships. I have a products model and a stores model. A product has a foreign key to the stores. So i would like to get the product name, and the store name in the same lookup. Since the products model is: class Products(models.Model): PrName = models.CharField(max_length=255) PrCompany = models....

Table with multiple languages

Hi, I want to store multiple translations of an text in a MSSQL database. for example one table product with the columns ProductId ProductPrice ProductNameId primairy key = ProductId and a table with the productnames Id Language Name primairy key = Id and Language How can i create an foreign key to link the Product.ProductName...

MySQL error 1005 on table create -- wtf

My table definition: CREATE TABLE x ( a INT NOT NULL, FOREIGN KEY (a) REFERENCES a (id) ON UPDATE CASCADE ON DELETE CASCADE ) ENGINE = InnoDB; which produces the following error: ERROR 1005 (HY000): Can't create table './abc/x.frm' (errno: 150) What does this mean? ...

How do i add foreign keys to two Innob tables so that they autoupdate each other?

I have two tables users and lead_contacts which have similar data. When somebody buys a product they become a user. How should I modify the two create staements below so: that the leads table receives a new entry with first_name, last_name, company and email, when a user is created. the first_name, last_name, company and email in use...