|
|
| 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(
| |
| − | 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(
| + | 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>
| |
| | | | |
| − | <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 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;
