Thanks very much Thomas! 2015-03-09 18:14 GMT+01:00 Thomas Mueller <thomas.tom.muel...@gmail.com>:
> Hi, > > The "belongs_to_table" is not part of the official API. It is used > internally mark sequences as belonging to tables. The issue is, in your > case, that you forgot(?) to link the table to the sequence. It should be: > > create table test(id identity); > script drop; > > CREATE SEQUENCE > PUBLIC.SYSTEM_SEQUENCE_6EB77E7B_2EFE_44D5_BC3C_2254953D5702 START WITH 1 > BELONGS_TO_TABLE; > CREATE CACHED TABLE PUBLIC.TEST( > ID BIGINT DEFAULT (NEXT VALUE FOR > PUBLIC.SYSTEM_SEQUENCE_6EB77E7B_2EFE_44D5_BC3C_2254953D5702) NOT NULL > NULL_TO_DEFAULT SEQUENCE > PUBLIC.SYSTEM_SEQUENCE_6EB77E7B_2EFE_44D5_BC3C_2254953D5702 > ); > ALTER TABLE PUBLIC.TEST ADD CONSTRAINT PUBLIC.CONSTRAINT_2 PRIMARY KEY(ID); > > simplified: > > create sequence seq start with 1 belongs_to_table; > create table test( > id bigint default (next value for seq) not null null_to_default > sequence seq > ); > > Notice there is a "sequence seq" after the "null_to_default". This is to > drop the sequence if the table is dropped. > > Regards, > Thomas > > > > On Thursday, January 22, 2015, Daniele Renda <daniele.re...@gmail.com> > wrote: > >> Hi, >> run this sql that create a table and a sequence: >> >> CREATE SEQUENCE PUBLIC.SEQUENCE_TEST START WITH 1 BELONGS_TO_TABLE; >> CREATE TABLE PUBLIC.TABLETEST ( >> ID BIGINT NOT NULL DEFAULT (NEXT VALUE FOR PUBLIC.SEQUENCE_TEST), >> VALUE VARCHAR_IGNORECASE(255), >> CONSTRAINT CONSTRAINT_12 PRIMARY KEY (ID) >> ); >> >> CREATE INDEX PRIMARY_KEY_1 ON PUBLIC.TABLETEST (ID); >> >> then run: >> >> script drop TO 'C:\test.sql'; >> >> this is the result: >> >> CREATE USER IF NOT EXISTS ROOT SALT 'f9863838dc26f749' HASH >> 'e64727b2982593291bc7b74bef5e55150cb0b49237286b61a04444725df22934' ADMIN; >> DROP TABLE IF EXISTS PUBLIC.TABLETEST CASCADE; >> CREATE SEQUENCE PUBLIC.SEQUENCE_TEST START WITH 1 BELONGS_TO_TABLE; >> CREATE CACHED TABLE PUBLIC.TABLETEST( >> ID BIGINT DEFAULT (NEXT VALUE FOR PUBLIC.SEQUENCE_TEST) NOT NULL, >> VALUE VARCHAR_IGNORECASE(255) >> ); >> ALTER TABLE PUBLIC.TABLETEST ADD CONSTRAINT PUBLIC.CONSTRAINT_12 PRIMARY >> KEY(ID); >> -- 0 +/- SELECT COUNT(*) FROM PUBLIC.TABLETEST; >> CREATE INDEX PUBLIC.PRIMARY_KEY_1 ON PUBLIC.TABLETEST(ID); >> >> And if you try to run: >> >> RUNSCRIPT FROM 'c:\test.sql' >> where test.sql contain the export above, you have: >> >> Errore SQL [90035] [90035]: Sequence "SEQUENCE_TEST" already exists; SQL >> statement: >> >> CREATE SEQUENCE PUBLIC.SEQUENCE_TEST START WITH 1 BELONGS_TO_TABLE [90035 >> -185] >> Sequence "SEQUENCE_TEST" already exists; SQL statement: >> >> CREATE SEQUENCE PUBLIC.SEQUENCE_TEST START WITH 1 BELONGS_TO_TABLE [90035 >> -185] >> >> >> According to documentation, SCRIPT DROP should >> >>> drop statements are created for tables, views, and sequences. >>> >> >> I'm doing something wrong? >> >> Thanks >> >> >> Il giorno giovedì 22 gennaio 2015 21:36:08 UTC+1, Thomas Mueller ha >> scritto: >>> >>> Hi, >>> >>> Could you please post a simple, reproducible test case? >>> >>> Regards, >>> Thomas >>> >>> >>> >>> On Sunday, January 18, 2015, Daniele Renda <daniel...@gmail.com> wrote: >>> >>>> According to documentation (http://www.h2database.com/ >>>> html/grammar.html#script) I'm using SCRIPT DROP TO 'filename' table >>>> tablename to create a sql script of a my table. >>>> >>>> Unfortunally seems that are not created drop statements for sequences. >>>> So when I "import" (runscript) the file into my new db, every time I've an >>>> exception because sequences are yet present. >>>> >>>> This is an example of the script created: >>>> >>>> SET IGNORECASE 1; >>>> ; >>>> CREATE USER IF NOT EXISTS ROOT SALT '55ef3fd3f0ef42ee' HASH ' >>>> 59892215285403b1554d630a5b8f68e6542a460a888c4206af9a63ed24ca164f' ADMIN >>>> ; >>>> DROP TABLE IF EXISTS PUBLIC.LOG CASCADE; >>>> CREATE SEQUENCE PUBLIC.SYSTEM_SEQUENCE_6BA8FFE2_3396_4DD3_A6EB_36A1F7F47767 >>>> START WITH 5 BELONGS_TO_TABLE; >>>> CREATE SEQUENCE PUBLIC.SYSTEM_SEQUENCE_72954992_67B5_491A_875F_6C4737B83ADE >>>> START WITH 7975 BELONGS_TO_TABLE; >>>> CREATE SEQUENCE PUBLIC.SYSTEM_SEQUENCE_A1DBB0E4_B508_45A8_980A_A577D231F9D2 >>>> START WITH 4 BELONGS_TO_TABLE; >>>> CREATE SEQUENCE PUBLIC.SYSTEM_SEQUENCE_6CC0B361_C9D3_42D0_82A7_52A545C47368 >>>> START WITH 90 BELONGS_TO_TABLE; >>>> CREATE SEQUENCE PUBLIC.SYSTEM_SEQUENCE_0F97CCE6_70FA_49FD_9FDE_DF59A5A7713C >>>> START WITH 5 BELONGS_TO_TABLE; >>>> CREATE SEQUENCE PUBLIC.SYSTEM_SEQUENCE_D835E49E_0E1D_41D6_B380_8A48971BAC60 >>>> START WITH 138 BELONGS_TO_TABLE; >>>> CREATE SEQUENCE PUBLIC.SYSTEM_SEQUENCE_006051CC_7153_4755_B38A_CB04A7D92A19 >>>> START WITH 2 BELONGS_TO_TABLE; >>>> CREATE SEQUENCE PUBLIC.SYSTEM_SEQUENCE_5A311BF2_C994_4068_9CC3_157648534C78 >>>> START WITH 25 BELONGS_TO_TABLE; >>>> CREATE SEQUENCE PUBLIC.SYSTEM_SEQUENCE_EECEB186_0B4C_46A2_8C9E_E74C0B60F1F6 >>>> START WITH 9 BELONGS_TO_TABLE; >>>> CREATE SEQUENCE PUBLIC.SYSTEM_SEQUENCE_515473C9_A6D0_431A_AD19_75140945C75D >>>> START WITH 34911 BELONGS_TO_TABLE; >>>> CREATE SEQUENCE PUBLIC.SYSTEM_SEQUENCE_1C646FFA_2FB7_4453_9CD0_A70D79604967 >>>> START WITH 21297 BELONGS_TO_TABLE; >>>> CREATE SEQUENCE PUBLIC.SYSTEM_SEQUENCE_D9D36025_EF3E_414B_A96F_D02F89A4303C >>>> START WITH 1 BELONGS_TO_TABLE; >>>> CREATE SEQUENCE PUBLIC.SYSTEM_SEQUENCE_7A28EE56_2D21_4A2B_B7B9_2CCB1D7C5BEA >>>> START WITH 10702 BELONGS_TO_TABLE; >>>> CREATE SEQUENCE PUBLIC.SYSTEM_SEQUENCE_18EA9F51_8F73_44C2_B27D_BECE151EDAB9 >>>> START WITH 3 BELONGS_TO_TABLE; >>>> CREATE SEQUENCE PUBLIC.SYSTEM_SEQUENCE_20C4C40D_0232_472C_8311_8DD7B158C4CD >>>> START WITH 9 BELONGS_TO_TABLE; >>>> CREATE SEQUENCE PUBLIC.SYSTEM_SEQUENCE_416610E1_BB14_47CA_9F19_1AE49CF7243E >>>> START WITH 33 BELONGS_TO_TABLE; >>>> CREATE SEQUENCE PUBLIC.SYSTEM_SEQUENCE_BCC067DD_2BD9_4010_B3F6_5771D452474F >>>> START WITH 2484 BELONGS_TO_TABLE; >>>> CREATE SEQUENCE PUBLIC.SYSTEM_SEQUENCE_2D09FDD3_60EA_4A67_9F15_1086F48B9EEF >>>> START WITH 23 BELONGS_TO_TABLE; >>>> CREATE SEQUENCE PUBLIC.SYSTEM_SEQUENCE_F57E7066_00A3_4A7E_8E6F_164F679311E1 >>>> START WITH 78 BELONGS_TO_TABLE; >>>> CREATE SEQUENCE PUBLIC.SYSTEM_SEQUENCE_42FF8CB2_19FA_4803_A0C6_E025BE308B1E >>>> START WITH 38743 BELONGS_TO_TABLE; >>>> CREATE SEQUENCE PUBLIC.SYSTEM_SEQUENCE_91AA317A_DC58_48FD_BBBC_A3153CC30228 >>>> START WITH 10702 BELONGS_TO_TABLE; >>>> CREATE SEQUENCE PUBLIC.SYSTEM_SEQUENCE_88EFD6C1_26AB_4C2F_823C_7E22BD370DE9 >>>> START WITH 39 BELONGS_TO_TABLE; >>>> CREATE SEQUENCE PUBLIC.SYSTEM_SEQUENCE_0A7157DD_0EAD_44F9_B87A_0BCAE594EA27 >>>> START WITH 49 BELONGS_TO_TABLE; >>>> CREATE SEQUENCE PUBLIC.SYSTEM_SEQUENCE_96D36B0E_C11C_4A8B_8BC2_9B4CF5478E6E >>>> START WITH 37 BELONGS_TO_TABLE; >>>> CREATE SEQUENCE PUBLIC.SYSTEM_SEQUENCE_0343F19C_9958_44A8_83A4_6D0AFF6F311C >>>> START WITH 8 BELONGS_TO_TABLE; >>>> CREATE SEQUENCE PUBLIC.SYSTEM_SEQUENCE_E52B3F29_8C4E_46A6_9387_8B274EE02B7E >>>> START WITH 26 BELONGS_TO_TABLE; >>>> CREATE CACHED TABLE PUBLIC.LOG( >>>> >>>> Is this a bug or I'm doing something wrong? >>>> >>>> Thanks >>>> >>>> -- >>>> 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. >>>> >>> -- >> 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. >> > -- > You received this message because you are subscribed to a topic in the > Google Groups "H2 Database" group. > To unsubscribe from this topic, visit > https://groups.google.com/d/topic/h2-database/AbSvMyYo8Do/unsubscribe. > To unsubscribe from this group and all its topics, 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. > -- Daniele Renda -- 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.