Hello everybody,
I have a question about creating foreign key and I would appreciate if you could kindly give me a hand. Here are my tables:
CREATE TABLE TEAM1(team_id VARCHAR2(20), project_id VARCHAR2(20));
ALTER TABLE TEAM1 ADD CONSTRAINT PK_TEAM1 PRIMARY KEY(team_id);
CREATE TABLE TEAM2(team_id VARCHAR2(20), project_id VARCHAR2(20));
ALTER TABLE TEAM2 ADD CONSTRAINT PK_TEAM2 PRIMARY KEY(team_id);
although the structure of both the tables is exactly the same, the values (in particular team_id) in both tables are different.
Also I have another table named AGENT
CREATE TABLE AGENT(agent_id VARCHAR2(20), team_id VARCHAR2(20));
ALTER TABLE AGENT ADD CONSTRAINT PK_AGENT PRIMARY KEY(agent_id)
Now the problem is that the column team_id in AGENT table is actually a foreign key, but the value can be in either TEAM1 or TEAM2. As far as I know a foreign key points only to one table.
How can I deal with this problem? Whenever there is an INSERT or UPDATE I have to make sure that the value of the column "team_id" in the table "AGENT" is a valid value either in "TEAM1" or "TEAM2"
Thanks in advance,
Kind Regards,
Dariyoosh