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 ...")
 
 
(One intermediate revision by one user not shown)
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 <a href="http://dev.mysql.com/doc/mysql/en/innodb-foreign-key-constraints.html">MySQL Reference Manual</a>.
+
−
</div>
+
−
{{CREATE and DROP ref}}
+

Latest revision as of 15:08, 28 November 2012

CREATE TABLE microprocessors 

(jaar INTEGER PRIMARY KEY
,architectuur VARCHAR(15)
,fabricant VARCHAR(10)
,aantalcores INTEGER
,aantalthreads INTEGER

);

INSERT INTO microprocessors(jaar, architectuur, fabricant, aantalcores, aantalthreads) VALUES (2011,Sandy Bridge,intel,4,8);

SELECT * FROM microprocessors;

Retrieved from "http://sqlzoo.net/w/index.php?title=CREATE_a_foreign_key&oldid=4926"
Personal tools
Namespaces

Variants
Actions
Reference
Toolbox
Google AdSense