MySQL(?) to SQLite

Question

I want to add this into SQLite, but I can't get alter table to work with add foreign key. It looks like this:

create table Medlem (Mnr integer not null, Namn varchar(6), Telefon varchar(10), primary key (Mnr));

create table Sektion
(Skod char(1) not null,
Namn varchar(14),
Ledare integer,
primary key (Skod));

create table Deltar
(Medlem integer not null,
Sektion char(1) not null,
primary key (Medlem, Sektion));

alter table Sektion
add foreign key (Ledare) references Medlem (Mnr);
alter table Deltar
add foreign key (Medlem) references Medlem (Mnr);
alter table Deltar
add foreign key (Sektion) references Sektion (Skod);

Do you have foreign key support enabled? (see http://www.sqlite.org/foreignkeys.html) — Raad, Mar 13 '13 at 17:11
@Raad *A `CREATE TABLE` command operates the same whether or not foreign key constraints are enabled* — Kermit, Mar 13 '13 at 17:13

Kermit · Accepted Answer · 2013-03-13T17:17:19.620

2

The only way to implement a foreign key constraint in SQL Lite is during CREATE TABLE:

CREATE TABLE track(
  trackid     INTEGER, 
  trackname   TEXT, 
  trackartist INTEGER,
  FOREIGN KEY(trackartist) REFERENCES artist(artistid)
);

It is not possible to use the ALTER TABLE ... ADD COLUMN syntax to add a column that includes a REFERENCES clause, unless the default value of the new column is NULL. Attempting to do so returns an error.

Documentation

edited Mar 13 '13 at 17:17

answered Mar 13 '13 at 17:11

Kermit

33,827
13
85
121

Thank you, I think I got it now. – newbie4life Mar 13 '13 at 17:17

MySQL(?) to SQLite

1 Answers1