CODEWITHSUNDEEP
sqlite
Georgio
Georgio

Reputation: 157

SQLite how get the Column ID for a REFERENCE table

I am try to write a PHP script to convert a SQLite database to an MySQL database. From SQLite I only get the name of the table but not the column id! To create a table in MySQL I got an error message if I only define the table name. FOREIGN KEY (column_of_the_current_table) REFERENCES foreign_table_name (column_id_of_foreign_table) My SQLite gives me only the foreign_table_name but not the column_id_of_foreign_table.

Is there a SQLite command to get the ´column_id_of_foreign_table´ too?

Upvotes: -1

Views: 9

Answers (1)

MikeT
MikeT

Reputation: 56928

The foreign_key_list PRAGMA can be used to determine the columns.

A result row has the following columns:-

  1. id
  2. seq
  3. table
  4. from
  5. to
  6. on_update
  7. on_delete
  8. match

e.g. using:-

CREATE TABLE IF NOT EXISTS p (id INTEGER, a, id2 INTEGER UNIQUE, PRIMARY KEY (id,id2));
CREATE TABLE IF NOT EXISTS c (id INTEGER PRIMARY KEY, a, ref INTEGER, ref2 INTEGER, FOREIGN KEY (ref,ref2) REFERENCES p(id,id2)); 
PRAGMA foreign_key_list(c);

Results in:-

result

i.e. both the from and to column names are returned.

Upvotes: 0

Related Questions