Foreign Key for Either-Or Column

Foreign Key for either-or column?

A check constraint can handle this. If this is SQL Server, something like this will work:

create table A (Id int not null primary key)
go
create table B (Id int not null primary key)
go
create table C (Id int not null primary key, A_Id int null, B_Id int null)
go
alter table C add constraint FK_C_A
foreign key (A_Id) references A (Id)
go
alter table C add constraint FK_C_B
foreign key (B_Id) references B (Id)
go
alter table C add constraint CK_C_OneIsNotNull
check (A_Id is not null or B_Id is not null)
go
alter table C add constraint CK_C_OneIsNull
check (A_Id is null or B_Id is null)
go

Database foreign key in either one or another column

The problem was gone because at least we redefined our datamodel anyway. Thanks for your help anyway!

Foreign Key has to exist on either table

You can have FK constraints on a single column that references into two different tables but it will not work with your use case.

Since an incidentNbr exists either in Incident table or PendIncident table at any given point in time, having two FK constraints in IncidentDetail table will fail as soon as you attempt to insert a record in this child table. Since the incident exists in one parent table but not the other, it will throw an integrity constraint violation error w.r.t. second FK.

Using a check constraint can be a viable solution for this scenario.

Code snippet for quick reference -

Create table table_a(col_a number primary key);
Create table table_b(col_b number primary key);
Create table table_c(col_c number);

ALTER TABLE table_c
ADD CONSTRAINT fk_c_a
  FOREIGN KEY (col_c)
  REFERENCES table_a(col_a);

ALTER TABLE table_c
ADD CONSTRAINT fk_c_b
  FOREIGN KEY (col_c)
  REFERENCES table_b(col_b);

Insert into table_a values(100);
Insert into table_b values(200);
Insert into table_c values(100); —-This statement will throw integrity constraint violation error

How to declare a foreign key with an OR condition using Oracle?

No, that sort of thing is not possible in Oracle.

Your options generally are

• Create two different columns (bkey and ckey) in A where bkey references B.bkey and ckey references C.ckey and create a constraint that ensures that only one is non-NULL at any point in time.
• Create some sort of "combined B & C" entity that B & C have foreign keys to and make the foreign key in A reference the key of this combination entity.

If you want a constraint that ensures that exactly one of two columns is NULL and one is NOT NULL for any row

create table one_key( 
  col1 number, 
  col2 number, 
  check( nvl2(col1,1,0) + nvl2(col2,1,0) = 1 ) 
)

Database design when having either foreign key or string

There's not a lot of information in your question on which to base an answer.

If there is common data and functionality across all types of supervisors then you will probably want one table to hold that common data. That table would establish the primary key values for supervisors and the case table would have a foreign key into this table. Information that is unique to either internal or external supervisors would go into separate tables and those tables would also have a foreign key back to the common supervisor level data.

This design is superior because you only have one place to go in order to find a list of all supervisors and because you can enforce the supervisor / case relationship directly in the database without a lot of code or additional constraints to ensure that "one and only one" of two columns is populated.

It's sufficiently superior, from a database point of view, that I'd consider using this design even if the data for internal and external supervisors is completely disjoint (which it's unlikely to be).

Foreign key to table A or table B

The primary reason why we have foreign keys is not so as to be able to do things like cascaded deletes. The primary reason for the existence of foreign keys is referential integrity.

This means that grpid is declared as REFERENCES group(id) in order to ensure that grpid will never be allowed to take any value which is not found in group(id). So, it is an issue of validity. A cascaded DELETE also boils down to validity: if a key is deleted, then any and all foreign keys referring to that key would be left invalid, so clearly, something must be done about them. Cascaded deletion is one possible solution. Setting the foreign key to NULL, thus voiding the relationship, is another possible solution.

Your notion of having a child id refer to either a group or an object violates any notion of referential integrity. Relational Database theory has no use and no provision for polymorphism. A key must refer to one and only one kind of entity. If not, then you start running into problems like the one you have just discovered, but even worse, you cannot have any referential integrity guarantees in your database. That's not a nice situation to be in.

The way to handle the need of relationships to different kinds of entities is with the use of a set of foreign keys, one for each possible related entity, out of which only one may be non-NULL. So, here is how it would look like:

CREATE TABLE tree_relation (
    parent_id INTEGER,
    child_object_id INTEGER,
    child_group_id INTEGER,
    PRIMARY KEY (parent_id, child_object_id, child_group_id) );
ALTER TABLE tree_relation
    ADD FOREIGN KEY (parent_id) REFERENCES group(id) ON DELETE CASCADE;
ALTER TABLE tree_relation 
    ADD FOREIGN KEY (child_object_id) REFERENCES object(id) ON DELETE CASCADE;
ALTER TABLE tree_relation
    ADD FOREIGN KEY (child_group_id) REFERENCES group(id) ON DELETE CASCADE;

All you need to do is ensure that only one of child_object_id, child_group_id is non-NULL.

postgresql: foreign key to either tableA or tableB

CREATE TABLE source (
       type CHAR(1) NOT NULL CHECK (type IN ('J', 'B')),
       id INT NOT NULL,
       PRIMARY KEY (type, id)
);

CREATE TABLE book (
       type CHAR(1) NOT NULL CHECK(type = 'B'), id INT NOT NULL,
       PRIMARY KEY (id),
       FOREIGN KEY (type, id) REFERENCES source (type, id) ON DELETE CASCADE
);

CREATE TABLE journal (
       type CHAR(1) NOT NULL CHECK(type = 'J'), id INT NOT NULL,
       PRIMARY KEY (id),
       FOREIGN KEY (type, id) REFERENCES source (type, id) ON DELETE CASCADE
);

CREATE TABLE scan (id INT NOT NULL, sourcetype CHAR(1) NOT NULL, sourceid INT NOT NULL,
       PRIMARY KEY (id),
       FOREIGN KEY (sourcetype, sourceid) REFERENCES source (type, id)
);

With this design, you should not delete records directly from book or journal: instead, delete from table source which will cascade the operation to the appropriate table.

You can move the attributes that are common to book and journal to source.

How to enforce two foreign key columns to be either both null or both pointing to some row in other table in Oracle?

You need to combine your two foreign keys into a single foreign key - otherwise I think you'll find they're not doing quite what you think they're doing. Also, you need a check constraint to ensure that all three fields are set or all three are NULL. Your TASKS table needs to be something like:

CREATE TABLE TASKS (
    TASK_ID          NUMBER(6) NOT NULL,
    NAME             VARCHAR2(32) NOT NULL,
    IS_BOSS          NUMBER(1),
    EMPLOYEE_ID      NUMBER(6),
    FINISH_DATE      DATE,

    CONSTRAINT TASKS_CK1
      CHECK (is_boss IN (1)),
    CONSTRAINT TASKS_FK1
      FOREIGN KEY (IS_BOSS, EMPLOYEE_ID)
        REFERENCES EMPLOYEES (IS_BOSS, EMPLOYEE_ID),
    CONSTRAINT TASKS_CK2
      CHECK((IS_BOSS IS NULL AND
               EMPLOYEE_ID IS NULL AND
               FINISH_DATE IS NULL)
            OR
            (IS_BOSS IS NOT NULL AND
               EMPLOYEE_ID IS NOT NULL AND
               FINISH_DATE IS NOT NULL))
);

---

Related Topics