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

Nhiều nhà lớn mạnh 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 vẹn toàn tham chiếu).

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

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

Cụ thể của ba bảng được tạo thành làm chẳng hạn trong bài này:

TEMP_JP1BẢNG CHATEMP_JP2BẢNG CONTEMP_JP3BẢNG CON

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

Bảng cha TEMP_JP1 được tạo thành 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 đương. Nhưng khi phấn đấu 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);
SQLvàgt; 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 mục cột)

Bây giờ 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 bảo đảm đượ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);

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

SQLvàgt; 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

Có mục đích diễn đạt chẳng hạn 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:

SQLvàgt; 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 mục cột)

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

Xem Thêm  ASP.NET 4 and Visual Studio 2010 Web Development Overview - postback là gì

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

SQLvàgt; 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)

Tất cả 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ư nhắc nhở lỗi đã nêu 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.

SQLvàgt; alter table temp_jp1 add constraint temp_jp1_Uk unique (col2);

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

SQLvàgt; 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:

SQLvàgt; 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 vẹn toàn (JP.TEMP_JP3_FK) bị vi phạm, khoá chính không tìm ra)

Ràng buộc vẹn toà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 ra ở bảng cha.

SQLvàgt; insert into temp_jp3 values(2);
1 row created.
SQLvàgt; commit;
Commit complete.

Về căn bản, các ràng buộc bảo vệ & khiến 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. Này là nguyên nhân tại sao Oracle mong 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 các bạn, bạn sẽ chẳ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 & ràng buộc khoá đơn duy nhất cho cột Col2 trên bảng cha TEMP_JP1.

Hiện giờ tất cả chúng ta sẽ loại bỏ một số bản ghi ở bảng cha TEMP_JP1.

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

SQLvàgt; 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 chẳng thể bị cắt hoặc xoá khi ràng buộc khoá ngoại (tức vẹn toàn tham chiếu) đang tham chiếu tới.

Xem Thêm  Cách tạo một bảng từ một bảng khác trong SQL - tạo bảng từ một bảng khác trong máy chủ sql với dữ liệu

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

SQLvàgt; 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 phấn đấu cắt bảng TEMP_JP1, tất cả chúng ta sẽ thu được lỗi:

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

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

SQLvàgt; select owner,constraint_name,constraint_type,table_name,r_owner,r_constraint_name
2 from all_constraints where constraint_type in (‘Ρ’,’ᑗ’) and table_name=’TEMP_JP1′;
OWNER CONSTRAINT_NAME ₵ TABLE_NAME R_OWNER R_CONSTRAINT_NAME
———- ————— – ———- ———- ——————–
JP TEMP_JP1_PK Ρ TEMP_JP1
JP TEMP_JP1_UK ᑗ TEMP_JP1

Này 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 khái niệm này, tất cả chúng ta có thể tìm thấy toàn bộ ràng buộc khoá ngoại tham chiếu tới bảng cha TEMP_JP1.

SQLvàgt; select owner,constraint_name,constraint_type,table_name,r_owner,r_constraint_name
2 from all_constraints
3 where constraint_type=’Ŕ’
4 and r_constraint_name in (select constraint_name from all_constraints
5 where constraint_type in (‘Ρ’,’ᑗ’) and table_name=’TEMP_JP1′);
OWNER CONSTRAINT_NAME ₵ TABLE_NAME R_OWNER R_CONSTRAINT_NAME
———- ————— – ———- ———- ——————–
JP TEMP_JP2_FK Ŕ TEMP_JP2 JP TEMP_JP1_PK
JP TEMP_JP3_FK Ŕ TEMP_JP3 JP TEMP_JP1_UK

Này là một truy vấn con, phân phối DUY NHẤT kiểu ràng buộc primary key hoặc unique key với trường giá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.

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

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

SQLvàgt; 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 chuyên mục bỏ ràng buộc (JP.TEMP_JP1_PK), các lệ thuộc đang tồn tại)

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

SQLvàgt; 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 chuyên mục bỏ ràng buộc (JP.TEMP_JP1_PK), các lệ thuộc đang tồn tại)

Có thể thấy, lúc 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ệ.

Xem Thêm  Cách xác định một hàm trong Python - cách sử dụng một hàm đã xác định trong python

Nếu cần thao tác dữ liệu trên bảng cha TEMP_JP1, trước hết 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ận từ truy vấn trước tất cả chúng ta đã thực hiện):

SQLvàgt; alter table temp_jp2 disable constraint temp_jp2_fk;
Table altered.
SQLvàgt; alter table temp_jp3 disable constraint temp_jp3_fk;
Table altered.
SQLvàgt; truncate table temp_jp1;
Table truncated.

Tất cả chúng ta có thể cắt bảng cha sau khoảng thời gian 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 (cốt truyện) để xác nhận & loại bỏ các ràng buộc khoá ngoại trên bảng con xây dựng sẵn.

SQLvàgt; select ‘alter table ‘||α.owner||’.’||α.table_name||
2 ‘ disable constraint ‘||α.constraint_name||’;’
3 from all_constraints α, all_constraints ɓ
4 where α.constraint_type = ‘Ŕ’
5 and α.r_constraint_name = ɓ.constraint_name
6 and α.r_owner = ɓ.owner
7 and ɓ.table_name = ‘TEMP_JP1’;
‘ALTERTABLE’||?.OWNER||’.’||?.TABLE_NAME||’DISABLECONSTRAINT’||?.CONSTRAINT_NAME
——————————————————————————–
alter table JP.TEMP_JP3 disable constraint TEMP_JP3_FK;
alter table JP.TEMP_JP2 disable constraint TEMP_JP2_FK;

Ước ao bài giới thiệu này phân phối cho bạn một bức họa rõ ràng, mạch lạc về các ràng buộc khoá ngoại & cách giải quyết vấn đề với chúng như vậy nào khi thao tác dữ liệu trên bảng cha.

Bạn có thể đọc thêm:

SQLvàgt; INSERT INTO TEMP_JP1 VALUES(2,NULL);
1 row created.
SQLvàgt; INSERT INTO TEMP_JP3 VALUES(NULL);
1 row created.
SQLvàgt; INSERT INTO TEMP_JP3 VALUES(NULL);
1 row created.
SQLvàgt; 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″)

Lưu ý là: cả chỉ mục khoá primary key & unique đều không được phép có giá trị lặp trong cột. Toàn bộ 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 trung tâm chúng là bạn chẳ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.

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

SQLvàgt; alter table temp_jp1 modify(col2 not null);
Table altered.
SQLvàgt; 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″)

Viết một bình luận