SQL constraint to check another table column

Question

So essentially I have the following problem to implement as part of an oracle sql database:

table1(attr1, attr2)

table2(attr3, table1_attr1)

Constraint: a table2 tuple can only have some table1_attr1 for which tuple in table1 attr2 is not null.

I have tried the following (along with other commented out possibilities), but it obviously doesn't work because attr2 is not a unique value:

CREATE TABLE table1 (
  attr1                VARCHAR(100)  NOT NULL,
  attr2             VARCHAR(200),
  PRIMARY KEY(attr1)
);

CREATE TABLE table2 (
  attr3                  INTEGER       NOT NULL,
  table1_attr1       VARCHAR(100)  NOT NULL,
  table1_attr2         VARCHAR(50),  --REFERENCES table1(attr2),

  FOREIGN KEY (table1_attr1, table1_attr2) REFERENCES table1(attr1, attr2),
    CONSTRAINT const_table1 CHECK(table1_attr2 IS NOT NULL),

  PRIMARY KEY(attr3)
);

I need some help, since making attr2 unique is not an option. I have read about making a function somewhere, but I'm not very familiar with them. Will try it if they are the only choice.

Ren · Accepted Answer · 2014-11-22 09:37:38Z

1

Finally found an answer by myself. It will sound silly, but I do hope it helps anyone else out there struggling with this case.

By adding:

UNIQUE (attr1, attr2)

inside table1 we are creating a unique key, but since the primary is just attr1, there is no way that there would be duplicates of the same tuple with different attr2s.

Point being, now the combination of these two is a unique key, without affecting the rest of the database. And so the check constraint in table2 can be done without trouble at all.

answered Nov 22, 2014 at 9:37

Ren

4,6919 gold badges39 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL constraint to check another table column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related