INSERT problems: reference

From SQLZOO
Revision as of 11:55, 17 July 2012 by Marek (Talk | contribs)

(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to: navigation, search

INSERT is used to add records.

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
Personal tools
Namespaces

Variants
Actions
Reference
Toolbox
Google AdSense