Thiết lập GENERATED ALWAYS / BY DEFAULT – Tự động lưu trữ các giá trị số liên tiếp trong một cột

Khi bạn tạo một bảng, bạn có thể đặt GENERATED ALWAYS AS IDENTITY hoặc GENERATED BY DEFAULT AS IDENTITY cho một cột để lưu trữ các số tăng tự động trong cột.

Cú pháp lệnh:

CREATE TABLE [ IF NOT EXISTS ] table_name (
  column_name data_type
  GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [, ... ]
)

Cho dù là GENERATED ALWAYS hay GENERATED BY DEFAULT được đặt, thì các giá trị số liên tiếp cũng đều sẽ được tự động lưu trữ cho cột, nhưng nếu GENERATED ALWAYS được set thì sẽ xảy ra lỗi nếu dữ liệu được thêm bằng cách chỉ định giá trị cho cột mục tiêu và không thể thêm dữ liệu. Ngược lại, nếu GENERATED BY DEFAULT được đặt và dữ liệu được thêm bằng cách chỉ định một giá trị trong cột mục tiêu, thì giá trị đã chỉ định sẽ được ưu tiên và dữ liệu sẽ được thêm vào.

Đầu tiên, sẽ tạo bảng friends1 với GENERATED ALWAYS AS IDENTITY được đặt cho cột của bảng trong lược đồ myschema1 của cơ sở dữ liệu mydb:

CREATE TABLE myschema1.friends1 (
  id integer GENERATED ALWAYS AS IDENTITY, 
  name varchar(10)
);

Option GENERATED ALWAYS AS IDENTITY được đặt cho cột id.

Xem thông tin cột của bảng friends1 đã tạo:

\d myschema1.friends1

Bây giờ, thêm 3 dữ liệu mà không chỉ định giá trị của cột id:

INSERT INTO myschema1.friends1 (name) VALUES 
  ('Minh Hoang'), 
  ('Blog'), 
  ('minhhn.com');

Khi truy xuất dữ liệu từ bảng friends1 thì giá trị của cột id sẽ tự động lưu trữ các số 1, 2 và 3 tăng liên tiếp.

Tiếp theo, thử thêm dữ liệu bằng cách chỉ định giá trị cụ thể cho cột id:

INSERT INTO myschema1.friends1 values (4, 'Nagoya');

Vì đã thiết lập GENERATED ALWAYS AS IDENTITY cho cột id, nên khi thêm dữ liệu mà có chỉ định giá trị cụ thể cho cột id sẽ phát sinh lỗi như hình trên và thêm dữ liệu bị thất bại.

Trong trường hợp này, nếu muốn thêm dữ liệu mà có chỉ định giá trị cụ thể cho cột id thì khi INSERT cần PHẢI có thêm chỉ định OVERRIDING SYSTEM VALUE.

INSERT INTO myschema1.friends1 OVERRIDING SYSTEM VALUE VALUES 
  (4, 'Nagoya'), 
  (10, 'Tokyo');

Lần này thì đã có thể thêm dữ liệu mà không bị lỗi. Giá trị đã chỉ định được lưu trữ trong cột id của dữ liệu đã thêm.

※Vì chỉ định GENERATED ALWAYS AS IDENTITY sẽ thiết lập cột id tự động ràng buộc NOT NULL, chứ không ràng buộc PRIMARY KEY nên hoàn toàn có thể thêm giá trị của cột id giống nhau:

INSERT INTO myschema1.friends1 OVERRIDING SYSTEM VALUE VALUES (10, 'Tokyo');

Phần này sẽ thử thiết lập GENERATED BY DEFAULT AS IDENTITY.

Tương tự như trên, chúng ta tạo bảng friends2 với GENERATED BY DEFAULT AS IDENTITY được đặt cho cột của bảng trong lược đồ myschema1 của cơ sở dữ liệu mydb:

CREATE TABLE myschema1.friends2 (
  id integer GENERATED BY DEFAULT AS IDENTITY, 
  name varchar(10)
);

Option GENERATED BY DEFAULT AS IDENTITY được đặt cho cột id.

Xem thông tin cột của bảng friends2 đã tạo:

\d myschema1.friends2

Bây giờ, thêm 3 dữ liệu mà không chỉ định giá trị của cột id:

INSERT INTO myschema1.friends2 (name) VALUES 
  ('Minh Hoang'), 
  ('Blog'), 
  ('minhhn.com');

Khi truy xuất dữ liệu từ bảng friends2 thì giá trị của cột id sẽ tự động lưu trữ các số 1, 2 và 3 tăng liên tiếp.

Tiếp theo, thử thêm dữ liệu bằng cách chỉ định giá trị cụ thể cho cột id:

INSERT INTO myschema1.friends2 values (12, 'Nagoya');

Vì đã thiết lập GENERATED BY DEFAULT AS IDENTITY cho cột id, nên khi thêm dữ liệu mà có chỉ định giá trị cụ thể cho cột id sẽ KHÔNG phát sinh lỗi và thêm dữ liệu thành công.

※Nếu bạn thêm dữ liệu cho cột đang được set GENERATED BY DEFAULT AS IDENTITY có chỉ định giá trị cụ thể, thì bạn có thể bỏ qua giá trị đã chỉ định và lưu trữ giá trị được tăng tự động tiếp theo khi thực hiện lệnh INSERT bằng cách chỉ định OVERRIDING USER VALUE và thực thi:

INSERT INTO myschema1.friends2 OVERRIDING USER VALUE VALUES 
  (58, 'Nagoya'), 
  (100, 'Tokyo'), 
  (17, 'Osaka'), 
  (39, 'Sapporo');

Giá trị của cột id của dữ liệu được thêm vào không phải là giá trị được chỉ định trong lệnh INSERT, mà là giá trị được tạo tự động tăng lên 1.

Ở các ví dụ trên, chúng ta thấy dù chỉ định GENERATED ALWAYS AS IDENTITY hay GENERATED BY DEFAULT AS IDENTITY đi chăng nữa thì khi thêm dữ liệu giá trị chỉ tự động tăng liên tục lên 1 đơn vị. Và bạn có thể tùy chỉnh số đơn vị của mỗi lần tăng lên, không nhất thiết mỗi lần chỉ tăng thêm 1 đơn vị, bạn cũng có thể tùy chỉnh giá trị số bắt đầu bằng cách sử dụng cú pháp lệnh như sau:

CREATE TABLE [ IF NOT EXISTS ] table_name (
  column_name data_type
  GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY ( sequence_options )
)

Tùy chọn gia tăng/giảm INCREMENT BY number. Giá trị tiếp theo được tăng hoặc giảm theo giá trị được chỉ định bởi number và number có thể nhận giá trị âm cũng như giá trị dương.

Để minh họa, chúng ta sẽ tạo bảng friends3 trong lược đồ myschema1 của cơ sở dữ liệu mydb:

CREATE TABLE myschema1.friends3 (
  id integer GENERATED BY DEFAULT AS IDENTITY (INCREMENT BY 5), 
  name varchar(10)
);

thêm 3 dữ liệu vào bảng friends3:

INSERT INTO myschema1.friends3(name) VALUES 
  ('Minh Hoang'), 
  ('Blog'), 
  ('minhhn.com');

Khi truy xuất dữ liệu từ bảng friends3, chúng ta có thể thấy rằng cột id lưu trữ các giá trị 1, 6, 11 và được tăng thêm 5 như được chỉ định bởi tùy chọn (INCREMENT BY 5).

Tiếp theo là tùy chọn START WITH start_number thiết lập số thứ tự bắt đầu của dãy số. Giá trị được đặt tự động bắt đầu từ giá trị được chỉ định khi bắt đầu.

Để minh họa, chúng ta sẽ tạo bảng friends4 trong lược đồ myschema1 của cơ sở dữ liệu mydb:

CREATE TABLE myschema1.friends4 (
  id integer GENERATED BY DEFAULT AS IDENTITY (START WITH 20), 
  name varchar(10)
);

thêm 3 dữ liệu vào bảng friends4:

INSERT INTO myschema1.friends4(name) VALUES 
  ('Minh Hoang'), 
  ('Blog'), 
  ('minhhn.com');

Khi truy xuất dữ liệu từ bảng friends4, chúng ta có thể thấy rằng cột id lưu trữ các giá trị 20, 21, 22 và số được lưu trữ tự động bắt đầu từ 20 được chỉ định bởi tùy chọn (START WITH 20).

Khi đặt nhiều tùy chọn trình tự, hãy viết chúng liên tiếp mà không phân tách chúng bằng dấu phẩy.

Ví dụ: để chỉ định cả tùy chọn INCREMENT BY và tùy chọn START WITH thì sẽ mô tả như sau:

CREATE TABLE myschema1.friends5 (
  id integer GENERATED BY DEFAULT AS IDENTITY (INCREMENT BY 5 START WITH 20), 
  name varchar(10)
);