Difference between revisions of "CREATE a foreign key"

From SQLZOO
Jump to: navigation, search
(Created page with "CREATE TABLE with a foreign key <div class='ht'> <div class=params>schema:scott</div> <div> A foreign key references is where one field "links" to another table. The database ...")
 
Line 67: Line 67:
 
</pre>
 
</pre>
 
We can force MySQL to respect foreign keys if we use the InnoDB table type.
 
We can force MySQL to respect foreign keys if we use the InnoDB table type.
See <a href="http://dev.mysql.com/doc/mysql/en/innodb-foreign-key-constraints.html">MySQL Reference Manual</a>.
+
See [http://dev.mysql.com/doc/mysql/en/innodb-foreign-key-constraints.html MySQL Reference Manual].
 
</div>
 
</div>
 
{{CREATE and DROP ref}}
 
{{CREATE and DROP ref}}

Revision as of 10:34, 18 July 2012

CREATE TABLE with a foreign key

schema:scott

A foreign key references is where one field "links" to another table. The database will maintain referential integrity - this means that there must be a corresponding record in the other table - in this example each track must be on an album that exists in the album table.

The field (or fields) linked to must be unique - usually the other field is the primary key of the other table.

In this example we store details of tracks on albums - we need to use three columns to get a unique key - each album may have more than one disk - each disk will have tracks numbered 1, 2, 3...

DROP TABLE track;
DROP TABLE album
 
CREATE TABLE album(
  id CHAR(10) NOT NULL PRIMARY KEY,
  title VARCHAR(100),
  artist VARCHAR(100)
);
 
CREATE TABLE track(
  album CHAR(10),
  dsk INTEGER,
  posn INTEGER,
  song VARCHAR(255),
  FOREIGN KEY (album) REFERENCES album(id)
)
CREATE TABLE album(
  id CHAR(10) NOT NULL PRIMARY KEY,
  title VARCHAR(100),
  artist VARCHAR(100)
);
 
CREATE TABLE track(
  album CHAR(10),
  dsk INTEGER,
  posn INTEGER,
  song VARCHAR(255),
  FOREIGN KEY (album) REFERENCES album(id)
);
Personal tools
Namespaces

Variants
Actions
Reference
Toolbox
Google AdSense