CREATE TABLE problems: Foreign key references.

From SQLZOO
Revision as of 12:01, 21 August 2014 by Andr3w (Talk | contribs)

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

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

Variants
Actions
Reference
Toolbox
Google AdSense