On Friday 05 September 2008 22:43:15 Nicolas Williams wrote:
> On Fri, Sep 05, 2008 at 09:31:43PM +0200, Andreas Ntaflos wrote:
> > In the Room table the primary key is a combination of two attributes. How
> > could I auto-increment the roomID? "roomID INTEGER AUTOINCREMENT" is
> > syntactically incorrect and the AUTOINCREMENT keyword seems to cannot
> > appear anywhere else.
> >
> > What is the SQLite-way to auto-increment when the AUTOINCREMENT keyword
> > is not available?
>
> I think you should make the roomID the primary key, and autoincrement,
> then create a UNIQUE INDEX for the Room table on the roomID and
> buildingID columns.
>
> Finally, you already know you need to use triggers to enforce the
> foreign key constraint.
>
> What does it matter, then, that you can't declare those two columns of
> the Room table as PRIMARY KEY and get autoincrement for roomID? You can
> get autoincrement behavior and primary key behavior as described above.
Thank you, too, for your reply!
Does creating a UNIQUE INDEX really emulate the behaviour of the following to
(Postgre)SQL statements which are not supported in SQLite directly?
CREATE TABLE Building (
building_id SERIAL8 PRIMARY KEY
);
CREATE TABLE Room (
building_id BIGINT,
room_number SERIAL8,
FOREIGN KEY (building_id) REFERENCES Building(building_id) ON DELETE
CASCADE,
PRIMARY KEY (building_id, room_number)
);
Would this look like this in SQLite:
CREATE TABLE Building (
building_id INTEGER PRIMARY KEY AUTOINCREMENT
);
CREATE TABLE Room (
room_number INTEGER PRIMARY KEY AUTOINCREMENT,
building_id INTEGER CONSTRAINT fk_room_building_id REFERENCES
Building(building_id) ON DELETE CASCADE
);
CREATE UNIQUE INDEX idx_building_room ON TABLE Room(room_number, building_id);
That is exactly what I'll need to use, then. Is there anything else I need to
take care of?
Thank you very much!
Andreas
--
Andreas Ntaflos
Vienna, Austria
GPG Fingerprint: 6234 2E8E 5C81 C6CB E5EC 7E65 397C E2A8 090C A9B4
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
