U++ forum: Welcome to the forum

SQLite and foreign keys [message #40977]

Thu, 17 October 2013 02:28

wimpie
Messages: 46
Registered: March 2013
Location: holland

Member

Hello,

I am using SQLite, with a Schema with a few tables with references and I noticed I could delete rows in one table which were used in another.
After some digging I noticed in Sqlite3Schema.h the REFERENCES*** macro's are not defined.

First I wondered why, but after reading Sqlite doc's I think it's because it doesn't allow adding constraints to an existing table. Am I right?

But, it does allow it when creating the column, so I changed this in Sqlite3Schema.h:

#define REFERENCES(x)              INLINE_ATTRIBUTE("references " #x)

And as far as I can see, this works. I think it can also work with CASCADE ("references " #x " on delete cascade") but I did not test that.

But it seems not to be the perfect solution because I can't reference TABLE.COLUMN; Sqlite gives error on the ".". it needs TABLE(COLUMN) to work.
This can be put in the schema also like

TABLE (TEST)
	INT (ID) PRIMARY_KEY
	INT (KEY1) REFERENCES(SIMPLE_TEST1(ID))
END_TABLE

but I guess this is not portable with other SQL engines?

Can someone please comment on this?

Report message to a moderator