MYSQL :: Why Use A Foreign Key?

Foreign Key
I add foreign key to my tables, but mysql seem to don't consider them.

For exemple the table BIERE (beer in english) :
CREATE TABLE BIERE
(
NOM VARCHAR(20) NOT NULL,
TYPEB VARCHAR(20) NOT NULL,
ORIGINE VARCHAR(20),
COTE VARCHAR(10) NOT NULL,
PRIMARY KEY(NOM),
FOREIGN KEY (TYPEB) REFERENCES TYPEBIERE (NOM) on delete cascade,
FOREIGN KEY (ORIGINE) REFERENCES ORIGINE (NOM) on delete cascade,
FOREIGN KEY (COTE) REFERENCES COTE (NOM) on delete cascade
);
So the foreign key typeb come from the table TYPEBIERE (white...brown..etc)

But if I do an insert of a beer and I type "Love" for a type of beer (Love isn't in TYPEBIERE) MySql don't show any error and add this beer to the table?

Foreign Key
I think I've finally figured out how to make a foeign key - you just copy some code into the "SQL query" box near the bottom of a table's Structure view page, right?

I thought I also had the right code to paste in, but it isn't working. Here are the stats:

Foreign Key
I've got a strange foreign key constraint problem in that when I try to do an insert into a table with some foreign keys, even though those keys reference valid fields in valid tables, I get the "cannot add or update...." [1216] error message. Code:

Foreign Key
i am able to do good with single db or table with all the queries. I like to learn how to work with multiple tables/dbs, particularly i what to learn seting foreign key?

Foreign Key
I try to create tables with foreign keys so that not to delete rows when linked to another row of another tables i try for example:

DROP TABLE IF EXISTS LIVRE;
CREATE TABLE LIVRE (
id_livre int(11) NOT NULL auto_increment,
id_genre int(11) NULL ,INDEX (id_genre),
CONSTRAINT `FK_LIVRE_GENRE` FOREIGN KEY (id_genre) REFERENCES genre(id_genre) ON DELETE RESTRICT
) TYPE=INNODB;

but i can delete a row from the table genre when exist a row in LIVRE that use it

Can someone give me the right syntax please ?

Foreign Key?
What problems would be there if I don't use foreign keys in stars_in_movies table when I select data? I feel it doesn't really matter. Anyone would guess future problems if I get rid of foreign keys from the stars_in_movies table?

movies
id:integer (primary key)
title:varchar(100)
year:integer
director:varchar(100)
banner_url:varchar(200)
trailer_url:varchar(200)

stars
id:integer (primary key)
first_name:varchar(50)
last_name:varchar(50)
dob:date
photo_url:varchar(200)

stars_in_movies
star_id:integer, foriegn key referencing stars.id
movie_id:integer, foreign key referencing movies.id

Foreign Key
I have made 2 tables in sql, these are the table names/fields:

product: product_no,name,price,stock
user: username,password

I now have to create another table called basket, with these 3 fields: username, itemno, quantity.

username and itemno are foreign keys to the 'user' and 'product' table, respectively.

So, my problem is how to create the this table with this foreign key thing, would it be:

CREATE table basket(username VARCHAR(20), itemno INT(4), quantity INt(4))

Thats a simple create table line, but what do i change in it to take into account the foreign key thing?

Foreign Key
can i use two foreign key constraints in one table. i.e can one table refer to two or more tables

Foreign Key
I'd like to create a table with PRIMARY KEY k (a,b,c) where a, b, c are
foreign key. Is it possible? How can I do?

Foreign Key
problem:

I´ve a table "A" with primary key (a1, a2), and a table "B" where the PK of A is propaged to as follow:

(b1,b2) --> A(a1,a2)

With this I get an error in MySql front.
I also tried with a sql sentence and I get an error again.

Maybe Mysql does not allow foreign keys of more than one field?

Foreign Key
i want to link PRIMARY KEY in one table as a FOREIGN KEY in an other.
I have applied "InnoDB" to both, and applying the relationship via phpmyAdmin.

When i try to enter data into the data base via some php. The Data doesn't seem to be arrive. Even thought the PHP returns say that it has been sent. If I take out the foreign key relationship, the data is sent to the tables.

Add A Foreign Key
The SQL statements below works fine if the line for the foreign key is omitted.
With the line included, I get this error message:

#1005 - Can't create table './marywhar_VegDb/test_fk.frm' (errno: 150)

I've also tried removing the constraint name and ON UPDATE and ON DELETE parameters - no difference.

Would someone please tell me what I'm doing wrong?

CREATE TABLE `levels` (
`id` tinyint(4) NOT NULL auto_increment,
`level` char(16) NOT NULL default '',
PRIMARY KEY (`id`)
) TYPE=InnoDB AUTO_INCREMENT=5;

INSERT INTO `levels` VALUES (1, 'not specified');
INSERT INTO `levels` VALUES (2, 'low');
INSERT INTO `levels` VALUES (3, 'medium');
INSERT INTO `levels` VALUES (4, 'high');

CREATE TABLE `test_fk` (
`id` smallint(6) NOT NULL auto_increment,
`level` tinyint(4) default 1,
PRIMARY KEY (`id`),
CONSTRAINT `k_level` FOREIGN KEY (`level`) REFERENCES `levels`(`id`) ON UPDATE CASCADE ON DELETE RESTRICT
) TYPE=InnoDB AUTO_INCREMENT=1;

Foreign Key
If I have 3 tables (for example Tables A, B and C) and the Primary Key for table A is a Foreign Key in table B, Do I need to show the Primary Keys of tables A AND B as Foreign Keys in table C if I wish to use data from Tables A and B, OR does the fact that the Primary key of Table A is a Foreign key in Table B mean that I can obtain the data from Table A via Table B (because of the Foreign Key) through the use of a query.

Foreign Key
how we can create a foreign key in mysql?

Foreign Key
I tried to create a new table with a foreign key but It doesnt work.. this is the code>
CREATE TABLE Groupe
(
id_groupe VARCHAR(5) ,
nbre_member INT UNSIGNED ,
PRIMARY KEY (id_groupe)
); //the groupe table is created

CREATE TABLE Student
(
code_student VARCHAR(10),
Name VARCHAR(10),
id_groupe VARCHAR(5),
PRIMARY KEY (code_student)
FOREIGN KEY (id_groupe) REFERENCES Groupe(id_groupe)
ON DELETE CASCADE
ON UPDATE CASCADE
); // error in SQL syntax.

Foreign Key
I have a customer table and a products table. The clientID is a foreign key inside the products table. If i edit the clientId inside the product table i get:

1451 - cannot delete or update a parent row; a foreign key constraint fails ('database01/customers', CONSTRAINT 'client_ibfk_1 'FOREIGN KEY('clientId') REFERENCES 'Products' ('clientId'))

any ideas ? :|

From the customers table : CONSTRAINT `client_ibfk_1` FOREIGN KEY (`clientId`) REFERENCES 'Products`

Foreign Key
when you create your tables and a table contains a foreign key does that key automatically become part of the primary key for that table ? Say the table that is its being mapped to already has a unique primary key.

Foreign Key
I'd like to create a table with PRIMARY KEY k (a,b,c) where a, b, c are
foreign key. Is it possible? How can I do?

Foreign Key
right i have two tables:

members:

Field Type Null Default
customer_id int(5) Yes NULL
fobnumber int(5) Yes 00000
firstname varchar(32) Yes NULL
lastname varchar(32) Yes NULL
emailaddress varchar(32) Yes NULL
access_id int(5) Yes NULL

Indexes:
Keyname Type Cardinality Field
PRIMARY PRIMARY 1 customer_id
access_id INDEX 1 access_id

userAccessDetails:

Field Type Null Default
id int(10) Yes NULL
username varchar(32) Yes NULL
password var(32) Yes NULL

Indexes:
Keyname Type Cardinality Field
PRIMARY PRIMARY 0 id

I am wanting to link userAccessDetails->id (PRIMARY KEY as a foreign key for members->access_id.

I have applied "InnoDB" to both, and applying the relationship via phpmyAdmin.

When i try to enter data into the data base via some php. The Data does seem to be arrive. even thought the PHP return that it has been sent. If i take out the foreign key relationship the data is sent to the tables.

Foreign Key
I have two tables, one created using innoDB while the other uses MyISAM. Can I build a foreign key across these two tables?

A Foreign Key
In mysql, does foreign key work this way : instead of deleting the child entries when the parent entry is deleted, delete the parent entry when ALL child entries refering to it are deleted ?

Foreign Key
If I have a table ,A, that have been link to other table
is there any command that can show all the table link to the Table?

Foreign Key
I am using XAMPP and MySql. I want to asssign foreign key constraint using phpmyadmin. I am quite conversant with Oracle and Mysql.what i did is i typed sql query in phpmyadmin sql window. when i try to see structure of the table it displays Index in Key column rather than Foreign key. Is it Correct?? Does it mean that foreign key cinstraint is applied??Is there any way to define Foreign key using phpmyadmin??

Foreign Key
How would one implement a dual foreign key, kind of like an "and".
for example

table a( field01, field02)

table b(field01, field02

foreign key "field01 AND field 02"

)

Foreign Key
I have created a relational DB. But when i go to insert data into a table which has 2 F keys in it...... ie, 2, 1-many relationships comin in, it says that
Cannot add or update a child row: a foreign key constraint fails (1216)

Foreign Key
I have a table that has columns with foreign keys to other tables. Table 1 relates to table2-5. Tables 2-5 rely on an auto incremented id number generated when i insert data into table 1.
So if I create a foreign key for table 2-5 on table 1 then I can't insert data into table 1 because of the foreign key constraints. However I can't insert data into tables 2-5 without the auto incremented value from table 1.
If I create a foreign key for table 1 on tables 2-5 then when I delete table 1 the delete will not cascade to tables 2-5.
How should I structure my foreign key relationships so I'm able to do this?

Foreign Key
I have a table that has 2 columns with foreign key referencing on on different table. i've been trying to create the foreign key but always get error. Help.

Table
Owner (ownerid, etc.)
Lot (lotid, etc.)
Payment (ownerid, lotid) <- both column referce on the table mentioned respectively

Foreign Key Constraint
I have the following 2 tables with a many 2 many relationship between them
Category TABLE and Member TABLE.

CREATE TABLE Category
(
CategoryId INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
CategoryName VARCHAR(25)
);

CREATE TABLE Member
(
MemberId INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
MemberName VARCHAR(75)
);

A lookup table connecting the above 2 tables is defined as
CREATE TABLE favouriteCategory
(
CategoryId INT NOT NULL,
MemberId INT NOT NULL
);

I tried defining a FOREIGN KEY Constraint after creating the favouriteCategory table as

ALTER TABLE favouriteCategory
ADD CONSTRAINT categoryId_FK
FOREIGN KEY CategoryId
REFERENCES Category(CategoryId);

MySQL gives me this error

You have an error in your SQL syntax;
check the manual that corresponds to your MySQL server version for the right syntax to use near 'ALTER TABLE favouriteCategory
ADD CONSTRAINT categoryId_FK
FOREIGN KEY CategoryId
REFERE' at line 1

Could someone tell me what I am doing wrong?

How To Distinguish The FOREIGN KEY?
How do i know if the table has FOREIGN KEY specially in PHPMYADMIN?

And can i call two tables at once even though they are both don't have FOREIGN KEY?

Foreign Key In Another Database
I am building a web application on a server that offers multiple mysql databases, but each database has a maximum capacity of 100 MB.

I'd like to balance the load of data over several databases, however, many of my tables use foreign key constraints. I would like to know if it's possible in mysql to have a table with foreign key that references another table in a different database ? And if so, how this can be done ?

I have tried using databasename.tablename (for example: FOREIGN KEY (id) REFERENCES databasename.tablename (id)), but I get errno: 150

Do I Really Need Foreign Keys
I am currently designing a database that is to be used for ordering products from a site.

member, order-states, order, order-lines, product, review

I am considering using foreign keys between the tables. I am not sure if this would be the best way to do it or not.

Q1: If I use fk which tables need to be innodb, the ones containing the fk, the ones the fk are from or all of them.

Q2: If I were to delete a member do all related table entries get removed or does that only happen with the cascade keyword. This is important as you would still need a record of the transactions in the orders table.

Q3: What benefits are there to fk using innodb over myisam and simply storing the value from one table as a field in the other.

Hope these questions aren't to trivial and feel free to point me to tutorials. Thanks.

Foreign Key Value In SQL Query...?
Currently toying with this cart script...
http://www.zend.com/zend/spotlight/p...ping-cart4.php

At one stage when I get the contents of the cart this query:

PHP

SELECT inventory.product, inventory.price,
  shopping.quantity, inventory.description,
  SUM(inventory.price*shopping.quantity) as subtotal
  SUM(subtotal) as total
FROM shopping, inventory
WHERE session='SESSID' AND
  shopping.product = inventory.product
GROUP BY shopping.product

is generated by this:

PHP

function get_contents() {
   $count = 0;

   $query = "SELECT ".$this->inv_table.".product," .
          $this->inv_table.".price," .
          $this->cart_table.".quantity,".
          $this->inv_table.".description,
          SUM(".$this->inv_table.".price*" .
          $this->cart_table.quantity .") as subtotal,
          SUM(subtotal) as total
          FROM ".$this->cart_table.",".$this->inv_table."
          WHERE session='".$this->cart_id."' AND
          ".$this->cart_table.".product = ".
          $this->inv_table.".product GROUP BY ".
          $this->cart_table.".product";

      $result = mysql_query($query, $this->dblink);
    for($count = 0;
        array_push($contents, mysql_fetch_array($result));
        $count++);

    $contents['items'] = $count;

    return $contents;
}

My setup is a little different in that "inventory.price" is infact a foreign key to a "pricebands" table with 11 different price bands.

How can i get the correct price from my "pricebands" table within the MYSQL query??

In essence something like this...

PHP

SELECT inventory.product,
(SELECT pricebands.bndPrice WHERE inventory.price = pricebands.bndID) AS inventory.price,
  shopping.quantity, inventory.description,
  SUM(inventory.price*shopping.quantity) as subtotal
  SUM(subtotal) as total
FROM shopping, inventory
WHERE session='SESSID' AND
  shopping.product = inventory.product
GROUP BY shopping.product

Foreign Key Concept
I am new to MySQL.
In my project i make use of multiple tables having relation with each other as one-to-many.
A1 table has one to many relation with z1 table.
A2 table also has one-to-many relation with z1Table.
i.e.z1 table has 2 foreign keys in it.

How to index z1 table?
How to store data in z1 table when there are multiple foreign keys in it?

Foreign Keys - To Use Or Not To Use?
I'm developing a large app with more than 100 tables and expecting very large traffic. Performance is an issue for me. According to the MySQL doc, using Foreign Keys in InnoDB (which I'm using) will affect performance.

I'm wondering how much does performance suffer and is it worth it to NOT use FKs and let the app code handle the table dependencies at the risk of getting orphaned records?

Foreign Keys
I have a table called Offer, where the primary key is ID number and there are two foreign keys - Item ID and Member ID (these are the primary keys from the Item table and Member ID).
When I insert data into all the tables, how should I enter it so the foreign keys get the data from the Item and Member tables so it appears in the Offer table.

Foreign Key Creation
I have a website that pulls data from a database. For some reason, after I add data to the database, from the site, the site will pull it up fine on the page, but then, sometimes, out of the blue, it does not. The data will be in the database, but it won't appear on the page. The strange thing about the whole situation is that when I add a new entry to the database, the site will find the information it couldn't find before and it will appear on the page...

Foreign Keys
I'm reading the documentation on foreign keys for MySQL:

Both tables have to be InnoDB type, in the table there must be an INDEX where the foreign key columns are listed as the FIRST columns in the same order, and in the referenced table there must be an INDEX where the referenced columns are listed as the FIRST columns in the same order.

The above quote baffles me. What in the world are they talking about? What do they mean by "first columns" and what do they mean by "same order"?

Display FOREIGN KEY?
I want to know the structure of the table. I want to see the FOREIGN KEY, if it has index?, primary key? etc.

4.0.15 Foreign Key Creation Bug?
The table creation script(at the end of this post) works fine on
4.0.1-alpha-win, but the foreign key constraints fail on 4.0.15-win. I
am starting the server with the same command for both versions:

mysqld-max-nt --console --transaction-isolation=SERIALIZABLE

In 4.0.15-win I can extract the following error after I run the table
creation script:

[test01] ERROR 1005: Can't create table '.ibdata#sql-a14_3.frm'
(errno: 150)

Also, the InnoDB status in 4.0.150-win shows:
------------------------
LATEST FOREIGN KEY ERROR
------------------------
030930 19:38:49 Error in foreign key constraint of table
ibdata/#sql-a14_3:
Cannot find an index in the referenced table where the
referenced columns appear as the first columns, or column types
in the table and the referenced table do not match for constraint:
FOREIGN KEY (contactInfo_fk) REFERENCES contact(pk) ON DELETE SET NULL
See http://www.innodb.com/ibman.html for correct foreign key
definition.

But the above is wrong. I DO have an index defined on the foreign key,
it IS the first column in the index, and their IS NO type mismatch.
Since this works on 4.0.1, I have to think this is a bug. I need a
working version of the server that supports transaction savepoints.
Any help appreciated!!

# Connection: test01
# Host: localhost
# Saved: 2003-09-29 18:16:42
#
CREATE TABLE orderjoin (
owner BIGINT,owned BIGINT
) TYPE=InnoDB;
CREATE TABLE orders (
pk BIGINT UNSIGNED AUTO_INCREMENT NOT N.....................................

Foreign Keys
Is there a way to view all foreign keys in a table?

Foreign Key Clarification
Building (normalising) my Db, I have a query for you guys...

If the admin person queries tbl_premises, can they automatically get the country data from tbl_country by use of foreign keys, or, must they query tbl_country directly. Maybe it would work both ways but which would be more efficient/quicker/'shortest perl code'? ....

Foreign Key Syntax
I use hiberanate to generate sql. For a many-to-many relationship, it will generate:

DROP TABLE IF EXISTS `mts_ticketcase_ticket`;
CREATE TABLE `mts_ticketcase_ticket` (
`caseId` bigint(20) NOT NULL,
`ticketId` bigint(20) NOT NULL,
`idx` int(11) NOT NULL,
PRIMARY KEY (`caseId`,`idx`),
KEY `FK9FDEEA9C960AEF5F` (`caseId`),
KEY `FK9FDEEA9CCEA3DF17` (`ticketId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

The problem is, caseId and ticketId are foreign keys, but it didn't point to another table, like
FOREIGN KEY (case_id) REFERENCES case (case_id)

Why it is so smart ?

Deleting A Foreign Key
I was wondering if some of you know how to delete a foreign key on MySQL 4.0.5 (InnoDB), I tried the SQL92 syntax but it doesn`t works (I can not define a name to the foreign key when I create it, but I can give a constraint name to it, what it is useful for?)

MySQL syntax to create a FOREIGN KEY from an ALTER TABLE:
ALTER TABLE table_name ADD [CONSTRAINT name] FOREIGN KEY (col, ...) REFERENCES anotherTable(col1, ...);

STANDARD SQL92 syntax to delete a FOREIGN KEY:
ALTER TABLE table_name DROP FOREIGN KEY foreign_key_name;

Using Foreign Keys
I'm experimenting with a few tables that rely on mutual ID keys (ie - for orders table, retrieve customer details from customer table, based on customerID key available in both). I've noticed that in the Query Browser tool, when creating a new table there's a place to define foreign keys (in the Table Editor GUI). How might I put this to good use and where can I find more reference on the issue?

Foreign Keys ....
I know how to create tables and databses, but I'm not familiar with using foreign keys. I have a MySQL book, but it wasn't very clear on how to set up foreign keys...Can someone give me a a quick example?

I'm working on a contact list at the moment...I'm planning on having person_id, First_Name, Last_Name in one table and Address, Phone, etc in another...I know I'd probably link the two by the person_id....