Video
SQL Output from Video
SQL> /**************************************************************************************************************************
SQL>
SQL> Using a Sequence to Generate Values for Primary Keys
SQL>
SQL>
SQL> The context for this example is a small family counseling practice where we will need to keep track of information
SQL>
SQL> counselors, clients, and appointments for counseling.
SQL>
SQL>
SQL> In this example, we will have a list of counselors, each with a COUNSELOR_ID. We will use the COUNSELOR _ID as a unique
SQL>
SQL> identifier for each counselor so we need to be sure that no two counselors have the same value for COUNSELOR _ID.
SQL>
SQL>
SQL> **************************************************************************************************************************/
SQL>
SQL> create table counselor(
2 COUNSELOR_ID int Primary Key,
3 FIRST_NAME varchar(20),
4 LAST_NAME varchar(20),
5 PHONE varchar(14)
6 );
Table created.
SQL> insert into counselor values(10,'Quinn','Fowler','(508) 244-1416');
1 row created.
SQL> insert into counselor values(11,'Elizabeth','Baker','(508) 243-1789');
1 row created.
SQL> insert into counselor values(12,'Darrold','Cooper',NULL);
1 row created.
SQL> select * from counselor;
COUNSELOR_ID FIRST_NAME LAST_NAME PHONE
------------ -------------------- -------------------- --------------
10 Quinn Fowler (508) 244-1416
11 Elizabeth Baker (508) 243-1789
12 Darrold Cooper
SQL> create sequence ObjectID start with 100 increment by 1;
Sequence created.
SQL> select objectid.nextval from dual;
NEXTVAL
----------
100
SQL> select objectid.nextval from dual;
NEXTVAL
----------
101
SQL> select objectid.nextval from dual;
NEXTVAL
----------
102
SQL> select objectid.nextval from dual;
NEXTVAL
----------
103
SQL> CREATE OR REPLACE TRIGGER counselor_PK
2 BEFORE INSERT ON counselor
3 FOR EACH ROW
4 begin
5 select ObjectID.nextval into :new.counselor_id from dual;
6 end;
7 /
Trigger created.
SQL> insert into counselor values(null,'Leslie','Wright','(508) 244-5586');
1 row created.
SQL> select * from counselor;
COUNSELOR_ID FIRST_NAME LAST_NAME PHONE
------------ -------------------- -------------------- --------------
10 Quinn Fowler (508) 244-1416
11 Elizabeth Baker (508) 243-1789
12 Darrold Cooper
104 Leslie Wright (508) 244-5586
SQL> insert into counselor values(1000,'Abigail','Smith','(508) 244-6931');
1 row created.
SQL> select * from counselor;
COUNSELOR_ID FIRST_NAME LAST_NAME PHONE
------------ -------------------- -------------------- --------------
10 Quinn Fowler (508) 244-1416
11 Elizabeth Baker (508) 243-1789
12 Darrold Cooper
104 Leslie Wright (508) 244-5586
105 Abigail Smith (508) 244-6931
SQL> insert into counselor (first_name, last_name, phone) values('James','Sawyer','(508) 247-7631');
1 row created.
SQL> select * from counselor;
COUNSELOR_ID FIRST_NAME LAST_NAME PHONE
------------ -------------------- -------------------- --------------
10 Quinn Fowler (508) 244-1416
11 Elizabeth Baker (508) 243-1789
12 Darrold Cooper
104 Leslie Wright (508) 244-5586
105 Abigail Smith (508) 244-6931
106 James Sawyer (508) 247-7631
6 rows selected.