System.Data.SQLite

Hi,

I have created some solutions in the past using MS Access and VS2005 (C#) to access the database.

I intend to use SQLite from now on, and i have a (perhaps silly) question.

 

How do i specify in an SQLIte database, when 2 tables are related,

with for example a one-to-many relationship;

 

In MS Access, i specify this relationship with a foreign key that i create and then by

connecting in a graphical manner the primary and the foreign key.

How can this be done in SQLite;

I know i can live whithout that... but i am curious.

 

Thanks in advanced.

SQLite doesn't support forigen keys but joins are supported meaning that you have to enforce the relationship yourself in your code.
the provider comes with a Query Designer that can be integrated with Visual Studios.
but if you don't use those, you could use any 3rd party SQLite administrator such as SQLite2007 Pro Enterprise Manager which is FREE and includes a query designer (btw that's not an affilated link, it's just the download page).
While SQLite doesn't enforce anything (not even datatype), you could define your forgien keys and programs such the above would enforce it for you.

SQLite supports the definition of foreign key constraints, but does not enforce them currently.  There are plans for enforcement in SQLite's near future.

This .NET provider will parse and provide enough schema for foreign keys so that drag-n-dropped tables onto a typed dataset will automatically link up -- basically they behave as they should for the sake of the designer.

An example of a foreign key declaration (taken from an adaptation of Microsoft's northwind.db to SQLite):

CREATE TABLE [Employees](
        [EmployeeID] integer primary key autoincrement,
        [LastName] nvarchar(20) NOT NULL,
        [FirstName] nvarchar(10) NOT NULL,
        [Title] nvarchar(30) NULL,
        [TitleOfCourtesy] nvarchar(25) NULL,
        [BirthDate] [datetime] NULL,
        [HireDate] [datetime] NULL,
        [Address] nvarchar(60) NULL,
        [City] nvarchar(15) NULL,
        [Region] nvarchar(15) NULL,
        [PostalCode] nvarchar(10) NULL,
        [Country] nvarchar(15) NULL,
        [HomePhone] nvarchar(24) NULL,
        [Extension] nvarchar(4) NULL,
        [Photo] [image] NULL,
        [Notes] ntext NULL,
        [ReportsTo] integer NULL,
        [PhotoPath] nvarchar(255) NULL,
    constraint fk_Employees_Employees foreign key(ReportsTo) references Employees(EmployeeID)
);

CREATE TABLE [Orders](
        [OrderID] integer primary key autoincrement,
        [CustomerID] nchar(5) NULL,
        [EmployeeID] int NULL,
        [OrderDate] [datetime] NULL,
        [RequiredDate] [datetime] NULL,
        [ShippedDate] [datetime] NULL,
        [ShipVia] int NULL,
        [Freight] money NULL DEFAULT (0),
        [ShipName] nvarchar(40) NULL,
        [ShipAddress] nvarchar(60) NULL,
        [ShipCity] nvarchar(15) NULL,
        [ShipRegion] nvarchar(15) NULL,
        [ShipPostalCode] nvarchar(10) NULL,
        [ShipCountry] nvarchar(15) NULL,
    constraint fk_Orders_Customers foreign key (CustomerID) references Customers (CustomerID),
    constraint fk_Orders_Employees foreign key (EmployeeID) references Employees (EmployeeID),
    constraint fk_Orders_Shippers foreign key (ShipVia) references Shippers (ShipperID)
);

 

Thanks a lot.

I will defenately go for SQLite in my next project!!!