At 04:06 AM 3/12/2008, you wrote:

>Is it possible to have a FK child table where some of the rows FK values
>are not present in the parent table?
>
>For example...
>
>CREATE TABLE PARENT
>(
>KEY INTEGER NOT NULL PRIMARY KEY,
>NAME VARCHAR(10)
>);
>
>CREATE TABLE CHILD
>(
>FKEY INTEGER NOT NULL,
>NAME VARCHAR(10),
>CONSTRAINT FK_PARENT
> FOREIGN KEY (FKEY)
> REFERENCE PARENT(KEY)
> ON UPDATE CASCADE
>);
>
>
>INSERT INTO PARENT (KEY, NAME) VALUES(1, 'Test');
>
>INSERT INTO CHILD (FKEY, NAME) VALUES(1, 'Child');
>/* okay */
>
>INSERT INTO CHILD (FKEY, NAME) VALUES(123, 'Orphan');
>/* fails, but I'd like it to work, the FKEY values would be unique and
>not conflict with parent key values */

No - if that were possible, it wouldn't be an INTEGRITY constraint, would it? But, out of curiosity, WHY?

The ON....SET NULL action rule is available for retaining orphans deliberately after deleting the parent or altering its primary key. The child has to have start life with a parent, though, and you must make the FK column[s] nullable. The rule will apply to the entire table, of course: you can't have two action rules in the same constraint.

Another way is to have a surrogate parent record that never gets deleted, with e.g. -1 as its primary key, that you can hand the would-be orphans over to in a Before Delete trigger on the natural parent. This would have a similar effect to the SET NULL rule, but it would also enable you to create "orphans" directly. It would also be possible to have more than one of these surrogate parents if you wanted to.

./heLen