Tìm ràng buộc khoá ngoại trong Oracle

Nhiều nhà phát triển thấy rằng thật khó có thể xoá được các bản ghi trên bảng cha khi các bản ghi con tham chiếu tới qua ràng buộc khoá ngoại (tính toàn vẹn tham chiếu).

Các ràng buộc đảm bảo cho dữ liệu hợp lệ và có hiệu lực. Nếu không có ràng buộc, chúng ta chỉ lưu trữ các dữ liệu không hợp lệ.

Đối với một nhà phát triển, xác định và loại bỏ ràng buộc khoá ngoại (foreign key) là điều không mấy dễ dàng. Hầu hết các biểu đồ ER của ứng dụng không phù hợp cho các nhà phát triển. Dưới đây là bản mô tả tóm tắt về các ràng buộc khoá ngoại, tức toàn vẹn tham chiếu sẽ giúp ích cho bạn rất nhiều khi muốn xác định và loại bỏ các ràng buộc này.

Chi tiết của ba bảng được tạo ra làm ví dụ trong bài này:

Các bảng con TEMP_JP2 và TEMP_JP3 tham chiếu tới bảng cha TEMP_JP1.

Bảng cha TEMP_JP1 được tạo ra theo kiểu thêm từng hàng một.

create table temp_jp1(col1 number,col2 number);
insert into temp_jp1 values(1,2);
commit;

Bảng con TEMP_JP2 cũng được tạo tương tự. Nhưng khi cố gắng tạo một ràng buộc khoá ngoại trên bảng này thì gặp lỗi:

create table temp_jp2(col1 number);
SQL> alter table temp_jp2 add (constraint temp_jp2_fk
2 foreign key (col1) references temp_jp1(col1));
foreign key (col1) references temp_jp1(col1))
*
ERROR at line 2:
ORA-02270: no matching unique or primary key for this column-list
(Lỗi ở dòng 2:
Oracle-02270: không khớp khoá duy nhất hoặc khoá chính trong danh sách cột)

Lúc này bạn phải tạo ràng buộc khoá chính (primary key) hoặc khoá duy nhất (unique key) trên cột khoá cha thì Oracle mới cho phép tạo ràng buộc khoá ngoại trên cột khoá con. Khoá chính hoặc khoá duy nhất trên cột khoá cha không được phép có giá trị lặp (tức phải đảm bảo được tính độc nhất cho giá trị ở từng cột).

Chỉ mục (index) cho khoá chính được tạo trên bảng cha TEMP_JP1(COL1):

alter table temp_jp1 add constraint temp_jp1_pk primary key(col1);

Bây giờ, ràng buộc khoá ngoại tạo trên bảng con TEMP_JP2 được tạo thành công, sau đó là thêm vào từng hàng dữ liệu.

SQL> alter table temp_jp2 add (constraint temp_jp2_fk
2 foreign key (col1) references temp_jp1(col1));
Table altered.
insert into temp_jp2 values(1);
commit

Nhằm mục đích diễn giải ví dụ cho dễ hiểu, bảng thứ ba TEMP_JP3 được tạo:

create table temp_jp3(col2 number);

Tạo ràng buộc khoá ngoại cho bảng thứ ba TEMP_JP3 nhưng vẫn gặp lỗi:

SQL> alter table temp_jp3 add(constraint temp_jp3_fk
2 foreign key(col2) references temp_jp1(col2));
foreign key(col2) references temp_jp1(col2))
*
ERROR at line 2:
ORA-02270: no matching unique or primary key for this column-list
(Lỗi ở dòng 2:
ORA-02270: không khớp khoá duy nhất hoặc khoá chính trong danh sách cột)

Bạn phải đảm bảo chắc chắn rằng giá trị trong cột khoá cha là phân biệt và duy nhất trước khi tạo ràng buộc khoá ngoại trên cột khoá con.

Thêm vào trường chỉ mục khoá chính trên bảng cha TEMP_JP1 như sau (phải đảm bảo tính duy nhất cho giá trị cột khoá cha):

SQL> alter table temp_jp1 add constraint temp_jp1_pk primary key(col2);
alter table temp_jp1 add constraint temp_jp1_pk primary key(col2)
*
ERROR at line 1:
ORA-02260: table can have only one primary key
(Lỗi ở dòng 1:
ORA-02260: bảng chỉ được phép có một khoá chính duy nhất)

Chúng ta vẫn gặp phải lỗi. Bạn cần thêm một ràng buộc unique key (khoá đơn duy nhất) vào bảng cha TEMP_JP1.

Cột Col2, như thông báo lỗi đã chỉ ra là cột khoá chính thì không được phép có giá trị lặp. Nhưng một bảng được phép có nhiều ràng buộc khoá đơn duy nhất.

SQL> alter table temp_jp1 add constraint temp_jp1_Uk unique (col2);

Bây giờ hoàn toàn có thểm thêm ràng buộc khoá ngoại vào TEMP_JP3.

SQL> alter table temp_jp3 add(constraint temp_jp3_fk
2 foreign key(col2) references temp_jp1(col2));
Table altered.

Chèn hàng vào bảng TEMP_JP3:

SQL> insert into temp_jp3 values(1);
insert into temp_jp3 values(1)
*
ERROR at line 1:
ORA-02291: integrity constraint (JP.TEMP_JP3_FK) violated - parent key not found
(Lỗi ở dòng 1:
ORA-02291: ràng buộc toàn vẹn (JP.TEMP_JP3_FK) bị vi phạm, khoá chính không tìm thấy)

Ràng buộc toàn vẹn không cho phép chèn bản ghi con vào bảng, trừ khi một bản ghi tương ứng được tìm thấy ở bảng cha.

SQL> insert into temp_jp3 values(2);
1 row created.
SQL> commit;
Commit complete.

Về cơ bản, các ràng buộc bảo vệ và làm cho dữ liệu hợp lệ, có hiệu lực.

Một bản ghi cha có thể có nhiều bản ghi con, nhưng mỗi bản ghi con chỉ liên quan đến một bản ghi cha DUY NHẤT. Đó là lý do vì sao Oracle muốn chỉ mục PRIMARY/UNIQUE KEY phải được tạo trên cột khoá chính của bảng cha. Trên bảng con, việc tạo chỉ mục trên cột khoá con là không bắt buộc.

Trong trường hợp của chúng ta, bạn sẽ không thể sử dụng được ràng buộc khoá ngoại trên các bảng TEMP_JP2, TEMP_JP3 nếu chưa thêm ràng buộc khoá chính cho cột Col1 và ràng buộc khoá đơn duy nhất cho cột Col2 trên bảng cha TEMP_JP1.

Bây giờ chúng ta sẽ loại bỏ một số bản ghi ở bảng cha TEMP_JP1.

SQL> truncate table temp_jp1;
truncate table temp_jp1
*
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys

SQL> Delete from temp_jp1;
Delete from temp_jp1
*
ERROR at line 1:
ORA-02292: integrity constraint (JP.TEMP_JP3_FK) violated - child record
found

Bảng cha không thể bị cắt hoặc xoá khi ràng buộc khoá ngoại (tức toàn vẹn tham chiếu) đang tham chiếu tới.

Xem lại các ràng buộc trong từ điển dữ liệu với lệnh view all_constrains.

SQL> desc all_constraints
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER NOT NULL VARCHAR2(30)
CONSTRAINT_NAME NOT NULL VARCHAR2(30)
CONSTRAINT_TYPE VARCHAR2(1)
TABLE_NAME NOT NULL VARCHAR2(30)
SEARCH_CONDITION LONG
R_OWNER VARCHAR2(30)
R_CONSTRAINT_NAME VARCHAR2(30)
DELETE_RULE VARCHAR2(9)
STATUS VARCHAR2(8)
DEFERRABLE VARCHAR2(14)
DEFERRED VARCHAR2(9)
VALIDATED VARCHAR2(13)
GENERATED VARCHAR2(14)
BAD VARCHAR2(3)
RELY VARCHAR2(4)
LAST_CHANGE DATE
INDEX_OWNER VARCHAR2(30)
INDEX_NAME VARCHAR2(30)
INVALID VARCHAR2(7)
VIEW_RELATED VARCHAR2(14)

Nên nhớ rằng, khi cố gắng cắt bảng TEMP_JP1, chúng ta sẽ nhận được lỗi:

“ORA-02266: unique/primary keys in table referenced by enabled foreign keys”

Chúng ta hãy cùng xem các ràng buộc nào có trên bảng cha TEMP_JP1.

SQL> select owner,constraint_name,constraint_type,table_name,r_owner,r_constraint_name
2 from all_constraints where constraint_type in ('P','U') and table_name='TEMP_JP1';
OWNER CONSTRAINT_NAME C TABLE_NAME R_OWNER R_CONSTRAINT_NAME
---------- --------------- - ---------- ---------- --------------------
JP TEMP_JP1_PK P TEMP_JP1
JP TEMP_JP1_UK U TEMP_JP1

Đó là các ràng buộc primary/unique key.

Cột r_constraint_name (tên ràng buộc primary key, unique trên bảng cha) trong bảng ảo all_constrains được tham chiếu tới bằng constraint_name (khoá ngoại ở bảng con), khi constraint_type là ‘R’. Sử dụng định nghĩa này, chúng ta có thể tìm ra tất cả ràng buộc khoá ngoại tham chiếu tới bảng cha TEMP_JP1.

SQL> select owner,constraint_name,constraint_type,table_name,r_owner,r_constraint_name
2 from all_constraints
3 where constraint_type='R'
4 and r_constraint_name in (select constraint_name from all_constraints
5 where constraint_type in ('P','U') and table_name='TEMP_JP1');
OWNER CONSTRAINT_NAME C TABLE_NAME R_OWNER R_CONSTRAINT_NAME
---------- --------------- - ---------- ---------- --------------------
JP TEMP_JP2_FK R TEMP_JP2 JP TEMP_JP1_PK
JP TEMP_JP3_FK R TEMP_JP3 JP TEMP_JP1_UK

Đó là một truy vấn con, cung cấp DUY NHẤT kiểu ràng buộc primary key hoặc unique key với trường thẩm định điều kiện constraint_type là ‘P’ hoặc ‘U’. Các ràng buộc khoá ngoại chỉ được phép thực thi trên bảng con khi ràng buộc primary key, unique được sử dụng trên bảng cha.

Chúng ta đã xác định ràng buộc khoá ngoại nào đang tham chiếu tới bảng cha TEMP_JP1.

Bây giờ sẽ là loại bỏ các ràng buộc primary key, unique trên bảng.

SQL> alter table temp_jp1 disable constraint temp_jp1_pk;
alter table temp_jp1 disable constraint temp_jp1_pk
*
ERROR at line 1:
ORA-02297: cannot disable constraint (JP.TEMP_JP1_PK) - dependencies exist
(Lỗi ở dòng 1:
ORA-02297: không thể loại bỏ ràng buộc (JP.TEMP_JP1_PK), các phụ thuộc đang tồn tại)

Chúng ta cần loại bỏ ràng buộc unique key trên bảng cha trước.

SQL> alter table temp_jp1 disable constraint temp_jp1_uk;
alter table temp_jp1 disable constraint temp_jp1_uk
*
ERROR at line 1:
ORA-02297: cannot disable constraint (JP.TEMP_JP1_UK) - dependencies exist
(Lỗi ở dòng 1:
ORA-02297: không thể loại bỏ ràng buộc (JP.TEMP_JP1_PK), các phụ thuộc đang tồn tại)

Có thể thấy, khi nào các ràng buộc khoá ngoại trên bảng con vẫn còn được sử dụng, dữ liệu ở bảng cha sẽ được bảo vệ.

Nếu cần thao tác dữ liệu trên bảng cha TEMP_JP1, đầu tiên phải loại bỏ ràng buộc khoá ngoại trên bảng con đang tham chiếu tới nó.

Thực hiện loại bỏ ràng buộc khoá ngoại trên bảng con như sau (được xác định từ truy vấn trước chúng ta đã thực hiện):

SQL> alter table temp_jp2 disable constraint temp_jp2_fk;
Table altered.
SQL> alter table temp_jp3 disable constraint temp_jp3_fk;
Table altered.
SQL> truncate table temp_jp1;
Table truncated.

Chúng ta có thể cắt bảng cha sau khi loại bỏ ràng buộc khoá ngoại ở bảng con đang tham chiếu tới bảng cha.

Một script (kịch bản) để xác định và loại bỏ các ràng buộc khoá ngoại trên bảng con xây dựng sẵn.

SQL> select 'alter table '||a.owner||'.'||a.table_name||
2 ' disable constraint '||a.constraint_name||';'
3 from all_constraints a, all_constraints b
4 where a.constraint_type = 'R'
5 and a.r_constraint_name = b.constraint_name
6 and a.r_owner = b.owner
7 and b.table_name = 'TEMP_JP1';
'ALTERTABLE'||A.OWNER||'.'||A.TABLE_NAME||'DISABLECONSTRAINT'||A.CONSTRAINT_NAME
--------------------------------------------------------------------------------
alter table JP.TEMP_JP3 disable constraint TEMP_JP3_FK;
alter table JP.TEMP_JP2 disable constraint TEMP_JP2_FK;

Hy vọng bài giới thiệu này cung cấp cho bạn một bức tranh rõ ràng, mạch lạc về các ràng buộc khoá ngoại và cách xử lý vấn đề với chúng như thế nào khi thao tác dữ liệu trên bảng cha.

Bạn có thể tham khảo thêm:

SQL> INSERT INTO TEMP_JP1 VALUES(2,NULL);
1 row created.
SQL> INSERT INTO TEMP_JP3 VALUES(NULL);
1 row created.
SQL> INSERT INTO TEMP_JP3 VALUES(NULL);
1 row created.
SQL> insert into temp_jp1 values(null,null);
insert into temp_jp1 values(null,null)
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("JP"."TEMP_JP1"."COL1")

Chú ý là: cả chỉ mục khoá primary key và unique đều không được phép có giá trị lặp trong cột. Tất cả chỉ mcuj khoá chính đề phải là duy nhất. Một bảng chỉ được phép có một chỉ mục primary key, nhưng có thể có nhiều chỉ mục unique key. Điểm khác nhau chính giữa chúng là bạn không thể chèn giá trị NULL vào ràng buộc khoá chính trong khi có thể chèn vào ràng buộc unique key.

Do đó, nếu khoá cha trong bảng tra được tạo chỉ mục với ràng buộc khoá unique key, bạn nên sử dụng thêm ràng buộc NOT NULL cho cột khoá.

SQL> alter table temp_jp1 modify(col2 not null);
Table altered.
SQL> insert into temp_jp1 values(2,null);
insert into temp_jp1 values(2,null)
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("JP"."TEMP_JP1"."COL2")