CREATE TABLE problems: Foreign key references.

From SQLZOO
Jump to navigation Jump to search
The printable version is no longer supported and may have rendering errors. Please update your browser bookmarks and please use the default browser print function instead.

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  VALUES (101,'2014-08-21', 42.00);
SELECT *
  FROM invoice JOIN customer ON invoice.cust_no=customer.id;