Can I create a foreign key for a table with elements which don't exist in the referenced table?

Question

I'm working on an assignment which wants me to write some referential integrity constraints on a table. It asks me to ensure that every battle in Outcomes is also mentioned in Battles and to handle all violations by setting the referencing value to NULL.

I'm working with the following schemas

Battles(
    name VARCHAR(255) PRIMARY KEY, 
    date VARCHAR(255), 
)
Outcomes(
    ship VARCHAR(255),
    battle VARCHAR(255),
    RESULT VARCAHR(255),
    PRIMARY KEY(ship, battle)
)

I'm trying to add the foregin key like this:

alter table outcomes 
add foreign key (battle) 
references battles(name) 
on delete set null 
on update cascade;

I get the following error:

SQL error:

ERROR:  insert or update on table "outcomes" violates foreign key constraint "outcomes_battle_fkey"
DETAIL:  Key (battle)=(Pearl Harbor

I know this is because "Pearl Harbor" is in OUTCOMES but not in BATTLES, what I want to know is if there is some way to ignore that fact and set the foreign key anyway?

What RDBMS are you using? (looks like a pgsql error, I think) — Michael Berkowski, Nov 03 '12 at 23:09
possible duplicate of [How do I disable referential integrity in Postgres 8.2?](http://stackoverflow.com/questions/139884/how-do-i-disable-referential-integrity-in-postgres-8-2) — Michael Berkowski, Nov 03 '12 at 23:19
@MichaelBerkowski This is a different question; see answers below that show that. — Craig Ringer, Nov 04 '12 at 01:06

Branko Dimitrijevic · Accepted Answer · 2012-11-04T02:00:29.240

and to handle all violations by setting the referencing value to NULL.

That's impossible, since battle is part of the Outcomes's PK so it can't be NULL. Your data model is simply not constructed to handle a "battle-less" outcome.

If you were to change your model and remove the battle from child table's PK, then you could "fix" the data by:

UPDATE Outcomes SET battle = NULL
WHERE battle NOT IN (SELECT battle FROM Battles)

At this point, you would be able to create the FK without causing a violation (FKs are only enforced on non-NULL fields).

score 1 · Answer 2 · answered Nov 03 '12 at 23:14

1

You have to look for an option which prevents the check at the time of key creation, for Sql Server it would be WITH NOCHECK it:

Specifies whether the data in the table is or is not validated against a newly added or re-enabled FOREIGN KEY or CHECK constraint. If not specified, WITH CHECK is assumed for new constraints, and WITH NOCHECK is assumed for re-enabled constraints.

answered Nov 03 '12 at 23:14

Tomek

3,267
2
22
23

1

according to the documentation that doesn't look possible in Postgres http://www.postgresql.org/docs/9.1/interactive/sql-altertable.html – Ben Glasser Nov 03 '12 at 23:53
The question was later tagged [PostgreSQL], which makes your assumption this would concern SQL Server void. – Erwin Brandstetter Nov 04 '12 at 07:24

Can I create a foreign key for a table with elements which don't exist in the referenced table?

2 Answers2