3

I am working on a small sqlite3 database for a small simple mailserver. I have created a new sqlite3 database and add executed this to pürepare it for data:

BEGIN TRANSACTION;
CREATE TABLE `mailboxes` (
    `username`  TEXT NOT NULL,
    `domain`    TEXT NOT NULL,
    `mailbox`   TEXT NOT NULL,
    FOREIGN KEY(`domain`) REFERENCES domains(domain)
    UNIQUE (username, domain),
    UNIQUE (mailbox)
);
CREATE TABLE `domains` (
    `domain`    TEXT NOT NULL UNIQUE
);
CREATE TABLE `aliases` (
    `aliasname` TEXT NOT NULL,
    `domain`    TEXT NOT NULL,
    `destination`   TEXT NOT NULL,
    FOREIGN KEY(`domain`) REFERENCES domains(domain)
    UNIQUE (aliasname, domain)
);
COMMIT;

Foreign keys are enabled with PRAGMA and no errors are shown. As you can see, mailboxes and aliases are nearly the same. I have a list of available domains. Every mailbox/alias entry must use a valid entry of "domains". In addition to this, a "username - domain" pair should only appear once. BUT: the same mail can appear in mailboxes and aliases.

PROBLEM

I have no entries in my domains table, but executing this works (but it should not, because it is not a valid domain):

INSERT INTO aliases (aliasname, domain, destination)
VALUES ('test', 'myacc.de', 'other@worldwide.com');

Where is my fault?

  • Works for me (Error: FOREIGN KEY constraint failed). – CL. Nov 13 '15 at 13:12
2

I was partially able to reproduce your bug when having PRAGMA foreign_keys = ON;, but setting the domain column in the domains table as PRIMARY KEY removed any problem in my case. (Still keep the pragma on).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy