Difference between revisions of "CREATE a foreign key"
From SQLZOO
(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 | + | 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) );
CREATE a foreign key
