Difference between revisions of "CREATE TABLE problems: Foreign key references."

From SQLZOO
Jump to: navigation, search
 
(One intermediate revision by the same user not shown)
Line 8: Line 8:
 
</div>
 
</div>
  
<source lang=sql class='tidy'>DROP TABLE t_stupid</source>
+
<source lang=sql class='tidy'>DROP TABLE invoice;
 +
DROP TABLE customer</source>
 
<source lang=sql class='setup'></source>
 
<source lang=sql class='setup'></source>
  
 
<source lang='sql' class='def'>
 
<source lang='sql' class='def'>
CREATE TABLE customer(id INTEGER PRIMARY KEY
+
CREATE TABLE customer(
  , name VARCHAR(100)
+
  id INTEGER PRIMARY KEY,
 +
  name VARCHAR(100)
 
);
 
);
CREATE TABLE invoice (cust_no INTEGER
+
CREATE TABLE invoice(
   , whn DATE
+
  cust_no INTEGER,
   , amt DECIMAL(10,2)
+
   whn DATE,
   ,FOREIGN KEY(cust_no) REFERENCES customer(id));
+
   amt DECIMAL(10,2),
 +
   FOREIGN KEY(cust_no) REFERENCES customer(id)
 +
);
 +
INSERT INTO customer VALUES (101,'Arnold Anxious');
 +
INSERT INTO invoice (101,'2014-08-21', 42.00);
 +
SELECT *
 +
  FROM invoice JOIN customer ON invoice.cust_no=customer.id;
 
</source>
 
</source>
 
<div class="ecomm e-mysql" style="display: none"></div>
 
<div class="ecomm e-mysql" style="display: none"></div>

Latest revision as of 12:01, 21 August 2014

CREATE TABLE problems: Foreign key references.

schema:scott

A foreign key should refer to a candidate key in some table. This is usually the primary key but may be a field (or list of fields) specified as UNIQUE.

You must have REFERENCE permission on the table being referenced.

DROP TABLE invoice;
DROP TABLE customer

CREATE TABLE customer(
  id INTEGER PRIMARY KEY,
  name VARCHAR(100)
);
CREATE TABLE invoice(
   cust_no INTEGER,
   whn DATE,
   amt DECIMAL(10,2),
   FOREIGN KEY(cust_no) REFERENCES customer(id)
);
INSERT INTO customer VALUES (101,'Arnold Anxious');
INSERT INTO invoice (101,'2014-08-21', 42.00);
SELECT *
  FROM invoice JOIN customer ON invoice.cust_no=customer.id;