SQL> CREATE SEQUENCE seq_test;
Sequence created.
SQL> CREATE TABLE tb_test (    test_id NUMBER PRIMARY KEY);  2    3  
Table created.
SQL> INSERT INTO tb_test (test_id) VALUES (seq_test.nextval);
1 row created.
SQL> COMMIT;
Commit complete.
SQL> SELECT * FROM tb_test ORDER BY 1 DESC;
   TEST_ID----------   1

$ psql -U alvin -d alvindbpsql (11.9)Type "help" for help.
alvindb=> CREATE SEQUENCE seq_test;CREATE SEQUENCEalvindb=> CREATE TABLE tb_test (alvindb(>     test_id INTEGER PRIMARY KEYalvindb(> );CREATE TABLEalvindb=> INSERT INTO tb_test (test_id) VALUES (nextval('seq_test'));INSERT 0 1alvindb=> SELECT * FROM tb_test ORDER BY 1 DESC; test_id ---------       1(1 row)

SQL> CREATE SEQUENCE seq_test2;
Sequence created.
SQL> CREATE TABLE tb_test2 (    test_id NUMBER PRIMARY KEY,    test_order NUMBER);  2    3    4  
Table created.
SQL> CREATE OR REPLACE TRIGGER trg_b_ins_tb_test2  BEFORE INSERT ON tb_test2  FOR EACH ROWBEGIN  SELECT seq_test2.nextval  INTO :new.test_id  FROM dual;END;  2    3    4    5    6    7    8    9  /
Trigger created.
SQL> INSERT INTO tb_test2 (test_order) VALUES (1);                
1 row created.
SQL> SELECT * FROM tb_test2 ORDER BY 2 DESC;
   TEST_ID TEST_ORDER---------- ------------   1        1

SQL> INSERT INTO tb_test2 (test_id,test_order) VALUES (NULL,2);
1 row created.
SQL> COMMIT;
Commit complete.
SQL> SELECT * FROM tb_test2 ORDER BY 2 DESC;
   TEST_ID TEST_ORDER---------- ----------   2      2   1      1

alvindb=> CREATE SEQUENCE seq_test2;CREATE SEQUENCEalvindb=> CREATE TABLE tb_test2 (alvindb(>     test_id INTEGER PRIMARY KEY,alvindb(>     test_order INTEGERalvindb(> );CREATE TABLEalvindb=> CREATE OR REPLACE FUNCTION trgf_b_ins_tb_test2()alvindb-> RETURNS TRIGGER ASalvindb-> $$alvindb$> BEGINalvindb$>     NEW.test_id := nextval('seq_test2');alvindb$>     RETURN NEW;alvindb$> END;alvindb$> $$alvindb-> LANGUAGE 'plpgsql';CREATE FUNCTIONalvindb=> CREATE TRIGGER trg_b_ins_tb_test2alvindb->     BEFORE INSERT ON tb_test2alvindb->     FOR EACH ROWalvindb->     EXECUTE PROCEDURE trgf_b_ins_tb_test2();CREATE TRIGGERalvindb=> \d+ tb_test2                                   Table "public.tb_test2"   Column   |  Type   | Collation | Nullable | Default | Storage | Stats target | Description ------------+---------+-----------+----------+---------+---------+--------------+------------- test_id    | integer |           | not null |         | plain   |              |  test_order | integer |           |          |         | plain   |              | Indexes:    "tb_test2_pkey" PRIMARY KEY, btree (test_id)Triggers:    trg_b_ins_tb_test2 BEFORE INSERT ON tb_test2 FOR EACH ROW EXECUTE PROCEDURE trgf_b_ins_tb_test2()alvindb=> INSERT INTO tb_test2 (test_order) VALUES (1);INSERT 0 1alvindb=> SELECT * FROM tb_test2 ORDER BY 2 DESC; test_id | test_order ---------+--------------       1 |            1(1 row)

alvindb=> INSERT INTO tb_test2 (test_id,test_order) VALUES (NULL,2);INSERT 0 1alvindb=> SELECT * FROM tb_test2 ORDER BY 2 DESC; test_id | test_order ---------+------------       2 |          2       1 |          1(2 rows)

SQL> CREATE SEQUENCE seq_test3;                              
Sequence created.
SQL> CREATE TABLE tb_test3 (    test_id NUMBER DEFAULT seq_test3.nextval PRIMARY KEY,    test_order NUMBER);  2    3    4      test_id NUMBER DEFAULT seq_test3.nextval PRIMARY KEY,                            *ERROR at line 2:ORA-00984: column not allowed here

Restriction on Default Column ValuesA DEFAULT expression cannot contain references to PL/SQL functions or to other columns, the pseudocolumns CURRVAL, NEXTVAL, LEVEL, PRIOR, and ROWNUM, or date constants that are not fully specified.

SQL> CREATE SEQUENCE seq_test3;
Sequence created.
SQL> CREATE TABLE tb_test3 (    test_id NUMBER DEFAULT seq_test3.nextval PRIMARY KEY,    test_order NUMBER);  2    3    4  
Table created.
SQL> INSERT INTO tb_test3 (test_id,test_order) VALUES (seq_test3.nextval,1);
1 row created.
SQL> INSERT INTO tb_test3 (test_id,test_order) VALUES (DEFAULT,2);
1 row created.
SQL> INSERT INTO tb_test3 (test_order) VALUES (3);
1 row created.
SQL> COMMIT;
Commit complete.
SQL> SELECT * FROM tb_test3 ORDER BY 2 DESC;
   TEST_ID TEST_ORDER---------- ------------   3        3   2        2   1        1

SQL> SET linesize 100COL table_name FOR a30COL column_name FOR a30COL data_default FOR a30SQL> SELECT table_name,column_name,data_default FROM user_tab_columns WHERE table_name = 'TB_TEST3';
TABLE_NAME           COLUMN_NAME          DATA_DEFAULT------------------------------ ------------------------------ ------------------------------TB_TEST3           TEST_ID            "TEST"."SEQ_TEST3"."NEXTVAL"TB_TEST3           TEST_ORDER

SQL> DROP SEQUENCE seq_test3;
Sequence dropped.

SQL> SELECT table_name,column_name,data_default FROM user_tab_columns WHERE table_name = 'TB_TEST3';
TABLE_NAME           COLUMN_NAME          DATA_DEFAULT------------------------------ ------------------------------ ------------------------------TB_TEST3           TEST_ID            "TEST"."SEQ_TEST3"."NEXTVAL"TB_TEST3           TEST_ORDER
SQL> SQL> INSERT INTO tb_test3 (test_order) VALUES (5);INSERT INTO tb_test3 (test_order) VALUES (5)       *ERROR at line 1:ORA-02289: sequence does not exist

alvindb=> CREATE SEQUENCE seq_test3;CREATE SEQUENCEalvindb=> CREATE TABLE tb_test3 (alvindb(>     test_id INTEGER DEFAULT nextval('seq_test3') PRIMARY KEY,alvindb(>     test_order INTEGERalvindb(> );CREATE TABLEalvindb=> INSERT INTO tb_test3 (test_id,test_order) VALUES (nextval('seq_test3'),1);INSERT 0 1alvindb=> INSERT INTO tb_test3 (test_id,test_order) VALUES (DEFAULT,2);INSERT 0 1alvindb=> INSERT INTO tb_test3 (test_order) VALUES (3);INSERT 0 1alvindb=> SELECT * FROM tb_test3 ORDER BY 2 DESC; test_id | test_order ---------+--------------       3 |            3       2 |            2       1 |            1(3 rows)

alvindb=> CREATE SEQUENCE seq_test3;CREATE SEQUENCEalvindb=> CREATE TABLE tb_test3 (alvindb(>     test_id INTEGER DEFAULT nextval('seq_test3') PRIMARY KEY,alvindb(>     test_order INTEGERalvindb(> );CREATE TABLEalvindb=> \d+ tb_test3                                               Table "public.tb_test3"   Column   |  Type   | Collation | Nullable |            Default             | Storage | Stats target | Description ------------+---------+-----------+----------+--------------------------------+---------+--------------+------------- test_id    | integer |           | not null | nextval('seq_test3'::regclass) | plain   |              |  test_order | integer |           |          |                                | plain   |              | Indexes:    "tb_test3_pkey" PRIMARY KEY, btree (test_id)alvindb=> DROP SEQUENCE seq_test3;ERROR:  cannot drop sequence seq_test3 because other objects depend on itDETAIL:  default value for column test_id of table tb_test3 depends on sequence seq_test3HINT:  Use DROP ... CASCADE to drop the dependent objects too.alvindb=> DROP SEQUENCE tb_test4_test_id_seq CASCADE;NOTICE:  drop cascades to default value for column test_id of table tb_test4DROP SEQUENCE

CREATE TABLE distributors (    name     VARCHAR(40) DEFAULT 'luso films',    did      INTEGER  DEFAULT NEXTVAL('distributors_serial'),    modtime  TIMESTAMP DEFAULT now());

SQL> CREATE TABLE tb_test4 (    test_id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY,    test_order NUMBER);  2    3    4  
Table created.

SQL> INSERT INTO tb_test4 (test_id,test_order) VALUES (DEFAULT,1);
1 row created.
SQL> INSERT INTO tb_test4 (test_order) VALUES (2);
1 row created.
SQL> COMMIT;
Commit complete.
SQL> SELECT * FROM tb_test4 ORDER BY 2 DESC;
   TEST_ID TEST_ORDER---------- ------------   2        2   1        1

SQL> COL object_name FOR a30COL object_type FOR a30SQL> SQL> SELECT object_name,object_type FROM user_objects;
OBJECT_NAME           OBJECT_TYPE------------------------------ ------------------------------TB_TEST4           TABLEISEQ$$_254835           SEQUENCESQL> SET linesize 100COL table_name FOR a30COL column_name FOR a30COL data_default FOR a30SQL> SELECT table_name,column_name,data_default FROM user_tab_columns WHERE table_name = 'TB_TEST4';
TABLE_NAME           COLUMN_NAME          DATA_DEFAULT------------------------------ ------------------------------ ------------------------------TB_TEST4           TEST_ID            "TEST"."ISEQ$$_254857".nextvalTB_TEST4           TEST_ORDER

SQL> SET linesize 200COL table_name FOR a30COL column_name FOR a30COL generation FOR a30COL sequence_name FOR a30SQL> SQL> SQL> SQL> SQL> SELECT table_name,column_name,sequence_name FROM user_tab_identity_cols;
TABLE_NAME           COLUMN_NAME          SEQUENCE_NAME------------------------------ ------------------------------ ------------------------------TB_TEST4           TEST_ID            ISEQ$$_254835

SQL> DROP SEQUENCE ISEQ$$_254835;DROP SEQUENCE ISEQ$$_254835              *ERROR at line 1:ORA-32794: cannot drop a system-generated sequence

SQL> DROP TABLE tb_test4;
Table dropped.
SQL> SELECT table_name,column_name,sequence_name FROM user_tab_identity_cols;
TABLE_NAME           COLUMN_NAME          SEQUENCE_NAME------------------------------ ------------------------------ ------------------------------BIN$vXiAW/32gIHgU5KhXwowkg==$0 TEST_ID            ISEQ$$_254835
SQL> SELECT object_name,object_type FROM USER_OBJECTS;
OBJECT_NAME           OBJECT_TYPE------------------------------ ------------------------------ISEQ$$_254835           SEQUENCE

SQL> PURGE RECYCLEBIN;
Recyclebin purged.
SQL> SELECT object_name,object_type FROM USER_OBJECTS;
no rows selected
SQL> SELECT table_name,column_name,sequence_name FROM user_tab_identity_cols;
no rows selected

SQL> SELECT * FROM tb_test4 ORDER BY 2 DESC;
   TEST_ID TEST_ORDER---------- ----------   6      8
SQL> INSERT INTO tb_test4 (test_id,test_order) VALUES (NULL,9);
1 row created.
SQL> COMMIT;
Commit complete.
SQL> SELECT * FROM tb_test4 ORDER BY 2 DESC;
   TEST_ID TEST_ORDER---------- ----------   7      9   6      8

SQL> CREATE TABLE tb_test5 (    test_id NUMBER GENERATED BY DEFAULT AS IDENTITY,    test_order NUMBER);   2    3    4  
Table created.
SQL> INSERT INTO tb_test5 (test_id,test_order) VALUES (NULL,1);INSERT INTO tb_test5 (test_id,test_order) VALUES (NULL,1)                                                  *ERROR at line 1:ORA-01400: cannot insert NULL into ("TEST"."TB_TEST5"."TEST_ID")

SQL> CREATE TABLE tb_test6 (    test_id NUMBER GENERATED ALWAYS AS IDENTITY,    test_order NUMBER);   2    3    4  
Table created.
SQL> INSERT INTO tb_test6 (test_id,test_order) VALUES (1,1);INSERT INTO tb_test6 (test_id,test_order) VALUES (1,1)                      *ERROR at line 1:ORA-32795: cannot insert into a generated always identity column
SQL> INSERT INTO tb_test6 (test_id,test_order) VALUES (NULL,2);INSERT INTO tb_test6 (test_id,test_order) VALUES (NULL,2)                      *ERROR at line 1:ORA-32795: cannot insert into a generated always identity column

SQL> INSERT INTO tb_test6 (test_id,test_order) VALUES (DEFAULT,3);
1 row created.SQL> COMMIT;
Commit complete.
SQL> SELECT * FROM tb_test6 ORDER BY 2 DESC;
   TEST_ID TEST_ORDER---------- ----------   1      3

alvindb=> CREATE TABLE tb_test4 (alvindb(>     test_id SERIAL PRIMARY KEY,alvindb(>     test_order INTEGERalvindb(> );CREATE TABLE

alvindb=> INSERT INTO tb_test4 (test_id,test_order) VALUES (DEFAULT,1);INSERT 0 1alvindb=> INSERT INTO tb_test4 (test_order) VALUES (2);INSERT 0 1alvindb=> SELECT * FROM tb_test4 ORDER BY 2 DESC; test_id | test_order ---------+--------------       2 |            2       1 |            1(2 rows)

alvindb=> \d+ tb_test4                                                    Table "public.tb_test4"   Column   |  Type   | Collation | Nullable |                  Default                  | Storage | Stats target | Description ------------+---------+-----------+----------+-------------------------------------------+---------+--------------+------------- test_id    | integer |           | not null | nextval('tb_test4_test_id_seq'::regclass) | plain   |              |  test_order | integer |           |          |                                           | plain   |              | Indexes:    "tb_test4_pkey" PRIMARY KEY, btree (test_id)

alvindb=> \d+ tb_test4_test_id_seq                Sequence "public.tb_test4_test_id_seq"  Type   | Start | Minimum |  Maximum   | Increment | Cycles? | Cache ---------+-------+---------+------------+-----------+---------+------- integer |     1 |       1 | 2147483647 |         1 | no      |     1Owned by: public.tb_test4.test_id

alvindb=> DROP TABLE tb_test4;DROP TABLEalvindb=> \d+ tb_test4_test_id_seqDid not find any relation named "tb_test4_test_id_seq".alvindb=>

alvindb=> CREATE TABLE tb_test4 (alvindb(>     test_id SERIAL PRIMARY KEY,alvindb(>     test_order INTEGERalvindb(> );CREATE TABLEalvindb=> DROP SEQUENCE tb_test4_test_id_seq;ERROR:  cannot drop sequence tb_test4_test_id_seq because other objects depend on itDETAIL:  default value for column test_id of table tb_test4 depends on sequence tb_test4_test_id_seqHINT:  Use DROP ... CASCADE to drop the dependent objects too.alvindb=> DROP SEQUENCE tb_test4_test_id_seq CASCADE;NOTICE:  drop cascades to default value for column test_id of table tb_test4DROP SEQUENCEalvindb=> \d+ tb_test4                                                    Table "public.tb_test4"   Column   |  Type   | Collation | Nullable |                  Default                  | Storage | Stats target | Description ------------+---------+-----------+----------+-------------------------------------------+---------+--------------+------------- test_id    | integer |           | not null | nextval('tb_test4_test_id_seq'::regclass) | plain   |              |  test_order | integer |           |          |                                           | plain   |              | Indexes:    "tb_test4_pkey" PRIMARY KEY, btree (test_id)

CREATE TABLE tablename (    colname SERIAL);

CREATE SEQUENCE tablename_colname_seq AS integer;CREATE TABLE tablename (    colname integer NOT NULL DEFAULT nextval('tablename_colname_seq'));ALTER SEQUENCE tablename_colname_seq OWNED BY tablename.colname;

alvindb=> INSERT INTO tb_test4 (test_id,test_order) VALUES (NULL,9);ERROR:  null value in column "test_id" violates not-null constraintDETAIL:  Failing row contains (null, 9).alvindb=> INSERT INTO tb_test3 (test_id,test_order) VALUES (NULL,9);ERROR:  null value in column "test_id" violates not-null constraintDETAIL:  Failing row contains (null, 9).

CREATE TABLE cinemas (        id serial,        name text,        location text) TABLESPACE diskvol1;

mysql> CREATE TABLE tb_test4 (    ->     test_id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,    ->     test_order INTEGER    -> );Query OK, 0 rows affected (0.01 sec)mysql> INSERT INTO tb_test4 (test_id,test_order) VALUES (DEFAULT,1);Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO tb_test4 (test_order) VALUES (2);Query OK, 1 row affected (0.01 sec)
mysql> SELECT * FROM tb_test4 ORDER BY 2 DESC;+---------+--------------+| test_id | test_order |+---------+--------------+|       2 |            2 ||       1 |            1 |+---------+--------------+2 rows in set (0.00 sec)

mysql> SHOW CREATE TABLE tb_test4;+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table    | Create Table                                                                                                                                                                                    |+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| tb_test4 | CREATE TABLE `tb_test4` (  `test_id` int(11) NOT NULL AUTO_INCREMENT,  `test_order` int(11) DEFAULT NULL,  PRIMARY KEY (`test_id`)) ENGINE=InnoDB AUTO_INCREMENT=102 DEFAULT CHARSET=latin1 |+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)

mysql> INSERT INTO tb_test4 (test_id,test_order) VALUES (NULL,9);Query OK, 1 row affected (0.01 sec)
mysql> SELECT * FROM tb_test5 ORDER BY 2 DESC;+---------+------------+| test_id | test_order |+---------+------------+|       1 |          8 |+---------+------------+1 row in set (0.00 sec)

INSERT INTO tb_test4 (test_order) VALUES (1);INSERT INTO tb_test4 (test_id,test_order) VALUES (100,2);INSERT INTO tb_test4 (test_order) VALUES (3);UPDATE tb_test4 SET test_id = 200 WHERE test_order = 3;INSERT INTO tb_test4 (test_order) VALUES (5);INSERT INTO tb_test4 (test_order) VALUES (6);SELECT * FROM tb_test4 ORDER BY 2 DESC;DELETE FROM tb_test4 WHERE test_order = 5;DELETE FROM tb_test4 WHERE test_order = 6;INSERT INTO tb_test4 (test_order) VALUES (7);TRUNCATE TABLE tb_test4;INSERT INTO tb_test4 (test_order) VALUES (8);

mysql> INSERT INTO tb_test4 (test_order) VALUES (1);Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM tb_test4 ORDER BY 2 DESC;+---------+------------+| test_id | test_order |+---------+------------+|       1 |          1 |+---------+------------+1 row in set (0.00 sec)
mysql> SELECT last_insert_id();+------------------+| LAST_INSERT_ID() |+------------------+|                1 |+------------------+1 row in set (0.00 sec)
mysql> INSERT INTO tb_test4 (test_id,test_order) VALUES (100,2);Query OK, 1 row affected (0.00 sec)
mysql> SELECT last_insert_id();+------------------+| last_insert_id() |+------------------+|                1 |+------------------+1 row in set (0.00 sec)
mysql> INSERT INTO tb_test4 (test_order) VALUES (3);Query OK, 1 row affected (0.00 sec)
mysql> SELECT last_insert_id();+------------------+| last_insert_id() |+------------------+|              101 |+------------------+1 row in set (0.00 sec)
mysql> SELECT * FROM tb_test4 ORDER BY 2 DESC;+---------+------------+| test_id | test_order |+---------+------------+|     101 |          3 ||     100 |          2 ||       1 |          1 |+---------+------------+3 rows in set (0.00 sec)mysql> UPDATE tb_test4 SET test_id = 200 WHERE test_order = 3;Query OK, 1 row affected (0.00 sec)Rows matched: 1  Changed: 1  Warnings: 0
mysql> SELECT last_insert_id();+------------------+| last_insert_id() |+------------------+|              101 |+------------------+1 row in set (0.00 sec)
mysql> INSERT INTO tb_test4 (test_order) VALUES (5);Query OK, 1 row affected (0.00 sec)
mysql> SELECT last_insert_id();+------------------+| last_insert_id() |+------------------+|              102 |+------------------+1 row in set (0.00 sec)
mysql> SELECT * FROM tb_test4 ORDER BY 2 DESC;+---------+------------+| test_id | test_order |+---------+------------+|     102 |          5 ||     200 |          3 ||     100 |          2 ||       1 |          1 |+---------+------------+4 rows in set (0.00 sec)mysql> INSERT INTO tb_test4 (test_order) VALUES (6);Query OK, 1 row affected (0.01 sec)
mysql> SELECT * FROM tb_test4 ORDER BY 2 DESC;+---------+------------+| test_id | test_order |+---------+------------+|     103 |          6 ||     102 |          5 ||     200 |          3 ||     100 |          2 ||       1 |          1 |+---------+------------+5 rows in set (0.00 sec)
mysql> SELECT last_insert_id();+------------------+| last_insert_id() |+------------------+|              103 |+------------------+1 row in set (0.00 sec)
mysql> DELETE FROM tb_test4 WHERE test_order = 5;Query OK, 1 row affected (0.01 sec)
mysql> SELECT last_insert_id();+------------------+| last_insert_id() |+------------------+|              103 |+------------------+1 row in set (0.00 sec)
mysql> DELETE FROM tb_test4 WHERE test_order = 6;Query OK, 1 row affected (0.00 sec)
mysql> SELECT last_insert_id();+------------------+| last_insert_id() |+------------------+|              103 |+------------------+1 row in set (0.00 sec)
mysql> SELECT * FROM tb_test4 ORDER BY 2 DESC;+---------+------------+| test_id | test_order |+---------+------------+|     200 |          3 ||     100 |          2 ||       1 |          1 |+---------+------------+3 rows in set (0.00 sec)
mysql> INSERT INTO tb_test4 (test_order) VALUES (7);Query OK, 1 row affected (0.01 sec)
mysql> SELECT last_insert_id();+------------------+| last_insert_id() |+------------------+|              104 |+------------------+1 row in set (0.00 sec)
mysql> SELECT * FROM tb_test4 ORDER BY 2 DESC;+---------+------------+| test_id | test_order |+---------+------------+|     104 |          7 ||     200 |          3 ||     100 |          2 ||       1 |          1 |+---------+------------+4 rows in set (0.00 sec)mysql> TRUNCATE TABLE tb_test4;Query OK, 0 rows affected (0.00 sec)
mysql> SELECT last_insert_id();+------------------+| last_insert_id() |+------------------+|              104 |+------------------+1 row in set (0.00 sec)
mysql> INSERT INTO tb_test4 (test_order) VALUES (8);Query OK, 1 row affected (0.00 sec)
mysql> SELECT last_insert_id();+------------------+| last_insert_id() |+------------------+|                1 |+------------------+1 row in set (0.00 sec)
mysql> SELECT * FROM tb_test4 ORDER BY 2 DESC;+---------+------------+| test_id | test_order |+---------+------------+|       1 |          8 |+---------+------------+1 row in set (0.00 sec)

mysql> CREATE TABLE tb_test5 (    ->     test_id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,    ->     test_order INTEGER    -> ) ENGINE = MYISAM;Query OK, 0 rows affected (0.00 sec)
mysql> SHOW CREATE TABLE tb_test5;+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table    | Create Table                                                                                                                                                                 |+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| tb_test5 | CREATE TABLE `tb_test5` (  `test_id` int(11) NOT NULL AUTO_INCREMENT,  `test_order` int(11) DEFAULT NULL,  PRIMARY KEY (`test_id`)) ENGINE=MyISAM DEFAULT CHARSET=latin1 |+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)
mysql> INSERT INTO tb_test5 (test_order) VALUES (1);Query OK, 1 row affected (0.00 sec)
mysql> SELECT last_insert_id();+------------------+| last_insert_id() |+------------------+|                1 |+------------------+1 row in set (0.00 sec)
mysql> INSERT INTO tb_test5 (test_id,test_order) VALUES (100,2);Query OK, 1 row affected (0.00 sec)
mysql> SELECT last_insert_id();+------------------+| last_insert_id() |+------------------+|                1 |+------------------+1 row in set (0.00 sec)
mysql> INSERT INTO tb_test5 (test_order) VALUES (3);Query OK, 1 row affected (0.00 sec)
mysql> SELECT last_insert_id();+------------------+| last_insert_id() |+------------------+|              101 |+------------------+1 row in set (0.00 sec)
mysql> mysql> SELECT * FROM tb_test5 ORDER BY 2 DESC;+---------+------------+| test_id | test_order |+---------+------------+|     101 |          3 ||     100 |          2 ||       1 |          1 |+---------+------------+3 rows in set (0.00 sec)
mysql> UPDATE tb_test5 SET test_id = 200 WHERE test_order = 3;Query OK, 1 row affected (0.00 sec)Rows matched: 1  Changed: 1  Warnings: 0
mysql> SELECT last_insert_id();+------------------+| last_insert_id() |+------------------+|              101 |+------------------+1 row in set (0.00 sec)
mysql> SELECT * FROM tb_test5 ORDER BY 2 DESC;+---------+------------+| test_id | test_order |+---------+------------+|     200 |          3 ||     100 |          2 ||       1 |          1 |+---------+------------+3 rows in set (0.00 sec)
mysql> INSERT INTO tb_test5 (test_order) VALUES (5);Query OK, 1 row affected (0.00 sec)
mysql> SELECT last_insert_id();+------------------+| last_insert_id() |+------------------+|              201 |+------------------+1 row in set (0.00 sec)
mysql> SELECT * FROM tb_test5 ORDER BY 2 DESC;+---------+------------+| test_id | test_order |+---------+------------+|     201 |          5 ||     200 |          3 ||     100 |          2 ||       1 |          1 |+---------+------------+4 rows in set (0.00 sec)
mysql> INSERT INTO tb_test5 (test_order) VALUES (5);Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO tb_test5 (test_order) VALUES (6);Query OK, 1 row affected (0.00 sec)
mysql> SELECT last_insert_id();+------------------+| last_insert_id() |+------------------+|              203 |+------------------+1 row in set (0.00 sec)
mysql> SELECT * FROM tb_test5 ORDER BY 2 DESC;+---------+------------+| test_id | test_order |+---------+------------+|     203 |          6 ||     201 |          5 ||     202 |          5 ||     200 |          3 ||     100 |          2 ||       1 |          1 |+---------+------------+6 rows in set (0.00 sec)
mysql> DELETE FROM tb_test5 WHERE test_order = 5;Query OK, 2 rows affected (0.00 sec)
mysql> DELETE FROM tb_test5 WHERE test_order = 6;Query OK, 1 row affected (0.01 sec)
mysql> SELECT last_insert_id();+------------------+| last_insert_id() |+------------------+|              203 |+------------------+1 row in set (0.00 sec)
mysql> SELECT * FROM tb_test5 ORDER BY 2 DESC;+---------+------------+| test_id | test_order |+---------+------------+|     200 |          3 ||     100 |          2 ||       1 |          1 |+---------+------------+3 rows in set (0.00 sec)
mysql> INSERT INTO tb_test5 (test_order) VALUES (7);Query OK, 1 row affected (0.00 sec)
mysql> SELECT last_insert_id();+------------------+| last_insert_id() |+------------------+|              204 |+------------------+1 row in set (0.00 sec)
mysql> SELECT * FROM tb_test5 ORDER BY 2 DESC;+---------+------------+| test_id | test_order |+---------+------------+|     204 |          7 ||     200 |          3 ||     100 |          2 ||       1 |          1 |+---------+------------+4 rows in set (0.00 sec)
mysql> TRUNCATE TABLE tb_test5;Query OK, 0 rows affected (0.00 sec)
mysql> SELECT last_insert_id();+------------------+| last_insert_id() |+------------------+|              204 |+------------------+1 row in set (0.00 sec)
mysql> INSERT INTO tb_test5 (test_order) VALUES (8);Query OK, 1 row affected (0.00 sec)
mysql> SELECT last_insert_id();+------------------+| last_insert_id() |+------------------+|                1 |+------------------+1 row in set (0.00 sec)
mysql> SELECT * FROM tb_test5 ORDER BY 2 DESC;+---------+------------+| test_id | test_order |+---------+------------+|       1 |          8 |+---------+------------+1 row in set (0.00 sec)
mysql>

SQL> INSERT INTO tb_test4 (test_order) VALUES (1);
1 row created.
SQL> INSERT INTO tb_test4 (test_id,test_order) VALUES (100,2);
1 row created.
SQL> INSERT INTO tb_test4 (test_order) VALUES (3);
1 row created.
SQL> COMMIT;
Commit complete.
SQL> SELECT * FROM tb_test4 ORDER BY 2 DESC;
   TEST_ID TEST_ORDER---------- ----------   2      3       100      2   1      1
SQL> COL table_name FOR a30COL column_name FOR a30COL generation FOR a30COL sequence_name FOR a30SELECT table_name,column_name,sequence_name FROM user_tab_identity_cols;SQL> SQL> SQL> SQL> SQL> SELECT table_name,column_name,sequence_name FROM user_tab_identity_cols;
TABLE_NAME           COLUMN_NAME          SEQUENCE_NAME------------------------------ ------------------------------ ------------------------------TB_TEST4           TEST_ID            ISEQ$$_254864
SQL>SQL> SELECT ISEQ$$_254864.currval FROM dual;
   CURRVAL----------   2SQL> UPDATE tb_test4 SET test_id = 200 WHERE test_order = 3;
1 row updated.
SQL> SELECT ISEQ$$_254864.currval FROM dual;
   CURRVAL----------   2
SQL> INSERT INTO tb_test4 (test_order) VALUES (5);
1 row created.
SQL> SELECT ISEQ$$_254864.currval FROM dual;
   CURRVAL----------   3
SQL> SELECT * FROM tb_test4 ORDER BY 2 DESC;
   TEST_ID TEST_ORDER---------- ----------   3      5       200      3       100      2   1      1
SQL>SQL> INSERT INTO tb_test4 (test_order) VALUES (6);
1 row created.
SQL> COMMIT;
Commit complete.
SQL> SELECT ISEQ$$_254864.currval FROM dual;
   CURRVAL----------   4
SQL> SELECT * FROM tb_test4 ORDER BY 2 DESC;
   TEST_ID TEST_ORDER---------- ----------   4      6   3      5       200      3       100      2   1      1
SQL> SQL> DELETE FROM tb_test4 WHERE test_order = 5;
1 row deleted.
SQL> DELETE FROM tb_test4 WHERE test_order = 6;
1 row deleted.
SQL> COMMIT;
Commit complete.
SQL> INSERT INTO tb_test4 (test_order) VALUES (7);
1 row created.
SQL> COMMIT;
Commit complete.
SQL> SELECT ISEQ$$_254864.currval FROM dual;
   CURRVAL----------   5
SQL> SELECT * FROM tb_test4 ORDER BY 2 DESC;
   TEST_ID TEST_ORDER---------- ----------   5      7       200      3       100      2   1      1SQL> TRUNCATE TABLE tb_test4;
Table truncated.
SQL> INSERT INTO tb_test4 (test_order) VALUES (8);
1 row created.
SQL> SELECT ISEQ$$_254864.currval FROM dual;
   CURRVAL----------   6
SQL> SELECT * FROM tb_test4 ORDER BY 2 DESC;
   TEST_ID TEST_ORDER---------- ----------   6      8

alvindb=> INSERT INTO tb_test4 (test_order) VALUES (1);INSERT 0 1alvindb=> INSERT INTO tb_test4 (test_id,test_order) VALUES (100,2);INSERT 0 1alvindb=> INSERT INTO tb_test4 (test_order) VALUES (3);INSERT 0 1alvindb=> SELECT * FROM tb_test4 ORDER BY 2 DESC; test_id | test_order ---------+------------       2 |          3     100 |          2       1 |          1(3 rows)
alvindb=> alvindb=> \d+ tb_test4                                                    Table "public.tb_test4"   Column   |  Type   | Collation | Nullable |                  Default                  | Storage | Stats target | Description ------------+---------+-----------+----------+-------------------------------------------+---------+--------------+------------- test_id    | integer |           | not null | nextval('tb_test4_test_id_seq'::regclass) | plain   |              |  test_order | integer |           |          |                                           | plain   |              | Indexes:    "tb_test4_pkey" PRIMARY KEY, btree (test_id)
alvindb=> SELECT currval('tb_test4_test_id_seq'); currval ---------       2(1 row)alvindb=> UPDATE tb_test4 SET test_id = 200 WHERE test_order = 3;UPDATE 1alvindb=> SELECT * FROM tb_test4 ORDER BY 2 DESC; test_id | test_order ---------+------------     200 |          3     100 |          2       1 |          1(3 rows)
alvindb=> SELECT currval('tb_test4_test_id_seq'); currval ---------       2(1 row)
alvindb=> INSERT INTO tb_test4 (test_order) VALUES (5);INSERT 0 1alvindb=> SELECT currval('tb_test4_test_id_seq'); currval ---------       3(1 row)
alvindb=> SELECT * FROM tb_test4 ORDER BY 2 DESC; test_id | test_order ---------+------------       3 |          5     200 |          3     100 |          2       1 |          1(4 rows)alvindb=> INSERT INTO tb_test4 (test_order) VALUES (6);INSERT 0 1alvindb=> SELECT currval('tb_test4_test_id_seq'); currval ---------       4(1 row)
alvindb=> SELECT * FROM tb_test4 ORDER BY 2 DESC; test_id | test_order ---------+------------       4 |          6       3 |          5     200 |          3     100 |          2       1 |          1(5 rows)alvindb=> DELETE FROM tb_test4 WHERE test_order = 5;DELETE 1alvindb=> DELETE FROM tb_test4 WHERE test_order = 6;DELETE 1alvindb=> INSERT INTO tb_test4 (test_order) VALUES (7);INSERT 0 1alvindb=> SELECT currval('tb_test4_test_id_seq'); currval ---------       5(1 row)
alvindb=> SELECT * FROM tb_test4 ORDER BY 2 DESC; test_id | test_order ---------+------------       5 |          7     200 |          3     100 |          2       1 |          1(4 rows)
alvindb=> alvindb=> TRUNCATE TABLE tb_test4;TRUNCATE TABLEalvindb=> INSERT INTO tb_test4 (test_order) VALUES (8);INSERT 0 1alvindb=> SELECT currval('tb_test4_test_id_seq'); currval ---------       6(1 row)
alvindb=> SELECT * FROM tb_test4 ORDER BY 2 DESC; test_id | test_order ---------+------------       6 |          8(1 row)

--OracleINSERT INTO tb_test (test_id) VALUES (seq_test.nextval);--PostgreSQLINSERT INTO tb_test (test_id) VALUES (nextval('seq_test'));

INSERT INTO tb_test (test_order) VALUES (1);

INSERT INTO tb_test (test_id,test_order) VALUES (NULL,1);

INSERT INTO tb_test (test_id,test_order) VALUES (DEFAULT,1);