Home » SQL & PL/SQL » SQL & PL/SQL » referential integrity constraint question
referential integrity constraint question [message #219054] Mon, 12 February 2007 13:22 Go to next message
kregan77
Messages: 21
Registered: January 2007
Junior Member
ALTER TABLE TABLE1 ADD CONSTRAINT TABLE1_TABLE2 FOREIGN KEY (NOT_UNIQUE_TABLE1_COLUMN) REFERENCING TABLE2 (NOT_UNIQUE_TABLE2_COLUMN);


table1
NOT_UNIQUE_TABLE1_COLUMN
1
1
1
2
2
3
3
3


table2
NOT_UNIQUE_TABLE2_COLUMN
1
1
1
1
1
1
1
2
2
2
2
2
2


ORA-02270: no matching unique or primary key for this column-list



I'm trying to maintain the integrity of TABLE2 so that a delete statement will fail when there are matching entries in TABLE1. Is it possible to do this with non-pk columns? Forgive me if this is too n00bish of a question for this forum and let me know hwere I should post instead.

If I can't do it with a real constraint is it possible to implement a check in a before delete trigger that will result in the deletion not being executed?

Thanks!

[Updated on: Mon, 12 February 2007 13:23]

Report message to a moderator

Re: referential integrity constraint question [message #219063 is a reply to message #219054] Mon, 12 February 2007 16:04 Go to previous message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
Logically, in order to have a foreign key, you need a primary key in another table for it to reference. So you could have a table1 with a column that references table2, but that column in table2 needs to be unique.

And if that is the case, then by default oracle won't let you delete a record from table2 if there are records in table1 that point to it.

Your example and your names are kinda backwards from the way I normally think of it, because I typically think of table1 as being the parent.

Crudely (quickly thrown together):

MYDBA@orcl > create table parent(pid number primary key);

Table created.

MYDBA@orcl > create table child(cid number primary key, pid references parent);

Table created.

MYDBA@orcl > insert into parent values (1);

1 row created.

MYDBA@orcl > insert into parent values (2);

1 row created.

MYDBA@orcl > insert into parent values (3);

1 row created.

MYDBA@orcl > insert into child values (1, 1);

1 row created.

MYDBA@orcl > insert into child values (2,3);

1 row created.

MYDBA@orcl > insert into child values (3,3);

1 row created.

MYDBA@orcl > commit;

Commit complete.

MYDBA@orcl > delete from parent where pid = 1;
delete from parent where pid = 1
*
ERROR at line 1:
ORA-02292: integrity constraint (MYDBA.SYS_C006688) violated - child record found


MYDBA@orcl > delete from parent where pid = 2;

1 row deleted.

MYDBA@orcl > commit;

Commit complete.

MYDBA@orcl >

Previous Topic: Handle ORA 28002 Password expires in X days
Next Topic: date function
Goto Forum:
  


Current Time: Sat Dec 10 16:24:50 CST 2016

Total time taken to generate the page: 0.22190 seconds