SQL syntax error: two different platforms report different error

Question

I am trying to make two database table and linked them together. Here is my code:

CREATE TABLE `NYSE_daily_prices_A` (
`StockSymbol` varchar(10)  NOT NULL ,
`StockName` varchar(100)  NOT NULL ,
`StockExchange` varchar(10)  NOT NULL ,
PRIMARY KEY (
    `StockSymbol`
)
);

CREATE TABLE `NYSE_stock_names` (
    `StockExchange` varchar(10)  NOT NULL ,
    `StockSymbol` varchar(10)  NOT NULL ,
    `date` varchar(10)  NOT NULL ,
    `StockPriceOpen` money  NOT NULL ,
    `StockPriceHigh` money  NOT NULL ,
    `StockPriceLow` money  NOT NULL ,
    `StockPriceClose` money  NOT NULL ,
    `StockVolume` int  NOT NULL ,
    `StockPriceAdjClose` money  NOT NULL 
);

ALTER TABLE `NYSE_stock_names` ADD 
CONSTRAINT `fk_NYSE_stock_names_StockSymbol` 
FOREIGN KEY(`StockSymbol`)
REFERENCES `NYSE_daily_prices_A` (`StockSymbol`);

I tried to run this script in SQLiteStudio, it reports an error as following:

However, i copied the script into SQL Fiddle and it reports as following:

It really confuses me. How to fix this issue? Thanks in advance.

Not every SQL implementation is the same. MSSQL, MySQL, SQLite, etc. all have different syntax, field types, methods, etc. Some stuff is common between these, but some stuff isn't. — gen_Eric, Apr 16 '21 at 18:05
MySQL doesn't have a `money` field type (I don't SQLite does either), and SQLite doesn't use the keyword `CONSTRAINT` when adding foreign keys. So, you can't really write SQL for one database type and expect it to work in another. — gen_Eric, Apr 16 '21 at 18:07
SQLite does not have a `money` field type either. I think it's just reading `money` as an invalid field type and it's probably just giving you a `text` field. See: https://www.sqlite.org/faq.html#q3 — gen_Eric, Apr 16 '21 at 18:10

score 1 · Accepted Answer · answered Apr 16 '21 at 18:50

From SQLite's ALTER TABLE:

The ALTER TABLE command in SQLite allows these alterations of an existing table: it can be renamed; a column can be added to it; or a column can be dropped from it.

This means that with SQLite you can't add a foreign key constraint to an existing table.
Also, there are no varchar or money data types (they are interpreted as TEXT and NUMERIC affinities respectively), but this is not the source of the error.

In Mysql there is no money data type.
You can read this thread for an alternative: Best data type to store money values in MySQL

Your code, if you remove the backticks, is valid in SQL Server and Postgresql but not in MySql and SQLite.

SQL syntax error: two different platforms report different error

1 Answers1