CODEWITHSUNDEEP
mysqlsqloracle-database
Junjie
Junjie

Reputation: 1175

Constraints for foreign keys like this in associative table?

There are three tables A, B, C, column src is foreign key in table A and B. Table is the associative table for A.ID and B.ID.

Is there any constrains in mysql to make sure that each row inserted into table C must follow the constraint: A.src equals B.src for their ID.

For example, in table C as below, row 1 7 is allowed because both SRC for A.ID and B.ID are all 35, however row 2 7 is illegal because SRC for A.ID is 46 but for B.ID is 35. 

Table A
ID  SRC
1   35
2   46
3   46

Table B
ID  SRC
6   46
7   35
8   46

Table C
A_ID    B_ID
1   7
3   6

Is there any constrains or mechanism can be set up to make sure each row inserted into table C is following the regular?

Thanks.

Upvotes: 3

Views: 413

Answers (2)

MT0
MT0

Reputation: 167982

You can do this in Oracle using materialized views:

Oracle Setup:

CREATE TABLE A (
  ID NUMBER PRIMARY KEY,
  SRC NUMBER
);

CREATE TABLE B (
  ID NUMBER PRIMARY KEY,
  SRC NUMBER
);

CREATE MATERIALIZED VIEW LOG ON A
   WITH SEQUENCE, ROWID(id, src)
   INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON B
   WITH SEQUENCE, ROWID(id, src)
   INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW AB_MV
   BUILD IMMEDIATE
   REFRESH FAST ON COMMIT
   AS SELECT a.id, b.id 
      FROM   a, b
      WHERE  a.src = b.src
      GROUP BY A.id, B.id;


ALTER TABLE AB_MV ADD CONSTRAINT AB_MV__A__B__PK PRIMARY KEY (A_ID, B_ID );

CREATE TABLE C (
  A_ID NUMBER REFERENCES A( ID ),
  B_ID NUMBER REFERENCES B( ID ),
  PRIMARY KEY ( A_ID, B_ID ),
  FOREIGN KEY ( A_ID, B_ID ) REFERENCES AB_MV ( A_ID, B_ID )
);

Inserting Data:

INSERT INTO A
SELECT 1, 35 FROM DUAL UNION ALL
SELECT 2, 46 FROM DUAL UNION ALL
SELECT 3, 46 FROM DUAL;

INSERT INTO B
SELECT 7, 35 FROM DUAL UNION ALL
SELECT 6, 46 FROM DUAL UNION ALL
SELECT 8, 46 FROM DUAL;

SELECT * FROM AB_MV;
-- Will return zero rows as there has not been a commit.

COMMIT;

SELECT * FROM AB_MV;
-- Will return 5 rows.

INSERT INTO C VALUES ( 1, 7 );
-- 1 row inserted

INSERT INTO C VALUES ( 2, 7 );
-- Fails with ORA-02291: Integrity constraint violated - parent key not found.

You can probably do a similar thing in MySQL using triggers to populate an AB_MV table (using MERGE INTO to enforce the uniqueness) - although a trigger for deleting rows from A or B might be interesting as the ID pairs can come from multiple SRC values.

Upvotes: 0

JTR
JTR

Reputation: 333

I'm not sure about this, but I tried this

INSERT INTO table_c
(SELECT
   a.ID,
   b.ID
 FROM
   table_a AS a INNER JOIN table_b AS b ON a.SRC = b.SRC
);

This works for me to insert ID from table_a and table_b to table_c

Upvotes: 0

Related Questions