Sqlite syntax error, please tell me where it is?

Question

Can you guys please point me to the error? And, by the way, is there any SQLite syntax highlighter? Thanks.

    sqlite> .schema recordtypes
    CREATE TABLE recordtypes (record_id text primary key);
    sqlite> .schema headers
    CREATE TABLE headers (header_id text primary key);
    sqlite>                                
    sqlite> 
    sqlite> CREATE TABLE record_to_headers (id INTEGER, recordid TEXT, FOREIGN KEY(recordid) REFERENCES recordtypes(record_id), headerid TEXT, FOREIGN KEY(headerid) REFERENCES headers(header_id));
    Error: near "headerid": syntax error

Answer 1

I believe you need to define all your field then map them to foreign keys, as so:

CREATE TABLE record_to_headers (id INTEGER, recordid TEXT, headerid TEXT, FOREIGN KEY(recordid) REFERENCES recordtypes(record_id), FOREIGN KEY(headerid) REFERENCES headers(header_id));

Let me know if that works.

Answer 2

Put your constraints after column definitions:

CREATE TABLE record_to_headers (
    id INTEGER,
    recordid TEXT,
    headerid TEXT,
    FOREIGN KEY(recordid) REFERENCES recordtypes(record_id),
    FOREIGN KEY(headerid) REFERENCES headers(header_id)
);