CODEWITHSUNDEEP
sqlsqlite
magrif
magrif

Reputation: 480

How to make reference to sqlite_master's field?

It's possible make reference to field name of sqlite_master table? Something like this:

CREATE TABLE metadata (
    id          INTEGER PRIMARY KEY AUTOINCREMENT,
    name        TEXT    REFERENCES sqlite_master (name) ON DELETE CASCADE
                        UNIQUE
                        NOT NULL,
    description TEXT
);

When I'm trying to execute this script then SQLiteStudio return error message SQL logic error. I need create table with human-readable description of tables. I want make field name of service table sqlite_master as foreign key in my table.

Upvotes: 1

Views: 198

Answers (1)

Shawn
Shawn

Reputation: 52419

Here's what's happening:

A column used as a parent key needs to either be the parent table's primary key, or have an unique index/constraint on it. The sqlite_master table has no such thing for the name column (And you can't add an index on it), thus it can't be used. I'm not sure why you're getting a logic error on table creation instead of a foreign key mismatch error at time of insert like you do in the same situation on normal tables, though:

sqlite> pragma foreign_keys = on;
sqlite> create table parent(id integer primary key, name text not null);
sqlite> insert into parent(name) values ('Bob');
sqlite> create table child(id integer primary key, name text references parent(name));
sqlite> insert into child(name) values ('Bob');
Error: foreign key mismatch - "child" referencing "parent"
sqlite> create unique index parent_idx_name on parent(name);
sqlite> insert into child(name) values ('Bob');
sqlite>

If foreign key enforcement is disabled like it is by default, what you're trying will be accepted, it just doesn't do anything and will raise errors if you turn on FKs later and try to do stuff with the table.

Upvotes: 1

Related Questions