To insert a record in to database table “A” (where auto
generated sequence number is primary key) and retrieve that sequence no and
insert a record into table B ( where foreign key is referring to table “A”
primary key) .
Ex:
CREATE TABLE A (
priColumn
INT NOT NULL,
genColumn
VARCHAR(20),
PRIMARY KEY(priColumn)
);
CREATE TABLE B (
priColumn
INT NOT NULL,
genColumn
VARCHAR(30),
FOREIGN KEY (priColumn) REFERENCES
A(priColumn)
);
To handle this
scenario in MySQL
1.
To generate the sequence number in MySQL , you no
need to execute separate DDL’s like “create sequence” ..etc, instead you can
use “AUTO_INCREMENT” on table ddl
CREATE
TABLE A (
priColumn INT NOT NULL AUTO_INCREMENT,
genColumn VARCHAR(20),
PRIMARY KEY(priColumn)
);
2.
Every time you insert record into table A,
priColumn is filled automatically.
3.
Use the following select statement to retrieve
sequence number (Auto Increment) value generated after insert statement .
SELECT
LAST_INSERT_ID()
To handle this
scenario in DB2
1.
There are different ways you can generate the
unique values in the DB2.
a.
Using “generate_unique()” function
INSERT INTO A VALUES (GENERATE_UNIQUE(),
'general column1')
b.
Using Identity column
CREATE TABLE A (
priColumn
INT NOT NULL GENERATED ALWAYS AS IDENTITY
(START WITH 1, INCREMENT BY 1, NO
CACHE),
genColumn
VARCHAR(20),
CONSTRAINT
X_priColumn PRIMARY KEY(priColumn)
);
You can
simply insert row like and generates unique value in the “priColumn”
INSERT INTO A VALUES (‘general column1');
2.
To retrieve the value inserted in using identity
column, DB2 has function called IDENTITY_VAL_LOCAL , it retrieves the last
inserted sequence id.
You
can now retrieve the last inserted value in table A and put in table B ,
INSERT INTO B
VALUES (identity_val_local(), ‘general column1');
Resources
