php - SQLite how get the Column ID for a REFERENCE table - Stack Overflow

admin管理员组

文章数量:1313794

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?

Edit to MikeT answer

I tried PRAGMA foreign_key_list already, but I always receive NULL for the to value.

I am using SQLiteStudio (3.3.3). If I click under Structure, enable 'extented Modus' and click then on the Foreign KEY the I can see the FOREIGN TABLE and the FOREIGN COLUMN. But that does not help me for my PHP script.

The SQLite database is a part of mAiList, a radio automation program. If I check the version, it tellsme 24. But I am not sure, if this a SQLite version number.

In the picture above, you can see, that SQLite Studio return me a CREATE statement, where REFERENCES has only the FOREIGN TABLE name, but no COLUMN.

So that looks very strange to me!

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?

Edit to MikeT answer

I tried PRAGMA foreign_key_list already, but I always receive NULL for the to value.

I am using SQLiteStudio (3.3.3). If I click under Structure, enable 'extented Modus' and click then on the Foreign KEY the I can see the FOREIGN TABLE and the FOREIGN COLUMN. But that does not help me for my PHP script.

The SQLite database is a part of mAiList, a radio automation program. If I check the version, it tellsme 24. But I am not sure, if this a SQLite version number.

In the picture above, you can see, that SQLite Studio return me a CREATE statement, where REFERENCES has only the FOREIGN TABLE name, but no COLUMN.

So that looks very strange to me!

• php
• sqlite

Share Improve this question Follow edited Feb 3 at 15:27 Geio asked Jan 30 at 15:24 GeioGeio 17511 silver badge1818 bronze badges

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

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:-

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

---

Additional

---

Regarding the edited question and:-

So that looks very strange to me!

There are two forms of some options, one for specifying the option(s) within the column definition(s), the other for specifying the options at the table level.

In addition to specifying a foreign key at the column level or the table level you can also specify a primary key at both levels.

However, specifying an option at the column level cannot be used for a composite (more than one column).

There is no need to specify the column name when using such an option at the column level because the option is specific to that column. In fact the syntax does not allow for superfluous code.

In the case of a FOREIGN KEY, at the column level the the FOREIGN KEY keywords and the column to which the constraint applies is superfluous hence just the REFERENCES table_name(column_name) syntax as opposed to the FOREIGN KEY (column_name(s)) table_name(column_name(s)) syntax.

In the case of PRIMARY KEY, at the column level, the syntax is just PRIMARY KEY, whilst at the table level the syntax is PRIMARY KEY (column_name(s))

本文标签： phpSQLite how get the Column ID for a REFERENCE tableStack Overflow