I made some changes. You do not need "unique" and "primary key" on the same
variable. You still need a rowid. I removed the unique constraint otherwise
updating to duplicates does not work, and then you cannot see that it works..
CREATE TABLE Programmes
(
ID TEXT,
Title NVARCHAR(100) NOT NULL,
Description NVARCHAR(1000),
Year NVARCHAR(10),
EpNo1 NVARCHAR(50),
EpNo2 NVARCHAR(100)
);
create trigger ins_Programmes after insert on Programmes for each row
begin
update Programmes
set id = sha256(new.Title, new.Year, new.EpNo2)
where rowid = new.rowid;
end;
create trigger upd_Programmes after update of Title, Year, EpNo2 on Programmes
for each row
begin
update Programmes
set id = sha256(new.Title, new.Year, new.EpNo2)
where rowid = new.rowid;
end;
sqlite> CREATE TABLE Programmes
...> (
...> ID TEXT,
...> Title NVARCHAR(100) NOT NULL,
...> Description NVARCHAR(1000),
...> Year NVARCHAR(10),
...> EpNo1 NVARCHAR(50),
...> EpNo2 NVARCHAR(100)
...> );
sqlite> create trigger ins_Programmes after insert on Programmes for each row
...> begin
...> update Programmes
...> set id = sha256(new.Title, new.Year, new.EpNo2)
...> where rowid = new.rowid;
...> end;
sqlite>
sqlite> create trigger upd_Programmes after update of Title, Year, EpNo2 on
Programmes for each row
...> begin
...> update Programmes
...> set id = sha256(new.Title, new.Year, new.EpNo2)
...> where rowid = new.rowid;
...> end;
sqlite>
sqlite> insert into programmes (Title, Year, EpNo2) values ('Hello', 2001, 5);
sqlite> insert into programmes (Title, Year, EpNo2) values ('Hello', 2002, 5);
sqlite> insert into programmes (Title, Year, EpNo2) values ('Hello', 2002, 12);
sqlite> insert into programmes (Title, Year, EpNo2) values ('GoodBye', 2001, 5);
sqlite> insert into programmes (Title, Year, EpNo2) values ('GoodBye', 2002, 5);
sqlite> insert into programmes (Title, Year, EpNo2) values ('GoodBye', 2002,
12);
sqlite> select * from programmes;
ACA689A2343B462824B3CC682DDD897F78CACB9346B7E4C08AE301E5473C7CFF|Hello||2001||5
23CA3CD8AE54E5FE877544BC295B29B6E44903D1E6940D70F2821451093C162F|Hello||2002||5
E9FC08FA567B6DA212058E8CD0DFC4D78CA33890DDBE0428CA87E8999ABDC573|Hello||2002||12
E7306B3FF4FACE2CC0AE1421918E7BECC5D3C03279F52A68FF6979AE5E426AAE|GoodBye||2001||5
397DFE956F193D110224A988359F757384DC466CE59CCE81AF6A565D0A969451|GoodBye||2002||5
47C90EEF599DD1CB28347ABB261A348455C11746D5A33B9BD50E29E5774C549E|GoodBye||2002||12
sqlite> update programmes set title='GoodBye' where title='Hello';
sqlite> select * from programmes;
E7306B3FF4FACE2CC0AE1421918E7BECC5D3C03279F52A68FF6979AE5E426AAE|GoodBye||2001||5
397DFE956F193D110224A988359F757384DC466CE59CCE81AF6A565D0A969451|GoodBye||2002||5
47C90EEF599DD1CB28347ABB261A348455C11746D5A33B9BD50E29E5774C549E|GoodBye||2002||12
E7306B3FF4FACE2CC0AE1421918E7BECC5D3C03279F52A68FF6979AE5E426AAE|GoodBye||2001||5
397DFE956F193D110224A988359F757384DC466CE59CCE81AF6A565D0A969451|GoodBye||2002||5
47C90EEF599DD1CB28347ABB261A348455C11746D5A33B9BD50E29E5774C549E|GoodBye||2002||12
sqlite>
> -----Original Message-----
> From: [email protected] [mailto:sqlite-users-
> [email protected]] On Behalf Of Jörgen Hägglund
> Sent: Monday, 20 June, 2016 18:47
> To: [email protected]
> Subject: [sqlite] Unable to create table, default value of column [ID] is
> not constant
>
> Alright, trying to set the scenario:
> * I open/create a database
> * I register a custom function called HASH (set to be deterministic),
> tested and working
> * I create a bunch of tables (if not exists)
> So far, everything works fine.
>
> Then, the troublesome create:
> CREATE TABLE IF NOT EXISTS Programmes (
> ID NVARCHAR(64) UNIQUE NOT NULL PRIMARY KEY DEFAULT (HASH(Title,
> Year, EpNo2)),
> Title NVARCHAR(100) NOT NULL,
> Description NVARCHAR(1000),
> Year NVARCHAR(10),
> EpNo1 NVARCHAR(50),
> EpNo2 NVARCHAR(100))
>
> This query raises the following exception:
> Unable to create table default value of column [ID] is not constant
>
> Is it not possible to use custom functions this way?
> I tried with random() on another table and that works.
>
> Any ideas and explanations would be appreciated.
> /Jörgen
>
> _______________________________________________
> sqlite-users mailing list
> [email protected]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
