SQLite Forum

3.31.1: create table with multiple primary key columns
Login

3.31.1: create table with multiple primary key columns

(1) By Rich S (richs) on 2020-03-30 18:27:23 [link] [source]

I want to confirm I'm using correct syntax for a table with multiple columns as the primary key:

create table WeatherData (
    wxsite_id INT references WeatherSites(wxsite_id)
       on delete cascade on update cascade,
    sampdate TEXT,
    samptime TEXT,
    sampby TEXT,
    wxparam_name TEXT references WeaatherParams(wxparam_name)
       on delete cascade on update cascade,
    wxquant REAL,
    notes TEXT,
    constraint primary key (wxsite_id, sampdate, samptime, wxparam_name),
    without rowid
    );

Is this correct?

(2) By anonymous on 2020-03-30 18:30:37 in reply to 1 [link] [source]

Oops! The two columns, sampdate and samptime have been corrected to include the column constraint 'not null.'

(3) By Richard Hipp (drh) on 2020-03-30 19:06:24 in reply to 1 [link] [source]

Are you aware of the syntax diagrams in the documentation?

(4.1) By Keith Medcalf (kmedcalf) on 2020-03-30 19:25:33 edited from 4.0 in reply to 1 [link] [source]

create table WeatherData (
    wxsite_id INT not null references WeatherSites(wxsite_id)
       on delete cascade on update cascade,
    sampdate TEXT not null,
    samptime TEXT not null,
    sampby TEXT,
    wxparam_name TEXT not null references WeaatherParams(wxparam_name)
       on delete cascade on update cascade,
    wxquant REAL,
    notes TEXT,
    primary key (wxsite_id, sampdate, samptime, wxparam_name),
    ) without rowid;

Note that the columns in a primary key must be constrained NOT NULL for a "without rowid" table.

The keyword "constraint" is used to give a name to a constraint and so the construction "constraint primary key" is attempting to create a constraint named 'primary' with a next keyword (type) of key. There is not such type of constraint. The type of the constraint is 'primary key' so if you want to have that constraint named 'primary' then you must use the syntax constraint primary primary key (...).

The keywords WITHOUT ROWID to designate that a table does not have a rowid is a qualifier of the table, not of the contents in the table. As such it follows the ()'s delimiting the contents of the table.

If this is for collection of realtime data I would consider rearranging the order or your primary key since it is in the wrong order and will result in excessive continuous rebalancing of the index. The order should be from most stable to least stable (ie, in increasing order of change). That is, for each sampdate you will get multiple samptime and for each of those multiple wxsite_id and for each of those multiple wxparam_name.

If you put the wxsite_id first then each second when a new sample arrives you will have to rebalance the index to perform the insert because the inserts will not be in-order, and you will be wondering why there is (1) tremendous I/O occurring when you are only adding itty-bitty records (2) why sometimes it takes 1000 times longer to insert a record than it ususally does.

So to save time I will answer those next questions now.

If the table is to contain bulk-loaded data (that is, something else is recording the data feed and you are merely loading a bunch of data for analysis) then you should order your index to be most useful for how you will normally be accessing and querying the data since you will only be loading the data once, not continuously.

(5) By Rich S (richs) on 2020-03-30 19:57:27 in reply to 3 [link] [source]

Yes. I thought I had followed them. What did I miss?

(6) By Rich S (richs) on 2020-03-30 20:03:16 in reply to 4.1 [link] [source]

> Note that the columns in a primary key must be constrained NOT NULL for a "without rowid" table.

I assumed that since the exsite_id column must match a value in the WeatherSites(wxsite_id) column that 'not null' was implicit.

> The keyword "constraint" 

Oops! I forgot that. Thanks.

> The keywords WITHOUT ROWID to designate that a table does not have a rowid is a qualifier of the table, not of the contents in the table. As such it follows the ()'s delimiting the contents of the table.

I must have missed that when reading the syntax page. 

Data are entered by users, not automatically.

Thanks,

Rich

(7) By Keith Medcalf (kmedcalf) on 2020-03-30 21:13:46 in reply to 6 [link] [source]

A column x which is declared to be domain constrained to a value in a parent table column (ie, has a references table(column)) may contain a null value and pass the foreign key constraint even though the parent table does not contain a null in the referenced column, hence the on delete set null constraint which means that if the parent is deleted set the value to null, which means no parent row. Were this not the case then there would be no way to add a child that does not currently have a parent.

(8) By doug (doug9forester) on 2020-03-30 21:32:47 in reply to 7 [link] [source]

Au contraire, there is a way to add a child without a current parent: add DEFERRABLE INITIALLY DEFERRED to the column definition:

create table list (row_id INTEGER PRIMARY KEY, item TEXT UNIQUE,
     prev INTEGER REFERENCES list(row_id) DEFERRABLE INITIALLY DEFERRED);
...
begin transaction;
insert into list _child_;
insert into list _parent_;
commit transaction;

(9) By Rich S (richs) on 2020-03-30 21:53:32 in reply to 7 [link] [source]

I forgot that sqlite retains the capability of NULL primary keys. I'll add the primary key constraint to that table. Thanks, Keith.

(10) By Keith Medcalf (kmedcalf) on 2020-03-30 22:02:11 in reply to 8 [link] [source]

That only allows the deferral of the foreign key constraint check to commit time. It does not permit the addition of a child without a parent. To have an "orphan" child the referent is NULL.

eg:

create table parent(id integer primary key);
create table child(id integer primary key, parent references parent(id));
insert into parent default values;
insert into child values (2, 1);
insert into child values (3, null);
insert into child values (5, 2);
-- Error: FOREIGN KEY constraint failed
select * from parent;
-- 1
select * from child;
-- 2|1
-- 3|
or, for example:
create table parent(id integer primary key);
create table child(id integer primary key, parent references parent(id) on delete set null);
insert into parent default values;
insert into child values (2,1);
select * from parent;
-- 1
select * from child;
-- 2|1
delete from parent where id == 1;
select * from child;
-- 2|

(11) By Rich S (richs) on 2020-03-30 22:32:50 in reply to 10 [link] [source]

Keith,

The 'parent' is the Sites table. If a user tries to enter any data and the site_id field does not exist in the Sites table an error message box would be displayed.

No entered data can be deleted via the application. All data are needed for a regulatory compliance audit.

Because the value in the wxsite_id column in the WeatherData table must exist before data are committed how should I define that table?

Current definition:

create table WeatherData (
    wxsite_id INT not null
              references WeatherSites(wxsite_id)
              on delete cascade on update cascade,
    sampdate TEXT not null,
    samptime TEXT not null,
    sampby TEXT,
    wxparam_name TEXT references WeaatherParams(wxparam_name)
       on delete cascade on update cascade,
    wxquant REAL not null,
    notes TEXT,
    constraint primary key (wxsite_id, sampdate, samptime, wxparam_name)
    )without rowid;

(12) By Keith Medcalf (kmedcalf) on 2020-03-30 22:53:42 in reply to 11 [link] [source]

If data cannot be deleted then why have on delete cascade? This basically means that if a site is deleted then all the child records associated with that site are deleted. If you do not intend to delete data, then you should probably not use on delete cascade and probably want on delete restrict. You can also use triggers to do this. For example:

create trigger <name> before delete on <table>
begin
  select raise(ABORT, 'Cannot delete data');
end;

This means that even if there is an error in the application, it will not be able to delete records. (Mind you, someone can still remove the trigger in order to allow deletions, but that requires intent to delete rather than mere accident).

(13) By ddevienne on 2020-03-31 07:01:58 in reply to 8 [link] [source]

Not only, as Keyth already explained, this only postpones the FK check to
commit-time, instead of insert-time, DEFERRABLE INITIALLY DEFERRED is
SQLite default and only supported mode, unlike most databases.

(Well, putting aside the fact FKs are never active by default,
and one must explicitly turn them ON when opening a connection.)

(14) By David Raymond (dvdraymond) on 2020-03-31 13:11:15 in reply to 13 [source]

You can have either deferred or immediate. You don't have any commands to change that once the table's made though. https://www.sqlite.org/foreignkeys.html#fk_deferred

(15) By Rich S (richs) on 2020-03-31 15:29:34 in reply to 13 [link] [source]

Between validation checks on all tkinter widgets and SQLAlchemy controlling exchanges between database and views I'm not worried about any table having null primary key values for any row.

Thanks all.