Solved

SQL and FOREIGN KEY constraint syntax

Posted on 2004-08-18
7
1,220 Views
Last Modified: 2012-05-05

I have two tables in a MySQL database. I create them like this:

CREATE DATABASE test;

CREATE TABLE test.testtable_1 (
  did    SMALLINT
         NOT NULL
         AUTO_INCREMENT,
  kid    INT
         NOT NULL,
  PRIMARY KEY (did),
  INDEX (did),
  INDEX (kid),
  CONSTRAINT kid_fkey FOREIGN KEY (kid)
    REFERENCES testtable_2 ON DELETE NO ACTION
);

CREATE TABLE test.testtable_2 (
  kid    INT
         NOT NULL
         AUTO_INCREMENT
         PRIMARY KEY,
  INDEX (kid)
);


I want the foreign relationship between testtable1.kid and testtable2.kid to ensure that I cannot insert rows in testtable1 where kid does not match with a kid from testtable2. However, when I look at the table in PHPMyAdmin, the relationship is not shown and I can also insert whichever data I want into testtable1, regardless of the data in testtable2.

What is the correct way to create a foreign key constraint that enforces this behaviour?

/A.
0
Comment
Question by:Achton
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
7 Comments
 
LVL 11

Assisted Solution

by:cjjclifford
cjjclifford earned 125 total points
ID: 11829757
Hi,

I don't know MySQL at all, but I would have thought that testtable_2 would have to be created before testtable_1 for the foreign key reference to work - I might be way off here, I usually work on Oracle.

Cheers,
C.
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 125 total points
ID: 11829856
please specify the version and platform of MySQL you are using:
http://dev.mysql.com/doc/mysql/en/InnoDB_foreign_key_constraints.html
* Both tables must be InnoDB type

I agree with cjjclifford that the second table needs to be created first, before you have implement a referential constraint on the first table...
Do you really get NO error message while running the script like this?

CHeers

0
 

Author Comment

by:Achton
ID: 11830094
No error message whatsoever. I guess MyISAM bases just ignore foreign keys.

But yes, I see now that they need to be InnoDB, and I also believe that the constraint should be tied to testtable2, and from there to testtable1's primary key. Actually, I want to have several key constraints from testtable3, 4 and 5 to fields in testtable1, but since foreign key constraints can only be tied to primary keys, how can I do this? Have several primary keys in testtable1, maybe?

I'd think maybe like this:

CREATE DATABASE test;

CREATE TABLE test.testtable_1 (
  did    SMALLINT
         NOT NULL
         AUTO_INCREMENT,
  kid    INT
         NOT NULL,
  eid    INT
         NOT NULL,
  PRIMARY KEY (did,kid,eid)
) TYPE = INNODB;

CREATE TABLE test.testtable_2 (
  kid    INT
         NOT NULL
         AUTO_INCREMENT
         PRIMARY KEY,
  INDEX (kid),
  CONSTRAINT kid_fkey FOREIGN KEY (kid)
    REFERENCES testtable_1(kid) ON DELETE NO ACTION
) TYPE = INNODB;

CREATE TABLE test.testtable_3 (
  eid    INT
         NOT NULL
         AUTO_INCREMENT
         PRIMARY KEY,
  INDEX (eid),
  CONSTRAINT eid_fkey FOREIGN KEY (eid)
    REFERENCES testtable_1(eid) ON DELETE NO ACTION
) TYPE = INNODB;


But then I get the (in)famous "#1005 - Can't create table './test/testtable_2.frm' (errno: 150)"-error. What's up?

I'm using MySQL 4.0.16 for Netware.

Thanks,
A.
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 11830203
You can have several foreign keys (1 per table) to "connect" to the same parent table (ie the same primary key field). Now, what are you currently designing, giving more speakable column names would be helpful to understand (you, us, and anyone later needing to read your code/db design).
Also, you need to properly understand the concept of foreign keys in order to correctly use and implement them.
CHeers
0
 
LVL 11

Expert Comment

by:cjjclifford
ID: 11830220
just to extend on what angelIII says, a primary key with multiple columns is still only a single primary key - all 3 need to be defined for a foreign key...
0
 

Author Comment

by:Achton
ID: 11840778
angelIII: Thanks for the advice, but the naming is in Danish, so I'm not sure it would make things much easier to understand. But I'll try to make it more readable.

Apart from that, I have taken a basic database administration course at uni, so I *ought* to know something about it - only i was a long time ago, and I worked primarily with pgSQL. So my main problem is figuring out how MySQL works.

cjjclifford: I'm not sure I follow you. I was surprised that multiple primary keys were possible, without them becoming a composite key, or maybe the first one becoming the primary key, and the rest remaining candidate keys. But that is probably just my misconception of the subject.

Anyway, here's what I've learned (most of it is pretty obvious now that I think about it, but still):
- Use InnoDB
- Make sure all referenced keys have EXACTLY the same type - e.g. do not AUTO_INCREMENT one key and not the other, etc.
- Make sure all referenced key types have EXACTLY the same size and signing
- Make sure you create FOREIGN KEY references only to primary keys
- Make sure all referenced and referencing keys have associated INDEXes, and that they are created in the same order, and that they are created SEPERATELY for each foreign key
- Create the referencing tables LAST

So, the proper syntax for what I wanted to achieve would be:


CREATE TABLE status_tbl (
    sid     INTEGER
            NOT NULL,
    PRIMARY KEY (sid),
) TYPE = INNODB;

CREATE TABLE unit_tbl (
    uid     INTEGER
            NOT NULL,
    PRIMARY KEY (uid),
) TYPE = INNODB;

CREATE TABLE system_status  (
    id      SMALLINT
            NOT NULL
            AUTO_INCREMENT,
    sid     INTEGER
            NOT NULL,
    uid     INTEGER
            NOT NULL,
    PRIMARY KEY (id),
    INDEX (sid),
    FOREIGN KEY (sid)
      REFERENCES status_tbl(sid)
      ON DELETE CASCADE,
    INDEX (uid),
    FOREIGN KEY (uid)
      REFERENCES unit_tbl(uid)
      ON DELETE CASCADE
) TYPE = INNODB;

This works, and does what I want.

Thanks for your help guys, I hope you're happy with the way I split the points.

/A.¨
0
 
LVL 11

Expert Comment

by:cjjclifford
ID: 11841696
Hi,

Thanks for the points. I was just saying that a primary key composed of 2 or more columns is still only a single primary key (composite key).

Cheers,
C.
0

Featured Post

Database Solutions Engineer FAQs

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller single-server environments.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

630 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question