MySql foreign key, how it actually works

Maybe a newbie question about foreign keys, but I want to know the answer.

Let's say I have 2 tables:

products -------- product_id (int) name (unique) (varchar) description (text) vendor (varchar) (foreign key: vendors.name)

AND

vendors -------- name (varchar)

I know that I should use a vendor_id (int), but this is just an example to help me ask my question.

So: if I create vendor: Apple, and product: 1, iPhone 4, Description.., Apple then the varchar "Apple" will be stored both in products and vendors, or just in vendors (because of the foreign key)? Is this a wrong db design?

-------------Problems Reply------------

This is called "normalization" in the database. In your example, there are a couple things to consider:

  1. In order for products to have a foreign key to vendors, vendors needs a key. Is name the primary key for vendors? If so, then the foreign key would also be a varchar. In that case, yes, the value "Apple" would be stored in both. (Note that this isn't a very good idea.)
  2. If you add a vendor_id integer column to the vendors table, and it is the primary key for that table, then you can add a vendor_id (or any other name) column to the products table and make it a foreign key to the vendors table. In this case, only that integer would be stored in both tables. This is where the data becomes normalized. A small, simpler data type (integer) links the tables, which contain the actual data which describes the records.

Only that key value is stored in both tables. It's used as a reference to join the tables when selecting data. For example, in order to select a given product and its vendor, you'd do something like this:

SELECT products.name, products.description, vendors.name AS vendor
FROM products INNER JOIN vendors ON products.vendor_id = vendors.vendor_id
WHERE products.product_id = ?id

This would "join" the two tables into a single table (not really, just for the query) and select the record from it.

It will be stored in both. The foreign-key constraint requires that every value in products.vendor appear somewhere in vendor.name.

(By the way, note that MySQL only enforces foreign-key constraints if the storage engine is InnoDB.)

Category:mysql Views:0 Time:2012-01-10

Related post

  • Can someone explain MySQL foreign keys 2009-11-13

    I know what they are my question is, how do you link them or are they automatically linked when you have identical names in different tables. Here is an example: Say I have an [orders] table and a [customer] table. Each row in the [orders] table has

  • Ignore MySQL foreign key constraints in PHP 2010-11-24

    Is there a way to override mysql foreign key constraints in a php script? I have a query passed to mysql from php, but it fails a foreign key constraint, is there any way to get around this without altering the db schema? I'm just doing some testing,

  • MySQL foreign key problem rails 2011-02-21

    I'm trying to add foreign keys to my table using the following code: constraint_name = "fk_#{from_table}_#{to_table}" execute %{ CREATE TRIGGER #{constraint_name}_insert BEFORE INSERT ON #{from_table} FOR EACH ROW BEGIN SELECT RAISE(ABORT, "constrain

  • MySQL foreign keys - always a good idea? 2011-03-10

    Are MySQL foreign keys always a good idea? I mean if the field I'm using is required for every row and it references another table, should I 100% of the time use a foreign key? It seems to me like I would, and it seems like I'd always want to setup O

  • MySQL Foreign Key Log 2012-03-18

    I'm looking for some place to see a log of mysql foreign key operation triggered when i delete a row. I got many tables with delete cascade option and i want to find out if all the cascade deletion was performed. Is there some command to see affected

  • MySQL foreign keys - how to enforce one-to-one across tables? 2008-09-22

    If I have a table in MySQL which represents a base class, and I have a bunch of tables which represent the fields in the derived classes, each of which refers back to the base table with a foreign key, is there any way to get MySQL to enforce the one

  • SubSonic isn't generating MySql foreign key tables 2009-06-05

    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; /

  • MySql Foreign Key index 2009-06-09

    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

  • Is it possible to use Mysql foreign keys in InnoDB tables for inverse lookup? 2009-06-29

    I wonder if it is possible to use FK's in Mysql (InnoDB) for inverse lookup. The reason - I want to read an XML-like structure from the db (using one table per "layer"), but be able to do this dynamically. I want to be able to update the xml structur

  • MySQL Foreign Key Constraint Confusion 2009-07-31

    Hey everyone, I have the following 'users' table in MySQL: CREATE TABLE `users` ( `uid` int(11) NOT NULL auto_increment, `fname` varchar(50) NOT NULL, `lname` varchar(50) NOT NULL, `role` varchar(75) NOT NULL, `region` tinyint(4) unsigned default NUL

  • MySQL foreign key problem 2009-11-19

    A client of mine recently formatted his machine and re-installed MySQL Server and my application. He complained that deleting records from master table is not affecting the child tables. I requested him to send the backup of the database. When I rest

  • Is this a job for Mysql Foreign Keys? 2010-01-05

    In the past few years I have read plenty of articles on Foreign Keys in MySQL, nothing recent though. I know they are good to use on something like a forum topic that has child post's under it, if I delete a topic with 100 post's connected to that to

  • mysql foreign key constraints 2010-01-24

    I have set foreign key integrity in a table. But i am able to delete data from the master table, which is reference in child table.. What would be the problem Actually it should say error on deletion like all referenced table data has to be deleted.

  • MySQL, foreign key constraint does not make any difference 2010-02-12

    I have a database, with several tables. The "main" table, called contacts, stores information about contacts, each having an id, a name etc... I also have like, 20 other tables, each representing a certain function, like staff, media ... Each of thes

  • MySQL foreign key to the same table failed with error 1005, errno 150 2010-02-23

    mysql> ALTER TABLE category ADD CONSTRAINT category_parent_category_id FOREIGN KEY (parent) REFERENCES category(id); ERROR 1005 (HY000): Can't create table 'sfnews.#sql-244_1' (errno: 150) DDL as follows: Create Table: CREATE TABLE `category` ( `i

  • Mysql foreign key by non unique key - how is that possible? 2010-03-02

    I was migrating mysql database to postgres and stumbled across the following block in DDL (Note: This is what I got from mysqldump): CREATE TABLE `catalog_property_value` ( `id` int(10) unsigned NOT NULL, `property_id` int(10) unsigned NOT NULL, `sor

  • MySQL foreign key creation with alter table command 2010-04-10

    I created some tables using MySQL Workbench, and then did forward ‘forward engineer’ to create scripts to create these tables. BUT, the scripts lead me to a number of problems. One of which involves the foreign keys. So I tried creating separate fore

  • MySQL foreign key constraint disappearing 2010-05-11

    This is my table: /* oefenreeks leerplan */ CREATE TABLE leerplan_oefenreeks ( leerplan_oefenreeks_id INT PRIMARY KEY AUTO_INCREMENT NOT NULL, leerplan_id INT NOT NULL, oefenreeks_id INT NOT NULL, plaats INT NOT NULL ); /* fk */ ALTER TABLE leerplan_

  • Is it possible to alter the cascading behaviour of existing mysql foreign keys? 2010-05-25

    I have two tables in mysql, both are type InnoDB and there is an existing foreign key from the first to the second. However, the foreign key was created without any cascading behaviour. The table is large, and any changes to indexes or new keys locks

  • MySQL foreign key constraints, cascade delete 2010-05-26

    I want to use foreign keys to keep the integrity and avoid orphans (I already use innoDB). How do I make a SQL statment that DELETE ON CASCADE? If I delete a category then how do I make sure that it would not delete products that also are related to

Copyright (C) dskims.com, All Rights Reserved.

processed in 0.149 (s). 11 q(s)