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

From SQLZOO
Jump to: navigation, search
(Created page with "CREATE TABLE problems: Foreign key references. <div class='ht'> <div class=params>schema:scott</div> <div> A foreign key should refer to a ''candidate key'' in some table. Thi...")
 
Line 11: Line 11:
 
<source lang=sql class='setup'></source>
 
<source lang=sql class='setup'></source>
  
<source lang='sql' class='def'>CREATE TABLE t_stupid (a INTEGER
+
<source lang='sql' class='def'>
   ,FOREIGN KEY(a)
+
CREATE TABLE customer(id INTEGER PRIMARY KEY
      REFERENCES cia(population))
+
  , name VARCHAR(100)
 +
);
 +
CREATE TABLE invoice (cust_no INTEGER
 +
  , whn DATE
 +
  , amt DECIMAL(10,2)
 +
   ,FOREIGN KEY(cust_no) REFERENCES customer(id));
 
</source>
 
</source>
<div class="ecomm e-mysql" style="display: none">It's an old tart - it will let you reference any old rubbish.</div>
+
<div class="ecomm e-mysql" style="display: none"></div>
<div class="ecomm e-sqlite" style="display: none">Foreign key references are ignored. This problem does not arise.</div>
+
<div class="ecomm e-sqlite" style="display: none">Foreign key references are ignored in sql-lite. This problem does not arise.</div>
 
</div>
 
</div>
 
{{CREATE and DROP ref}}
 
{{CREATE and DROP ref}}

Revision as of 11:57, 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 t_stupid
 
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));
Personal tools
Namespaces

Variants
Actions
Reference
Toolbox
Google AdSense