yexinru/doc-exports
1
0
Fork 0
forked from docs/doc-exports
Code Pull Requests Activity
Files
sgode-patch-2
doc-exports/docs/dws/dev/dws_06_0174.html
luhuayi 177cd61a57 DWS DEVG 910.211 version 
Reviewed-by: Pruthi, Vineet <vineet.pruthi@t-systems.com>
Co-authored-by: luhuayi <luhuayi@huawei.com>
Co-committed-by: luhuayi <luhuayi@huawei.com>
2025-05-05 07:44:03 +00:00

149 lines
27 KiB
HTML
Raw Permalink Blame History

<a name="EN-US_TOPIC_0000001764516358"></a><a name="EN-US_TOPIC_0000001764516358"></a>
<h1 class="topictitle1">CREATE SEQUENCE</h1>
<div id="body1494377960800"><div class="section" id="EN-US_TOPIC_0000001764516358__sbf6e494f355248d8a5a4005609f0f4c3"><h4 class="sectiontitle">Function</h4><p id="EN-US_TOPIC_0000001764516358__p88567147426"><strong id="EN-US_TOPIC_0000001764516358__b027872202911">CREATE SEQUENCE</strong> adds a sequence to the current database. The owner of a sequence is the user who creates the sequence.</p>
</div>
<div class="section" id="EN-US_TOPIC_0000001764516358__s8b93599e33ae4cc985d49c504553df5b"><h4 class="sectiontitle">Precautions</h4><ul id="EN-US_TOPIC_0000001764516358__ul9174659182911"><li id="EN-US_TOPIC_0000001764516358__li14174105982910">A sequence is a special table that stores arithmetic sequence. Such a table is controlled by DBMS. It has no actual meaning and is usually used to generate unique identifiers for rows or tables.</li><li id="EN-US_TOPIC_0000001764516358__li1817485922912">If a schema name is given, the sequence is created in the specified schema; otherwise, it is created in the current schema. The sequence name must be different from the names of other sequences, tables, indexes, views in the same schema.</li><li id="EN-US_TOPIC_0000001764516358__li7174155912913">After the sequence is created, functions nextval() and generate_series(1,N) insert data to the table. Make sure that the number of times for invoking nextval is greater than or equal to N+1. Otherwise, errors will be reported because the number of times for invoking function generate_series() is N+1.</li><li id="EN-US_TOPIC_0000001764516358__li157991627384">A sequence cannot be created in the <strong id="EN-US_TOPIC_0000001764516358__b173818281103">template1</strong> database.</li></ul>
</div>
<div class="section" id="EN-US_TOPIC_0000001764516358__s056a0d3e3c494c3689f35de056ab8972"><h4 class="sectiontitle">Syntax</h4><div class="codecoloring" codetype="Sql" id="EN-US_TOPIC_0000001764516358__s7ed71564220a42829382d012816c24fb"><div class="highlight"><table class="highlighttable"><tr><td class="linenos"><div class="linenodiv"><pre><span class="normal">1</span>
<span class="normal">2</span>
<span class="normal">3</span>
<span class="normal">4</span></pre></div></td><td class="code"><div><pre><span></span><span class="k">CREATE</span><span class="w"> </span><span class="n">SEQUENCE</span><span class="w"> </span><span class="p">[</span><span class="w"> </span><span class="k">IF</span><span class="w"> </span><span class="k">NOT</span><span class="w"> </span><span class="k">EXISTS</span><span class="w"> </span><span class="p">]</span><span class="w"> </span><span class="n">name</span><span class="w"> </span><span class="p">[</span><span class="w"> </span><span class="k">INCREMENT</span><span class="w"> </span><span class="p">[</span><span class="w"> </span><span class="k">BY</span><span class="w"> </span><span class="p">]</span><span class="w"> </span><span class="k">increment</span><span class="w"> </span><span class="p">]</span>
<span class="w"> </span><span class="p">[</span><span class="w"> </span><span class="k">MINVALUE</span><span class="w"> </span><span class="k">minvalue</span><span class="w"> </span><span class="o">|</span><span class="w"> </span><span class="k">NO</span><span class="w"> </span><span class="k">MINVALUE</span><span class="w"> </span><span class="o">|</span><span class="w"> </span><span class="n">NOMINVALUE</span><span class="w"> </span><span class="p">]</span><span class="w"> </span><span class="p">[</span><span class="w"> </span><span class="k">MAXVALUE</span><span class="w"> </span><span class="k">maxvalue</span><span class="w"> </span><span class="o">|</span><span class="w"> </span><span class="k">NO</span><span class="w"> </span><span class="k">MAXVALUE</span><span class="w"> </span><span class="o">|</span><span class="w"> </span><span class="n">NOMAXVALUE</span><span class="p">]</span><span class="w"> </span>
<span class="w"> </span><span class="p">[</span><span class="w"> </span><span class="k">START</span><span class="w"> </span><span class="p">[</span><span class="w"> </span><span class="k">WITH</span><span class="w"> </span><span class="p">]</span><span class="w"> </span><span class="k">start</span><span class="w"> </span><span class="p">]</span><span class="w"> </span><span class="p">[</span><span class="w"> </span><span class="k">CACHE</span><span class="w"> </span><span class="k">cache</span><span class="w"> </span><span class="p">]</span><span class="w"> </span><span class="p">[</span><span class="w"> </span><span class="p">[</span><span class="w"> </span><span class="k">NO</span><span class="w"> </span><span class="p">]</span><span class="w"> </span><span class="k">CYCLE</span><span class="w"> </span><span class="o">|</span><span class="w"> </span><span class="n">NOCYCLE</span><span class="w"> </span><span class="p">]</span><span class="w"> </span>
<span class="w"> </span><span class="p">[</span><span class="w"> </span><span class="n">OWNED</span><span class="w"> </span><span class="k">BY</span><span class="w"> </span><span class="err">{</span><span class="w"> </span><span class="k">table_name</span><span class="p">.</span><span class="k">column_name</span><span class="w"> </span><span class="o">|</span><span class="w"> </span><span class="k">NONE</span><span class="w"> </span><span class="err">}</span><span class="w"> </span><span class="p">];</span>
</pre></div></td></tr></table></div>
</div>
</div>
<div class="section" id="EN-US_TOPIC_0000001764516358__s257b0c90b9d7413ba8530e8aba0a52d4"><h4 class="sectiontitle">Parameter Description</h4><ul id="EN-US_TOPIC_0000001764516358__ul11587180182917"><li id="EN-US_TOPIC_0000001764516358__li69103172920"><strong id="EN-US_TOPIC_0000001764516358__b69102162915">IF NOT EXISTS</strong><p id="EN-US_TOPIC_0000001764516358__p140752072617">If <strong id="EN-US_TOPIC_0000001764516358__b18864192953013">IF NOT EXISTS</strong> is specified and a sequence with the same name does not exist, the sequence can be created successfully. If a sequence with the same name already exists during sequence creation, the system will display a message indicating that the sequence already exists and no further operations will be performed. No error will be reported.</p>
<p id="EN-US_TOPIC_0000001764516358__p139104110293">This parameter is supported only by 9.1.0 and later versions.</p>
</li></ul>
<ul id="EN-US_TOPIC_0000001764516358__u7b7857c9bf73405dbc9b4d3dfa208fc9"><li id="EN-US_TOPIC_0000001764516358__l93f12270bf1a41c2850fbf36c019a2b0"><strong id="EN-US_TOPIC_0000001764516358__a9f37ae09889b41d9930a3a6bd0e388a7">name</strong><p id="EN-US_TOPIC_0000001764516358__p9299178288">Specifies the name of the sequence to be created.</p>
<p id="EN-US_TOPIC_0000001764516358__adc06c83b6076490ea7f238d16459bcde">Value range: The value can contain only lowercase letters, uppercase letters, special characters #_$, and digits.</p>
</li><li id="EN-US_TOPIC_0000001764516358__l6a528c1b5d0f4181b16542ed6409e1e7"><strong id="EN-US_TOPIC_0000001764516358__adb6aa450f7294082ac0b8d7d00bd2c6c">increment</strong><p id="EN-US_TOPIC_0000001764516358__a4c00482cff1c495298268e1f879b3730">Specifies the step for a sequence. A positive generates an ascending sequence, and a negative generates a decreasing sequence.</p>
<p id="EN-US_TOPIC_0000001764516358__en-us_topic_0059778825_p614152462810">The default value is <strong id="EN-US_TOPIC_0000001764516358__b12242098513350">1</strong>.</p>
</li><li id="EN-US_TOPIC_0000001764516358__l23a80e6098d74ad3b90e8af6fe22d69a"><strong id="EN-US_TOPIC_0000001764516358__acb485af3197e471f816a1a7963baba35">MINVALUE minvalue | NO MINVALUE| NOMINVALUE</strong><p id="EN-US_TOPIC_0000001764516358__p1291840107">Specifies the minimum value of the sequence. If <strong id="EN-US_TOPIC_0000001764516358__b842352706135624">MINVALUE</strong> is not declared, or <strong id="EN-US_TOPIC_0000001764516358__b842352706135640">NO MINVALUE</strong> is declared, the default value of the ascending sequence is <strong id="EN-US_TOPIC_0000001764516358__b842352706135720">1</strong>, and that of the descending sequence is <strong id="EN-US_TOPIC_0000001764516358__b842352706135753">-2<sup id="EN-US_TOPIC_0000001764516358__sup1502194170135748">63</sup>-1</strong>.</p>
<p id="EN-US_TOPIC_0000001764516358__a766b2919646d4097af02152643d66219"><strong id="EN-US_TOPIC_0000001764516358__b84235270619423">NOMINVALUE</strong> is equivalent to <strong id="EN-US_TOPIC_0000001764516358__b84235270619425">NO MINVALUE</strong>.</p>
</li><li id="EN-US_TOPIC_0000001764516358__l62a77a97275d4c4da0b57122df484cf1"><strong id="EN-US_TOPIC_0000001764516358__a1f4f99ab00784cc5bf37cb148a0aadc8">MAXVALUE maxvalue | NO MAXVALUE| NOMAXVALUE</strong><p id="EN-US_TOPIC_0000001764516358__p712111520101">Specifies the maximum value in a sequence. If <strong id="EN-US_TOPIC_0000001764516358__b961018679">MAXVALUE</strong> is not declared or <strong id="EN-US_TOPIC_0000001764516358__b2078767904">NO MAXVALUE</strong> is declared, the default value of the ascending sequence is <strong id="EN-US_TOPIC_0000001764516358__b1823960187">2<sup id="EN-US_TOPIC_0000001764516358__sup534742802135837">63</sup>-1</strong>, and that of the descending sequence is <strong id="EN-US_TOPIC_0000001764516358__b2036846420">-1</strong>.</p>
<p id="EN-US_TOPIC_0000001764516358__en-us_topic_0059778825_p446510284309"><strong id="EN-US_TOPIC_0000001764516358__b842352706194235">NOMAXVALUE</strong> is equivalent to <strong id="EN-US_TOPIC_0000001764516358__b842352706194237">NO MAXVALUE</strong>.</p>
</li><li id="EN-US_TOPIC_0000001764516358__l9f4e7d11575e498484da11935fef7289"><strong id="EN-US_TOPIC_0000001764516358__en-us_topic_0059778825_b39725374619">start</strong><p id="EN-US_TOPIC_0000001764516358__p188206131018">Specifies the start value of the sequence.</p>
<p id="EN-US_TOPIC_0000001764516358__en-us_topic_0059778825_p979016193462">The default value for ascending sequences is <strong id="EN-US_TOPIC_0000001764516358__b84235270614014">minvalue</strong> and for descending sequences <strong id="EN-US_TOPIC_0000001764516358__b84235270614038">maxvalue</strong>.</p>
</li><li id="EN-US_TOPIC_0000001764516358__l715b88a61b574d398c30e8c4dacca0b9"><strong id="EN-US_TOPIC_0000001764516358__en-us_topic_0059778825_b625174917531">cache</strong><p id="EN-US_TOPIC_0000001764516358__a5ad4e7a6ec884d858d72dc1db8777876">Specifies the number sequences stored in the memory for quick access purposes. Within a cache period, the CN does not request a sequence number from the GTM. Instead, the CN uses the sequence number that is locally applied for in advance.</p>
<p id="EN-US_TOPIC_0000001764516358__en-us_topic_0059778825_p48678338539">In cluster versions 9.1.0.100 and later, the default value is specified by the GUC parameter <strong id="EN-US_TOPIC_0000001764516358__b33173525456">default_sequence_cache</strong>.</p>
<ul id="EN-US_TOPIC_0000001764516358__ul1539223082217"><li id="EN-US_TOPIC_0000001764516358__li3392163012223">In a newly installed cluster of 9.1.0.100 or later, the default value is <strong id="EN-US_TOPIC_0000001764516358__b130016683118">20</strong>.</li><li id="EN-US_TOPIC_0000001764516358__li03921430112219">If the cluster is upgraded to version 9.1.0.100 or later from an earlier version, the default value will be <strong id="EN-US_TOPIC_0000001764516358__b15734112364610">1</strong>. This means that only one value can be generated at a time, and no cache will be available.</li></ul>
<div class="note" id="EN-US_TOPIC_0000001764516358__na13b9c7f1d3e4d27aeae66c81445b114"><img src="public_sys-resources/note_3.0-en-us.png"><span class="notetitle"> </span><div class="notebody"><ul id="EN-US_TOPIC_0000001764516358__ul29441356409"><li id="EN-US_TOPIC_0000001764516358__li20944635114012">It is not recommended that you define <strong id="EN-US_TOPIC_0000001764516358__b84235270614427">cache</strong>, and <strong id="EN-US_TOPIC_0000001764516358__b84235270614431">maxvalue</strong>, and <strong id="EN-US_TOPIC_0000001764516358__b84235270614438">minvalue</strong> at the same time. The continuity of sequences cannot be ensured after <strong id="EN-US_TOPIC_0000001764516358__b13224174119211">cache</strong> is defined because unacknowledged sequences may be generated, wasting sequence number segments.</li><li id="EN-US_TOPIC_0000001764516358__li1694573517406">You are advised not to set a large value for <strong id="EN-US_TOPIC_0000001764516358__b169663211317">cache</strong> (less than 100000000). Otherwise, it takes a long time to cache the sequence number (the first <strong id="EN-US_TOPIC_0000001764516358__b1696715216320">NEXTVAL</strong> in each cache period). Set a proper value for <strong id="EN-US_TOPIC_0000001764516358__b136741215941">cache</strong> based on services to ensure quick access without wasting sequence numbers.</li></ul>
</div></div>
</li><li id="EN-US_TOPIC_0000001764516358__en-us_topic_0059778825_li13432076215"><strong id="EN-US_TOPIC_0000001764516358__en-us_topic_0059778825_b629919521575">CYCLE</strong><p id="EN-US_TOPIC_0000001764516358__a1f73784205fb4f83aa4365c5fa08d368">Used to ensure that sequences can recycle after the number of sequences reaches <strong id="EN-US_TOPIC_0000001764516358__b842352706141020">maxvalue</strong> or <strong id="EN-US_TOPIC_0000001764516358__b842352706141029">minvalue</strong>.</p>
<p id="EN-US_TOPIC_0000001764516358__en-us_topic_0059778825_p157124516598">If you declare <strong id="EN-US_TOPIC_0000001764516358__b842352706143548">NO CYCLE</strong>, any invocation of <strong id="EN-US_TOPIC_0000001764516358__b842352706143616">nextval</strong> would return an error after the sequence reaches its maximum value.</p>
<p id="EN-US_TOPIC_0000001764516358__a4a06d021b0214b9396736bd8fded2829"><strong id="EN-US_TOPIC_0000001764516358__b842352706143651">NOCYCLE</strong> is equivalent to <strong id="EN-US_TOPIC_0000001764516358__b842352706143654">NO CYCLE</strong>.</p>
<p id="EN-US_TOPIC_0000001764516358__a7041fa0174ad4254b5080c26037e9d9f">The default value is <strong id="EN-US_TOPIC_0000001764516358__b842352706141433">NO CYCLE</strong>.</p>
<p id="EN-US_TOPIC_0000001764516358__adb49ca0d2a714e7ca0bd57e5cd4bafda">If the sequence is defined as <strong id="EN-US_TOPIC_0000001764516358__b1475553613301">CYCLE</strong>, the sequence uniqueness cannot be ensured.</p>
</li><li id="EN-US_TOPIC_0000001764516358__en-us_topic_0059778825_li59111551536"><strong id="EN-US_TOPIC_0000001764516358__en-us_topic_0059778825_b189111515311">OWNED BY</strong>-<p id="EN-US_TOPIC_0000001764516358__en-us_topic_0059778825_p33501231969">Associates a sequence with a specified column included in a table. In this way, the sequence will be deleted when you delete its associated field or the table where the field belongs. The associated table and sequence must be owned by the same user and in the same schema. <strong id="EN-US_TOPIC_0000001764516358__b28523102006">OWNED BY</strong> only establishes the association between a table column and the sequence. The sequence is not created for this column.</p>
<p id="EN-US_TOPIC_0000001764516358__en-us_topic_0059778825_p19630241866">If the default value is <strong id="EN-US_TOPIC_0000001764516358__b842352706141754">OWNED BY NONE</strong>, indicating that such association does not exist.</p>
<div class="notice" id="EN-US_TOPIC_0000001764516358__note114471820913"><span class="noticetitle"><img src="public_sys-resources/notice_3.0-en-us.png"> </span><div class="noticebody"><p id="EN-US_TOPIC_0000001764516358__p1644893525814">You are not advised to use the sequence created using <strong id="EN-US_TOPIC_0000001764516358__b1666923402812">OWNED BY</strong> in other tables. If multiple tables need to share a sequence, the sequence must not belong to a specific table.</p>
</div></div>
</li></ul>
</div>
<div class="section" id="EN-US_TOPIC_0000001764516358__sdf283ce6b18045b08938f6eb51bb77c8"><h4 class="sectiontitle">Examples</h4><p id="EN-US_TOPIC_0000001764516358__a24b7672894d84879b47ee7c283987333">Create an ascending sequence named <strong id="EN-US_TOPIC_0000001764516358__b842352706101754">serial</strong>, which starts from 101:</p>
<div class="codecoloring" codetype="Sql" id="EN-US_TOPIC_0000001764516358__sb550f83e8ead4ae5ab42f6ef0a5e7361"><div class="highlight"><table class="highlighttable"><tr><td class="linenos"><div class="linenodiv"><pre><span class="normal">1</span>
<span class="normal">2</span>
<span class="normal">3</span></pre></div></td><td class="code"><div><pre><span></span><span class="k">CREATE</span><span class="w"> </span><span class="n">SEQUENCE</span><span class="w"> </span><span class="nb">serial</span>
<span class="w"> </span><span class="k">START</span><span class="w"> </span><span class="mi">101</span>
<span class="w"> </span><span class="k">CACHE</span><span class="w"> </span><span class="mi">20</span><span class="p">;</span>
</pre></div></td></tr></table></div>
</div>
<p id="EN-US_TOPIC_0000001764516358__en-us_topic_0059778825_p962620363812">Select the next number from the sequence:</p>
<div class="codecoloring" codetype="Sql" id="EN-US_TOPIC_0000001764516358__sabb7cd7c342c4cd0bb10181677649015"><div class="highlight"><table class="highlighttable"><tr><td class="linenos"><div class="linenodiv"><pre><span class="normal">1</span>
<span class="normal">2</span>
<span class="normal">3</span>
<span class="normal">4</span></pre></div></td><td class="code"><div><pre><span></span><span class="k">SELECT</span><span class="w"> </span><span class="n">nextval</span><span class="p">(</span><span class="s1">'serial'</span><span class="p">);</span>
<span class="w"> </span><span class="n">nextval</span><span class="w"> </span>
<span class="w"> </span><span class="c1">---------</span>
<span class="w"> </span><span class="mi">101</span>
</pre></div></td></tr></table></div>
</div>
<p id="EN-US_TOPIC_0000001764516358__en-us_topic_0059778825_p74906671012">Select the next number from the sequence:</p>
<div class="codecoloring" codetype="Sql" id="EN-US_TOPIC_0000001764516358__s072c27b703424c9593e7fa01cd4aa0a2"><div class="highlight"><table class="highlighttable"><tr><td class="linenos"><div class="linenodiv"><pre><span class="normal">1</span>
<span class="normal">2</span>
<span class="normal">3</span>
<span class="normal">4</span></pre></div></td><td class="code"><div><pre><span></span><span class="k">SELECT</span><span class="w"> </span><span class="n">nextval</span><span class="p">(</span><span class="s1">'serial'</span><span class="p">);</span>
<span class="w"> </span><span class="n">nextval</span><span class="w"> </span>
<span class="w"> </span><span class="c1">---------</span>
<span class="w"> </span><span class="mi">102</span>
</pre></div></td></tr></table></div>
</div>
<p id="EN-US_TOPIC_0000001764516358__aa5005d8e205246ac94347c785adb6eab">Create a sequence associated with the table:</p>
<div class="codecoloring" codetype="Sql" id="EN-US_TOPIC_0000001764516358__s7734ccbbde9f42c9b6b542824ac9445e"><div class="highlight"><table class="highlighttable"><tr><td class="linenos"><div class="linenodiv"><pre><span class="normal"> 1</span>
<span class="normal"> 2</span>
<span class="normal"> 3</span>
<span class="normal"> 4</span>
<span class="normal"> 5</span>
<span class="normal"> 6</span>
<span class="normal"> 7</span>
<span class="normal"> 8</span>
<span class="normal"> 9</span>
<span class="normal">10</span>
<span class="normal">11</span>
<span class="normal">12</span>
<span class="normal">13</span>
<span class="normal">14</span>
<span class="normal">15</span>
<span class="normal">16</span>
<span class="normal">17</span>
<span class="normal">18</span>
<span class="normal">19</span>
<span class="normal">20</span>
<span class="normal">21</span></pre></div></td><td class="code"><div><pre><span></span><span class="k">CREATE</span><span class="w"> </span><span class="k">TABLE</span><span class="w"> </span><span class="n">customer_address</span>
<span class="p">(</span>
<span class="w"> </span><span class="n">ca_address_sk</span><span class="w"> </span><span class="nb">integer</span><span class="w"> </span><span class="k">not</span><span class="w"> </span><span class="k">null</span><span class="p">,</span>
<span class="w"> </span><span class="n">ca_address_id</span><span class="w"> </span><span class="nb">char</span><span class="p">(</span><span class="mi">16</span><span class="p">)</span><span class="w"> </span><span class="k">not</span><span class="w"> </span><span class="k">null</span><span class="p">,</span>
<span class="w"> </span><span class="n">ca_street_number</span><span class="w"> </span><span class="nb">char</span><span class="p">(</span><span class="mi">10</span><span class="p">)</span><span class="w"> </span><span class="p">,</span>
<span class="w"> </span><span class="n">ca_street_name</span><span class="w"> </span><span class="nb">varchar</span><span class="p">(</span><span class="mi">60</span><span class="p">)</span><span class="w"> </span><span class="p">,</span>
<span class="w"> </span><span class="n">ca_street_type</span><span class="w"> </span><span class="nb">char</span><span class="p">(</span><span class="mi">15</span><span class="p">)</span><span class="w"> </span><span class="p">,</span>
<span class="w"> </span><span class="n">ca_suite_number</span><span class="w"> </span><span class="nb">char</span><span class="p">(</span><span class="mi">10</span><span class="p">)</span><span class="w"> </span><span class="p">,</span>
<span class="w"> </span><span class="n">ca_city</span><span class="w"> </span><span class="nb">varchar</span><span class="p">(</span><span class="mi">60</span><span class="p">)</span><span class="w"> </span><span class="p">,</span>
<span class="w"> </span><span class="n">ca_county</span><span class="w"> </span><span class="nb">varchar</span><span class="p">(</span><span class="mi">30</span><span class="p">)</span><span class="w"> </span><span class="p">,</span>
<span class="w"> </span><span class="n">ca_state</span><span class="w"> </span><span class="nb">char</span><span class="p">(</span><span class="mi">2</span><span class="p">)</span><span class="w"> </span><span class="p">,</span>
<span class="w"> </span><span class="n">ca_zip</span><span class="w"> </span><span class="nb">char</span><span class="p">(</span><span class="mi">10</span><span class="p">)</span><span class="w"> </span><span class="p">,</span>
<span class="w"> </span><span class="n">ca_country</span><span class="w"> </span><span class="nb">varchar</span><span class="p">(</span><span class="mi">20</span><span class="p">)</span><span class="w"> </span><span class="p">,</span>
<span class="w"> </span><span class="n">ca_gmt_offset</span><span class="w"> </span><span class="nb">decimal</span><span class="p">(</span><span class="mi">5</span><span class="p">,</span><span class="mi">2</span><span class="p">)</span><span class="w"> </span><span class="p">,</span>
<span class="w"> </span><span class="n">ca_location_type</span><span class="w"> </span><span class="nb">char</span><span class="p">(</span><span class="mi">20</span><span class="p">)</span><span class="w"> </span>
<span class="p">)</span><span class="w"> </span><span class="p">;</span>
<span class="k">CREATE</span><span class="w"> </span><span class="n">SEQUENCE</span><span class="w"> </span><span class="n">serial1</span>
<span class="w"> </span><span class="k">START</span><span class="w"> </span><span class="mi">101</span>
<span class="w"> </span><span class="k">CACHE</span><span class="w"> </span><span class="mi">20</span>
<span class="n">OWNED</span><span class="w"> </span><span class="k">BY</span><span class="w"> </span><span class="n">customer_address</span><span class="p">.</span><span class="n">ca_address_sk</span><span class="p">;</span>
</pre></div></td></tr></table></div>
</div>
<p id="EN-US_TOPIC_0000001764516358__p14787124818211">Use <strong id="EN-US_TOPIC_0000001764516358__b679311421141">SERIAL</strong> to create a serial table <strong id="EN-US_TOPIC_0000001764516358__b2027716321543">serial_table</strong> for primary key auto-increment.</p>
<div class="codecoloring" codetype="Sql" id="EN-US_TOPIC_0000001764516358__screen135051750112718"><div class="highlight"><table class="highlighttable"><tr><td class="linenos"><div class="linenodiv"><pre><span class="normal">1</span>
<span class="normal">2</span>
<span class="normal">3</span>
<span class="normal">4</span>
<span class="normal">5</span>
<span class="normal">6</span>
<span class="normal">7</span>
<span class="normal">8</span>
<span class="normal">9</span></pre></div></td><td class="code"><div><pre><span></span><span class="k">CREATE</span><span class="w"> </span><span class="k">TABLE</span><span class="w"> </span><span class="n">serial_table</span><span class="p">(</span><span class="n">a</span><span class="w"> </span><span class="nb">int</span><span class="p">,</span><span class="w"> </span><span class="n">b</span><span class="w"> </span><span class="nb">serial</span><span class="p">);</span>
<span class="k">INSERT</span><span class="w"> </span><span class="k">INTO</span><span class="w"> </span><span class="n">serial_table</span><span class="w"> </span><span class="p">(</span><span class="n">a</span><span class="p">)</span><span class="w"> </span><span class="k">VALUES</span><span class="w"> </span><span class="p">(</span><span class="mi">1</span><span class="p">),(</span><span class="mi">2</span><span class="p">),(</span><span class="mi">3</span><span class="p">);</span>
<span class="k">SELECT</span><span class="w"> </span><span class="o">*</span><span class="w"> </span><span class="k">FROM</span><span class="w"> </span><span class="n">serial_table</span><span class="w"> </span><span class="k">ORDER</span><span class="w"> </span><span class="k">BY</span><span class="w"> </span><span class="n">b</span><span class="p">;</span>
<span class="w"> </span><span class="n">a</span><span class="w"> </span><span class="o">|</span><span class="w"> </span><span class="n">b</span>
<span class="c1">---+---</span>
<span class="w"> </span><span class="mi">1</span><span class="w"> </span><span class="o">|</span><span class="w"> </span><span class="mi">1</span>
<span class="w"> </span><span class="mi">2</span><span class="w"> </span><span class="o">|</span><span class="w"> </span><span class="mi">2</span>
<span class="w"> </span><span class="mi">3</span><span class="w"> </span><span class="o">|</span><span class="w"> </span><span class="mi">3</span>
<span class="p">(</span><span class="mi">3</span><span class="w"> </span><span class="k">rows</span><span class="p">)</span>
</pre></div></td></tr></table></div>
</div>
</div>
<div class="section" id="EN-US_TOPIC_0000001764516358__s7408f0450b9b4dc7948c069376053290"><h4 class="sectiontitle">Helpful Links</h4><p id="EN-US_TOPIC_0000001764516358__en-us_topic_0059778825_p749627459"><a href="dws_06_0205.html">DROP SEQUENCE</a> <a href="dws_06_0137.html">ALTER SEQUENCE</a></p>
</div>
</div>
<div>
<div class="familylinks">
<div class="parentlink"><strong>Parent topic:</strong> <a href="dws_06_0118.html">DDL Syntax</a></div>
</div>
</div>
View Git Blame Copy Permalink