Nhieu nha
phat trien thay rang that kho co the xoa duoc cac ban ghi
tren bang cha khi cac ban ghi con tham chieu toi qua rang
buoc khoa ngoai (tinh toan ven tham chieu).
Cac rang buoc dam bao cho du lieu hop le va co hieu luc. Neu
khong co rang buoc, chung ta chi luu tru cac du lieu khong
hop le.
Doi voi mot nha phat trien, xac dinh va loai bo rang buoc
khoa ngoai (foreign key) la dieu khong may de dang. Hau het
cac bieu do ER cua ung dung khong phu hop cho cac nha phat
trien. Duoi day la ban mo ta tom tat ve cac rang buoc khoa
ngoai, tuc toan ven tham chieu se giup ich cho ban rat nhieu
khi muon xac dinh va loai bo cac rang buoc nay.
Chi tiet cua ba bang duoc tao ra lam vi du trong bai
nay:
| TEMP_JP1 |
BANG CHA |
| TEMP_JP2 |
BANG CON |
| TEMP_JP3 |
BANG CON |
Cac bang con TEMP_JP2 va TEMP_JP3
tham chieu toi bang cha TEMP_JP1.
Bang cha TEMP_JP1 duoc tao ra theo kieu them tung hang mot.
create table temp_jp1(col1 number,col2 number);
insert into temp_jp1 values(1,2);
commit;
Bang con TEMP_JP2 cung duoc tao tuong tu.
Nhung khi co gang tao mot rang buoc khoa ngoai tren bang nay
thi gap loi:
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
(Loi o dong 2:
Oracle-02270: khong khop khoa duy nhat hoac khoa chinh
trong danh sach cot)
Luc nay ban phai tao rang buoc khoa chinh
(primary key) hoac khoa duy nhat (unique key) tren cot khoa
cha thi Oracle moi cho phep tao rang buoc khoa ngoai tren
cot khoa con. Khoa chinh hoac khoa duy nhat tren cot khoa
cha khong duoc phep co gia tri lap (tuc phai dam bao duoc
tinh doc nhat cho gia tri o tung cot).
Chi muc (index) cho khoa chinh duoc tao tren bang cha
TEMP_JP1(COL1):
alter table temp_jp1 add constraint temp_jp1_pk primary
key(col1);
Bay gio, rang buoc khoa ngoai tao tren
bang con TEMP_JP2 duoc tao thanh cong, sau do la them vao
tung hang du lieu.
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
Nham muc dich dien giai vi du cho de
hieu, bang thu ba TEMP_JP3 duoc tao:
create table temp_jp3(col2 number);
Tao rang buoc khoa ngoai cho bang thu ba
TEMP_JP3 nhung van gap loi:
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
(Loi o dong 2:
ORA-02270: khong khop khoa duy nhat hoac khoa chinh
trong danh sach cot)
Ban phai dam bao chac chan rang gia tri
trong cot khoa cha la phan biet va duy nhat truoc khi tao
rang buoc khoa ngoai tren cot khoa con.
Them vao truong chi muc khoa chinh tren bang cha TEMP_JP1
nhu sau (phai dam bao tinh duy nhat cho gia tri cot khoa
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
(Loi o dong 1:
ORA-02260: bang chi duoc phep co mot khoa chinh duy nhat)
Chung ta van gap phai loi. Ban can them
mot rang buoc unique key (khoa don duy nhat) vao bang cha
TEMP_JP1.
Cot Col2, nhu thong bao loi da chi ra la cot khoa chinh thi
khong duoc phep co gia tri lap. Nhung mot bang duoc phep co
nhieu rang buoc khoa don duy nhat.
SQL> alter table temp_jp1 add constraint temp_jp1_Uk
unique (col2);
Bay gio hoan toan co them them rang buoc
khoa ngoai vao TEMP_JP3.
SQL> alter table temp_jp3 add(constraint temp_jp3_fk
2 foreign key(col2) references temp_jp1(col2));
Table altered.
Chen hang vao bang 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
(Loi o dong 1:
ORA-02291: rang buoc toan ven (JP.TEMP_JP3_FK) bi vi
pham, khoa chinh khong tim thay)
Rang buoc toan ven khong cho phep chen
ban ghi con vao bang, tru khi mot ban ghi tuong ung duoc tim
thay o bang cha.
SQL> insert into temp_jp3 values(2);
1 row created.
SQL> commit;
Commit complete.
Ve co ban, cac rang buoc bao ve va lam
cho du lieu hop le, co hieu luc.
Mot ban ghi cha co the co nhieu ban ghi con, nhung moi ban
ghi con chi lien quan den mot ban ghi cha DUY NHAT. Do la ly
do vi sao Oracle muon chi muc PRIMARY/UNIQUE KEY phai duoc
tao tren cot khoa chinh cua bang cha. Tren bang con, viec
tao chi muc tren cot khoa con la khong bat buoc.
Trong truong hop cua chung ta, ban se khong the su dung duoc
rang buoc khoa ngoai tren cac bang TEMP_JP2, TEMP_JP3 neu
chua them rang buoc khoa chinh cho cot Col1 va rang buoc
khoa don duy nhat cho cot Col2 tren bang cha TEMP_JP1.
Bay gio chung ta se loai bo mot so ban ghi o bang 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
Bang cha khong the bi cat hoac xoa khi
rang buoc khoa ngoai (tuc toan ven tham chieu) dang tham
chieu toi.
Xem lai cac rang buoc trong tu dien du lieu voi lenh 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)
Nen nho rang, khi co gang cat bang
TEMP_JP1, chung ta se nhan duoc loi:
“ORA-02266: unique/primary keys in table referenced by
enabled foreign keys”
Chung ta hay cung xem cac rang buoc nao
co tren bang 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
Do la cac rang buoc primary/unique key.
Cot r_constraint_name (ten rang buoc primary key, unique
tren bang cha) trong bang ao all_constrains duoc tham chieu
toi bang constraint_name (khoa ngoai o bang con), khi
constraint_type la ‘R’. Su dung dinh nghia nay, chung ta co
the tim ra tat ca rang buoc khoa ngoai tham chieu toi bang
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
Do la mot truy van con, cung cap DUY NHAT
kieu rang buoc primary key hoac unique key voi truong tham
dinh dieu kien constraint_type la ‘P’ hoac ‘U’. Cac rang
buoc khoa ngoai chi duoc phep thuc thi tren bang con khi
rang buoc primary key, unique duoc su dung tren bang cha.
Chung ta da xac dinh rang buoc khoa ngoai nao dang tham
chieu toi bang cha TEMP_JP1.
Bay gio se la loai bo cac rang buoc primary key, unique tren
bang.
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
(Loi o dong 1:
ORA-02297: khong the loai bo rang buoc (JP.TEMP_JP1_PK),
cac phu thuoc dang ton tai)
Chung ta can loai bo rang buoc unique key
tren bang cha truoc.
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
(Loi o dong 1:
ORA-02297: khong the loai bo rang buoc (JP.TEMP_JP1_PK),
cac phu thuoc dang ton tai)
Co the thay, khi nao cac rang buoc khoa
ngoai tren bang con van con duoc su dung, du lieu o bang cha
se duoc bao ve.
Neu can thao tac du lieu tren bang cha TEMP_JP1, dau tien
phai loai bo rang buoc khoa ngoai tren bang con dang tham
chieu toi no.
Thuc hien loai bo rang buoc khoa ngoai tren bang con nhu sau
(duoc xac dinh tu truy van truoc chung ta da thuc hien):
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.
Chung ta co the cat bang cha sau khi loai
bo rang buoc khoa ngoai o bang con dang tham chieu toi bang
cha.
Mot script (kich ban) de xac dinh va loai bo cac rang buoc
khoa ngoai tren bang con xay dung san.
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 vong bai gioi thieu nay cung cap cho
ban mot buc tranh ro rang, mach lac ve cac rang buoc khoa
ngoai va cach xu ly van de voi chung nhu the nao khi thao
tac du lieu tren bang cha.
Ban co the tham khao them:
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")
Chu y la: ca chi muc khoa primary key va
unique deu khong duoc phep co gia tri lap trong cot. Tat ca
chi mcuj khoa chinh de phai la duy nhat. Mot bang chi duoc
phep co mot chi muc primary key, nhung co the co nhieu chi
muc unique key. Diem khac nhau chinh giua chung la ban khong
the chen gia tri NULL vao rang buoc khoa chinh trong khi co
the chen vao rang buoc unique key.
Do do, neu khoa cha trong bang tra duoc tao chi muc voi rang
buoc khoa unique key, ban nen su dung them rang buoc NOT
NULL cho cot khoa.
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")
T.Thu (Theo Databasejournal)
