LinuxQuestions.org
Register a domain and help support LQ
Go Back   LinuxQuestions.org > Forums > Non-*NIX Forums > Programming
User Name
Password
Programming This forum is for all programming questions.
The question does not have to be directly related to Linux and any language is fair game.

Notices



Reply
 
Search this Thread
Old 02-16-2009, 04:02 PM   #1
kpachopoulos
Member
 
Registered: Feb 2004
Location: Athens, Greece
Distribution: Gentoo,FreeBSD, Debian
Posts: 704

Rep: Reputation: 30
sql foreign key constraint fails


Hi,
i am trying to refresh my sql knowledge a little by using mysql. I have the following code:
Code:
DROP DATABASE IF EXISTS Store;
CREATE DATABASE Store;
USE Store;

CREATE TABLE Orders
(
  oid int NOT NULL,
  cid int NOT NULL,
  PRIMARY KEY (oid),
  FOREIGN KEY (cid) REFERENCES Customer (cid)
);


CREATE TABLE Customer
(
  cid int NOT NULL,
  PRIMARY KEY (cid)
);
I am able to insert a row in Order without the corresponding key in Customer. I do not get a complain. Why is that?
 
Old 02-16-2009, 05:24 PM   #2
raconteur
Member
 
Registered: Dec 2007
Location: Slightly left of center
Distribution: slackware
Posts: 276
Blog Entries: 2

Rep: Reputation: 44
In order to set up a foreign key relationship between two MySQL tables, three conditions must be met:

Both tables must be of the InnoDB table type.
The fields used in the foreign key relationship must be indexed.
The fields used in the foreign key relationship must be similar in data type.

I'm assuming you already use the InnoDB engine, and we can obviously see the data types are similar.

Try indexing the foreign key in the Orders table.

An afterthought occurs to me but I'd have to do more research to prove it... the table creation order may be significant.
All examples I've come across for creating tables with foreign keys create the referenced table first.

There is a brief but succinct tutorial on MySQL referential integrity here.
 
Old 02-17-2009, 07:49 PM   #3
jlinkels
Senior Member
 
Registered: Oct 2003
Location: Bonaire
Distribution: Debian Wheezy/Jessie/Sid, Linux Mint DE
Posts: 4,245

Rep: Reputation: 546Reputation: 546Reputation: 546Reputation: 546Reputation: 546Reputation: 546
Raconteur, you have more knowledge about this subject than I do, but as a matter of fact tables are created using the myISAM engines by default.

jlinkels
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
My SQL statement to delete an entry in MYSQL database fails in PHP ratchie Linux - Software 1 12-29-2008 03:48 AM
Mysql foreign key doesn't allow update mohtasham1983 Programming 3 07-25-2008 11:56 PM
java: SQL duplicate key exception or query for key is faster kpachopoulos Programming 1 01-06-2008 05:32 AM
SQL - getting the primary key autogenerated of an INSERT statement vharishankar Programming 3 12-05-2005 03:47 AM
Mysql Foreign Key munna_502 Linux - Software 0 06-12-2004 05:31 PM


All times are GMT -5. The time now is 02:31 PM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
identi.ca: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration