CODEWITHSUNDEEP
oracle-database
Mildas
Mildas

Reputation: 47

SQL Oracle - checking value in another table with CHECK or TRIGGER

Is any way, how to create table with CHECK (or maybe TRIGGER?), that will check, if inserted value is already in another table?

Example

CREATE TABLE Employee(
  Name VARCHAR(10),
  Adress VARCHAR(20)
);

CREATE TABLE Section(
  Section_name VARCHAR(10),
  Managers_name VARCHAR(10)
);

And I want to check, that value inserted to Managers_name is already in Employee, if it isnt, then print error.

I found any ways, how it could be done, but everything I tried in Oracle, didnt work.

Upvotes: 0

Views: 281

Answers (2)

Jamil.S
Jamil.S

Reputation: 1

Check constraint is, as explained by mt0, devoted to single table. Trigger is to be avoided for consistency reasons: while you're selecting a record, another session might be in the process of deleting it.

The Foreign Key is the correct way to implement it: Trap FK constraint violation in your code (ORA-02291 i guess), search for "section_manager_fk" in the message and finally rewrite a user-friendly error message.

Upvotes: 0

MT0
MT0

Reputation: 168806

Add a PRIMARY KEY constraint to the employee table and a FOREIGN KEY constraint to the section table:

CREATE TABLE Employee(
  Name VARCHAR(10) CONSTRAINT Employee_Name_PK PRIMARY KEY,
  Adress VARCHAR(20)
);

CREATE TABLE Section(
  Section_name  VARCHAR(10),
  Managers_name VARCHAR(10)
                CONSTRAINT section_manager_fk REFERENCES Employee( Name )
);

Upvotes: 1

Related Questions