Re: [sqlite] SQLite 3 and FK
On 6 Dic, 14:43, Jay A. Kreibich j...@kreibi.ch wrote: On Mon, Dec 06, 2010 at 06:20:13PM +0600, Dagdamor scratched on the wall: zac Yes ...my error...i want translate name from italian I resolve error! Table models is auto generate from csv import file...and...not have a PK !!! i dont see this macro error! Correct SQL: Anagrafica (Owner) CREATE TABLE [Anagrafica] ( [Idscheda] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, [Nominativo] NVARCHAR(100) NOT NULL, [CF] NVARCHAR(16) NOT NULL, [Indirizzo] NVARCHAR(100) NOT NULL, [Citta] NVARCHAR(50) NOT NULL, [Prov] NCHAR(2), [CAP] NCHAR(5), [telefono] NVARCHAR(30), [fax] NVARCHAR(30), [email] NVARCHAR(40), [web] NVARCHAR(55), [datareg] TIMESTAMP NOT NULL DEFAULT ('datetime(''now'',''localtime'')'), [Mostra] BOOLEAN DEFAULT ('1')); Modelli(Models) CREATE TABLE Modelli ( [IDschedamodello] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, [Marca] NCHAR(25) NOT NULL, [Modello] NCHAR(45) NOT NULL); and. Vetture (Cars) CREATE TABLE [Vetture] ( [IDvettura] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, [IDmodello] INTEGER NOT NULL CONSTRAINT [FKmodello] REFERENCES [Modelli]([IDschedamodello]), [IDproprietario] INTEGER NOT NULL CONSTRAINT [FKproprietario] REFERENCES [Anagrafica]([Idscheda]), [DataImmatricolazione] DATE, [Targa] NVARCHAR(20) NOT NULL); Sorry! :) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite 3 and FK
(First of all sorry for my inglish) 3 tables : Owners Models Cars My difficult is recreate a kind of referential integrity through FK With 2 tables not problem but with 3 cascade table . Owner: PK IDowner Models: PK IDmodel Cars: CodOwner CodModel IDowner 1- many CodOwner IDmodel 1- many CodModel I write CREATE TABLE [Models] ( [IDcar] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, [CODmodel] INTEGER NOT NULL CONSTRAINT [FKmodel] REFERENCES [Models] ([idmodel]) ON DELETE NO ACTION, [CODowner] INTEGER NOT NULL CONSTRAINT [FKowner] REFERENCES [Owners] ([Idowner]) ON DELETE NO ACTION, [DataImmatricolazione] DATE, [Targa] NVARCHAR(20) NOT NULL); But not works!!! message: Abort due to constraint violation. if delete one fk (random) and ta dah!... no error but incomplete simulation of referential integrity with 3 linked tables! ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite 3 and FK
Wodka40[Google] martekpr...@gmail.com писал(а) в своём письме Mon, 06 Dec 2010 17:30:47 +0600: (First of all sorry for my inglish) 3 tables : Owners Models Cars My difficult is recreate a kind of referential integrity through FK With 2 tables not problem but with 3 cascade table . Owner: PK IDowner Models: PK IDmodel Cars: CodOwner CodModel IDowner 1- many CodOwner IDmodel 1- many CodModel I write CREATE TABLE [Models] ( [IDcar] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, [CODmodel] INTEGER NOT NULL CONSTRAINT [FKmodel] REFERENCES [Models] ([idmodel]) ON DELETE NO ACTION, [CODowner] INTEGER NOT NULL CONSTRAINT [FKowner] REFERENCES [Owners] ([Idowner]) ON DELETE NO ACTION, [DataImmatricolazione] DATE, [Targa] NVARCHAR(20) NOT NULL); But not works!!! message: Abort due to constraint violation. if delete one fk (random) and ta dah!... no error but incomplete simulation of referential integrity with 3 linked tables! CREATE TABLE [Models] ... REFERENCES [Models] ? Isn't is a recursive reference? Regards, Serge Serge Igitov http://www.phpc.ru Want to make it support SQLite! :P ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite 3 and FK
On Mon, Dec 06, 2010 at 06:20:13PM +0600, Dagdamor scratched on the wall: Wodka40[Google] martekpr...@gmail.com писал(а) в своём письме Mon, 06 Dec 2010 17:30:47 +0600: (First of all sorry for my inglish) 3 tables : Owners Models Cars My difficult is recreate a kind of referential integrity through FK With 2 tables not problem but with 3 cascade table . Owner: PK IDowner Models: PK IDmodel Cars: CodOwner CodModel IDowner 1- many CodOwner IDmodel 1- many CodModel I write CREATE TABLE [Models] ( Do you mean CREATE TABLE [Cars] ? [IDcar] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, [CODmodel] INTEGER NOT NULL CONSTRAINT [FKmodel] REFERENCES [Models] ([idmodel]) ON DELETE NO ACTION, [CODowner] INTEGER NOT NULL CONSTRAINT [FKowner] REFERENCES [Owners] ([Idowner]) ON DELETE NO ACTION, [DataImmatricolazione] DATE, [Targa] NVARCHAR(20) NOT NULL); But not works!!! message: Abort due to constraint violation. if delete one fk (random) and ta dah!... no error but incomplete simulation of referential integrity with 3 linked tables! CREATE TABLE [Models] ... REFERENCES [Models] ? Isn't is a recursive reference? Yes, but that's not the problem. Tables that reference themselves are perfectly normal. It is a very common way to build a tree-type structure, for example, where you might have a parent column that is a FK to the PK of itself. In this case, the table shown above is called Models, but the FK REFERENCES Models ( IDModel ), which does not exist. If the example was simply copied wrong, it would help to see the CREATE TABLE statements for all three tables, plus the SQL commands (and bindings) that are causing the errors. -j -- Jay A. Kreibich J A Y @ K R E I B I.C H Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable. -- Angela Johnson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users