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