perl DBI::Sqlite et foreign_keys
Bonjour,
J'ai un soucis avec un script Perl pour activer les clefs étrangères.
Le script s'exécute sans erreur. Normalement il doit s'arreter car erreur de contrainte clef etrangère.
Mais, lorsque je lance les deux tests (voir le fichier perl) sur une console sqlite, j'obtient bien les deux erreurs
Je suis sous windows XP
activePerl : 5.8.0
SQlite : 3.7.3
Je ne sais pas comment débugguer ce problème
Merci d'avance
Code:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
|
#!perl -w
use strict;
use DBI;
my $dbfile="test.db";
my $dsn = "dbi:SQLite:dbname=$dbfile";
my $username = "";
my $password = "";
my %attr = ( RaiseError => 1, AutoCommit => 0 );
my $dbh = DBI->connect($dsn, $username, $password,\%attr ) ;
# FK
$dbh->do("pragma foreign_keys=ON;");
#artist
$dbh->do("DROP TABLE IF EXISTS artist");
$dbh->do("DROP TABLE IF EXISTS track");
$dbh->do("CREATE TABLE artist(
artistid INTEGER PRIMARY KEY,
artistname TEXT
)");
$dbh->do("INSERT INTO artist VALUES(1, 'Dean Martin')");
$dbh->do("INSERT INTO artist VALUES(2, 'Frank Sinatra')");
#track
$dbh->do("CREATE TABLE track(
trackid INTEGER PRIMARY KEY,
trackname TEXT,
artist INTEGER NOT NULL,
FOREIGN KEY(artist) REFERENCES artist(artistid)
)");
$dbh->do("INSERT INTO track VALUES(1, 'That''s Amore', 1 )");
$dbh->do("INSERT INTO track VALUES(2, 'Christmas Blues', 1 )");
$dbh->do("INSERT INTO track VALUES(3, 'My Way', 2 )");
# TEST 1
$dbh->do("INSERT INTO track VALUES(14, 'Mr. Bojangles', 3)");
$dbh->do("DELETE FROM artist WHERE artistid=2");
$dbh->commit;
$dbh->disconnect; |
Code:
1
2
3
4
5
6
|
sqlite> pragma foreign_keys=ON;
sqlite> INSERT INTO track VALUES(14, 'Mr. Bojangles', 3);
Error: foreign key constraint failed
sqlite> DELETE FROM artist WHERE artistid=2;
Error: foreign key constraint failed |
