Difference between revisions of "INSERT problems: reference"
From SQLZOO
(Created page with "INSERT is used to add records. <div class='ht'> <div class=params>schema:scott</div> <div> If a foreign key is set up between two tables it may be that you cannot insert unles...") |
|||
| Line 1: | Line 1: | ||
| − | INSERT | + | Can't INSERT because of reference. |
<div class='ht'> | <div class='ht'> | ||
<div class=params>schema:scott</div> | <div class=params>schema:scott</div> | ||
Latest revision as of 11:55, 17 July 2012
Can't INSERT because of reference.
schema:scott
If a foreign key is set up between two tables it may be that you cannot insert unless a related record exists. In this example we cannot add ('Tom', 'ma') to t_staff table unless we first create a maths department ('ma', 'Mathematics') in the t_dept table.
CREATE TABLE t_dept(
id CHAR(2)
,fname VARCHAR(20)
,PRIMARY KEY(id));
CREATE TABLE t_staff(
name VARCHAR(20)
,dept CHAR(2)
,PRIMARY KEY(name)
,FOREIGN KEY(dept) REFERENCES t_dept(id))
DROP TABLE t_staff; DROP TABLE t_dept;
CREATE TABLE t_dept(id CHAR(2), fname VARCHAR(20),PRIMARY KEY(id)); CREATE TABLE t_staff(name VARCHAR(20), dept CHAR(2),PRIMARY KEY(name),FOREIGN KEY(dept) REFERENCES t_dept(id));
INSERT INTO t_dept VALUES ('co','computing'); INSERT INTO t_staff VALUES ('andrew','co'); INSERT INTO t_staff VALUES ('tom', 'ma'); SELECT * FROM t_staff
INSERT problems: reference
