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).

| improve this answer | |

Your Answer

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