CREATE a foreign key - Revision history

91.176.206.53 at 14:08, 28 November 2012

2012-11-28T14:08:25Z

Marek at 09:34, 18 July 2012

2012-07-18T09:34:03Z

Marek: Created page with "CREATE TABLE with a foreign key

schema:scott

A foreign key references is where one field "links" to another table. The database ..."

2012-07-12T15:10:23Z

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 ..."

New page

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 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...
</div>

<source lang=sql class='tidy'>DROP TABLE track;
DROP TABLE album</source>
<source lang=sql class='setup'></source>

<source lang='sql' class='def e-access e-postgres'>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)
)
</source>

<source lang='sql' class='def'>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)
);
</source>

<div class="ecomm e-sqlite" style="display: none">
The relationship between the tables is not enforced.
Even when the album table is empty we can create a record in the track table:
<pre>
INSERT INTO track VALUES
('1122334455',1,1,'song')
</pre>
</div>
<div class="ecomm e-mysql" style="display: none">
The relationship between the tables is not enforced.
Even when the album table is empty we can create a record in the track table:
<pre>
INSERT INTO track VALUES
('1122334455',1,1,'song')
</pre>
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>.
</div>
{{CREATE and DROP ref}}

@@ Line 1: / Line 1: @@
-CREATE TABLE with a foreign key
+CREATE TABLE microprocessors
-<div class='ht'>
+ (jaar INTEGER PRIMARY KEY
-<div class=params>schema:scott</div>
+ ,architectuur VARCHAR(15)
-<div>
+ ,fabricant VARCHAR(10)
-A foreign key references is where one field "links" to another table.
+ ,aantalcores INTEGER
-The database will maintain '''referential integrity''' -
+ ,aantalthreads INTEGER
-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...
-</div>
-−
-<source lang=sql class='tidy'>DROP TABLE track;
-DROP TABLE album</source>
-<source lang=sql class='setup'></source>
-−
-<source lang='sql' class='def e-access e-postgres'>CREATE TABLE album(
-  id CHAR(10) NOT NULL PRIMARY KEY,
-  title VARCHAR(100),
-  artist VARCHAR(100)
 );
-CREATE TABLE track(
+INSERT INTO microprocessors(jaar, architectuur, fabricant, aantalcores, aantalthreads)  VALUES (2011,Sandy Bridge,intel,4,8);
-  album CHAR(10),
-  dsk INTEGER,
-  posn INTEGER,
-  song VARCHAR(255),
-  FOREIGN KEY (album) REFERENCES album(id)
-−
-</source>
-−
-<source lang='sql' class='def'>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)
 );
-</source>
-<div class="ecomm e-sqlite" style="display: none">
+SELECT * FROM microprocessors;
-The relationship between the tables is not enforced.
-Even when the album table is empty we can create a record in the track table:
-<pre>
-INSERT INTO track VALUES
-('1122334455',1,1,'song')
-</pre>
-</div>
-<div class="ecomm e-mysql" style="display: none">
-The relationship between the tables is not enforced.
-Even when the album table is empty we can create a record in the track table:
-<pre>
-INSERT INTO track VALUES
-('1122334455',1,1,'song')
-</pre>
-We can force MySQL to respect foreign keys if we use the InnoDB table type.
-See [http://dev.mysql.com/doc/mysql/en/innodb-foreign-key-constraints.html MySQL Reference Manual].
-</div>
-{{CREATE and DROP ref}}

CREATE a foreign key - Revision history

91.176.206.53 at 14:08, 28 November 2012

Marek at 09:34, 18 July 2012

Marek: Created page with "CREATE TABLE with a foreign key schema:scott A foreign key references is where one field "links" to another table. The database ..."

Marek: Created page with "CREATE TABLE with a foreign key

schema:scott

A foreign key references is where one field "links" to another table. The database ..."