Sequence problem

[areya]

I'm trying to learn about SEQUENCE and I came accross this example:

INSERT INTO student (studentID, name, address)
values (sID.NextVal, 'Albert', '123, Sage Ave., NY. 12180');

the column name used for the SEQUENCE's NEXTVAL in this example is studentID. Where is studentID defined or should it be defined?

As you can see studentID is not used in the CREATE statement below so where is it coming from?

CREATE SEQUENCE sID
INCREMENT BY 1
START WITH 100
ORDER;

Thanks for your assistance! Andy

[debasisdas]

I'm trying to learn about SEQUENCE and I came accross this example:

INSERT INTO student (studentID, name, address)
values (sID.NextVal, 'Albert', '123, Sage Ave., NY. 12180');

the column name used for the SEQUENCE's NEXTVAL in this example is studentID. Where is studentID defined or should it be defined?

As you can see studentID is not used in the CREATE statement below so where is it coming from?

CREATE SEQUENCE sID
INCREMENT BY 1
START WITH 100
ORDER;

Thanks for your assistance! Andy

Hi Andy

studentID is the name of the column in the table student.
that is not part of the sequence.

from the sequence it is trying to insert value to the column using sequencename.nextval

[amitpatel66]

I'm trying to learn about SEQUENCE and I came accross this example:

INSERT INTO student (studentID, name, address)
values (sID.NextVal, 'Albert', '123, Sage Ave., NY. 12180');

the column name used for the SEQUENCE's NEXTVAL in this example is studentID. Where is studentID defined or should it be defined?

As you can see studentID is not used in the CREATE statement below so where is it coming from?

CREATE SEQUENCE sID
INCREMENT BY 1
START WITH 100
ORDER;

Thanks for your assistance! Andy

Andy,

SEQUENCE will make sure that every time a new record is inserted in table STUDENTS, the STUDENTID value will be the next value of the sequence.

SEQUENCES are used to manage the values of PRIMARY KEY column where in DUPLICATES are not allowed.

[areya]

Andy,

SEQUENCE will make sure that every time a new record is inserted in table STUDENTS, the STUDENTID value will be the next value of the sequence.

SEQUENCES are used to manage the values of PRIMARY KEY column where in DUPLICATES are not allowed.

*******************************
You mentioned that SEQUENCES are used to manage the values of PRIMARY KEY column. Can you elaborate? If I create a new column for the NEXTVAL to be inserted into, because I'm trying to allow duplicates, do I make that column part of the PRIMARY KEY to insure that duplicates are inserted. Appreciate your help! Andy

[amitpatel66]

*******************************
You mentioned that SEQUENCES are used to manage the values of PRIMARY KEY column. Can you elaborate? If I create a new column for the NEXTVAL to be inserted into, because I'm trying to allow duplicates, do I make that column part of the PRIMARY KEY to insure that duplicates are inserted. Appreciate your help! Andy

Andy,

If you make a column PRIMARY KEY, then you cannot insert duplicate values to that column.

For Eg: I have a table EMP that cotains:
Empno, Ename,Address,Hiredate,Salary.

I make Empno as PRIMARY KEY. So now no two employees will have same EMPNO.

And I will make use of sequence to AUTOGENERATE the value of Empno for every new employee details inserted in to EMP table.

INSERT INTO emp VALUES(sid.NEXTVAL,'AAAA','20 ABC Road',SYSDATE,10000);
 

So in the above INSERT statement, the EMPNO will be autogenerated starting from 1,2,3 and so on.