Hi, there has to be some other problem. Now I see - I intended to update the table, so not providing default columns was not an issue. But somehow the original data in the database changed and some of the rows that I wanted to update were deleted. So now the merge tried inserting those rows and of course that fails when SHEET value is not given because of the NOT NULL constraint.
My bad, I did not check that. I have to inspect what happened to the original rows that are missing from the table and prune the merge dataset. Thanks for the quick answer anyways. Cheers, David On Friday, December 19, 2014 9:12:30 PM UTC+1, Rami Ojares wrote: > > Merge does either an insert or update. > If it does an insert it must always insert a complete row. > If the user does not provide a value for some column then dbms uses the > default value for that column. > If the user did not explicitly define a default value for the column then > the default value is NULL. > Ergo the dbms tries to insert a row where column sheet has value NULL but > NULL is not allowed for that column. > Does this make sense? > > - rami > > > On 19.12.2014 21:04, David Hájek wrote: > > Hi, > > I was trying to fix some screwed up data by running this by hand: > > MERGE INTO SheetOrders (id, createdAt, createdBy) KEY(id) VALUES > (154, '2014-11-20 08:36:07.209', 4), > (164, '2014-11-24 10:25:06.395', 3), > (165, '2014-11-24 10:25:46.987', 3), > (171, '2014-11-27 10:35:09.764', 3), > (172, '2014-11-27 10:35:41.147', 3), > (173, '2014-11-27 11:58:11.06', 3), > (174, '2014-11-27 12:00:32.21', 3), > (176, '2014-12-01 14:58:41.081', 3), > (178, '2014-12-03 09:48:16.415', 3), > (179, '2014-12-03 10:07:49.815', 3), > (181, '2014-12-05 09:36:35.754', 3), > (182, '2014-12-08 08:47:11.349', 3), > (183, '2014-12-09 12:48:11.538', 3), > (184, '2014-12-09 14:16:26.757', 3), > (185, '2014-12-11 14:43:40.76', 3), > (186, '2014-12-11 16:23:17.315', 3), > (187, '2014-12-12 09:02:29.67', 3), > (188, '2014-12-12 12:10:01.006', 3); > > > On a table defined as: > > CREATE TABLE IF NOT EXISTS SheetOrders ( > id IDENTITY PRIMARY KEY, > printOrder BIGINT NULL, > product BIGINT NULL, > sheet BIGINT NOT NULL, > amount INT NOT NULL, > note VARCHAR(255), > status INT NOT NULL, > createdAt TIMESTAMP NOT NULL, > createdBy BIGINT NOT NULL, > FOREIGN KEY (printOrder) REFERENCES PrintOrders(id), > FOREIGN KEY (product) REFERENCES Products(id), > FOREIGN KEY (sheet) REFERENCES Sheets(id), > FOREIGN KEY (createdBy) REFERENCES Users(id) > ); > > > What I get is: > > NULL not allowed for column "SHEET"; SQL statement: > MERGE INTO SheetOrders (id, createdAt, createdBy) KEY(id) VALUES > (154, '2014-11-20 08:36:07.209', 4), > (164, '2014-11-24 10:25:06.395', 3), > (165, '2014-11-24 10:25:46.987', 3), > ... > > > I don't understand this behavior. I updated several other tables in the > same manner - all had NOT NULL and FOREIGN KEY columns and none gave me any > errors. I would suppose that when I don't specify the NOT NULL column as > NULL explicitly in the merge, the DB should not even care about that column > in a MERGE that does not mention that column. What is happening? How do I > get around this? > > I also tried to set the columns NULL with ALTER before the MERGE (then > the MERGE worked fine) and then back to NOT NULL which gave me this error > afterwards: > > Column "SHEET" contains null values; SQL statement: > ALTER TABLE SheetOrders ALTER COLUMN sheet SET NOT NULL [90081-175] > [ERROR:90081, SQLSTATE:90081] > > > But when I queried the table for NULL values like such: > > SELECT * FROM SheetOrders WHERE sheet = NULL; > > > I got 0 rows back, which means there are no NULL values in that column. > So what's the deal? Is the DB corrupt? Is this a bug? Undocumented feature? > Why can't I MERGE into the table and set the column to NOT NULL? > > Regards, > David > -- > You received this message because you are subscribed to the Google Groups > "H2 Database" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to h2-database...@googlegroups.com <javascript:>. > To post to this group, send email to h2-da...@googlegroups.com > <javascript:>. > Visit this group at http://groups.google.com/group/h2-database. > For more options, visit https://groups.google.com/d/optout. > > > -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
