Creating a Sequence
A
SEQUENCE is a data object that multiple users can access to automatically generate incremented values.
It prevents duplicate values from being created simultaneously because multiple users are effectively forced to “take turns” before each sequential item is generated.
For the purposes of creating a unique primary key for a new table,
- First we must
CREATEthe table, course3, we’ll be using. - Add a primary key to the table by using
ALTER TABLE. - We’ll create our
SEQUENCEthat will be utilized later to actually generate the unique, auto incremented value. INSERTa row into the table and useNEXTVALfunction to generate a primary key.
SQL> DROP TABLE course3;
Table dropped.
SQL> CREATE TABLE course3 (
2 cnumber NUMBER NOT NULL,
3 title VARCHAR2(64) NOT NULL );
Table created.
SQL> ALTER TABLE course3
2 ADD ( CONSTRAINT course3_pk PRIMARY KEY ( cnumber ) );
Table altered.
SQL> CREATE SEQUENCE course3_seq;
Sequence created.
SQL> INSERT INTO course3 VALUES ( course3_seq.NEXTVAL, 'Databases' );
1 row created.
SQL> INSERT INTO course3 VALUES ( course3_seq.NEXTVAL, 'Advanced Database Systems' );
1 row created.
SQL> INSERT INTO course3 VALUES ( course3_seq.NEXTVAL, 'Data Engineering and Mining' );
1 row created.
SQL> SELECT * FROM course3;
CNUMBER TITLE
---------- ------------------------------------
1 Databases
2 Advanced Database Systems
3 Data Engineering and Mining
|
Demonstration
Below is an SQL test area from W3Schools, which uses the well-known Northwind sample database. The tables here are for read only because of the problem of embedding the scripts. For a fully working example, check this by using Chrome.
|
Result:
|
The Database includes:
|
|
The Database includes:
|