Can adding FK or PK constraints invalidate something?

Question

I have a couple of tables with primary-foreign key relations but those constraints do not actually exist. Now I want to add them with an alter table statement.

Will those commands cause any object that depends on the tables to become invalid?

Thanks.

Answer 1

This is a good question. Let's poke the database and see. Here's the set up:

SQL> create table p23 (id number not null, col1 varchar2(10));

Table created.

SQL> create table c23 (id number not null, p_id number not null, col1 varchar2(10));

Table created.

SQL> create or replace procedure tst23
  2  is
  3  begin
  4    insert into p23 values (1, 'ABC');
  5    insert into c23 values (11, 1, 'DEF');
  6  end;
  7  /

Procedure created.

SQL> select status from user_objects where object_name = 'TST23';

STATUS
-------
VALID

SQL> 

Everything is copacetic. Now we'll add some constraints.

SQL> alter table p23 add constraint p23_pk primary key (id);

Table altered.

SQL> select status from user_objects where object_name = 'TST23';

STATUS
-------
VALID

SQL> alter table c23 add constraint c23_p23_fk 
  2      foreign key (p_id) references p23;

Table altered.

SQL> select status from user_objects where object_name = 'TST23';

STATUS
-------
VALID

SQL>

All is still cool. But when we change a table's structure this happens...

SQL> alter table p23 add col2 date;

Table altered.

SQL> select status from user_objects where object_name = 'TST23';

STATUS
-------
INVALID

SQL> 

... which is exactly what we would want.

Note that I ran these tests on 11gR2. Oracle introduced fine-grained dependency tracking in 11g, which made programmatic objects more robust when we run DDL on their dependencies. Find out more. So the outcome might be different in earlier versions. It pays to test.

---

" what is the reason for the procedure to become invalid?"

Structural changes such as adding, modifying or dropping a column potentially have an impact on referencing objects. The procedure had insert statements which didn't specify target columns. So adding a column introduced an ORA-00947: not enough values bug.

But suppose we employed good practice and specified columns?

SQL> create or replace procedure tst23
  2  is
  3  begin
  4      insert into p23 (id, col1) values (1, 'ABC');
  5  end;
  6  /

Procedure created.

SQL> select status from user_objects where object_name = 'TST23';

STATUS
-------
VALID

SQL> alter table p23 add col4 number;

Table altered.

SQL> select status from user_objects where object_name = 'TST23';

STATUS
-------
VALID

SQL> 

Now we're protected by fine-grained dependency tracking. Well, sort of. We shouldn't substitute dependency tracking for impact analysis:

SQL> alter table p23 add col5 date not null;

Table altered.

SQL> select status from user_objects where object_name = 'TST23';

STATUS
-------
VALID

SQL> 

The procedure has a VALID state but it will still fail when we run it, because it doesn't populate the new mandatory column.