Check the uniqueness of department name

Other Tech Topics
1

Hello! I would like to make a procedure that will help me add a new row in the Departments table. I have to insert department_id, department_name, location_id and manager_id. I made this successfully with that code:

=======

create or replace PROCEDURE add_depar(
p_name VARCHAR2, p_mgr NUMBER, p_loc NUMBER) IS
BEGIN
INSERT INTO DEPARTMENTS (department_id,
department_name, manager_id, location_id)
VALUES (DEPARTMENTS_SEQ.NEXTVAL, p_name, p_mgr, p_loc);
DBMS_OUTPUT.PUT_LINE('Added Dept: '|| p_name);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Err: adding dept: '|| p_name);
END;

=======

Now I'd like to check the uniqueness of the department_name, And if the requirements are not done, I would like to make an exception for that. May I ask for your help! I have tried to do this many times, but still not resolved. Please help!

Thanks

2

  1. you are using Oracle ? FYI SQLTeam is on MS SQL Server

  2. just create a unique index on department_name

3

Yes. I am using Oracle. Do I have to create a unique index? May I just search some way to see if the department name is unique and if there is a department with the same name, then I have to raise an exception and put the new row in a new table?

4

I can't actually write the code for you because I haven't touched Oracle for more than a decade. I do remember that what you are trying to do can most easily be solved my using MERGE in Oracle, which is a kind of "upsert" on steroids. Look it up in the Oracle documentation. IIRC, the exception code can be included in MERGE, as well.

And yes... if Department_ID and Department_Name are each supposed to be unique, you're actually going to need 2 unique indexes... one on Department_ID and one on Department_Name. You might want to add a 3rd on the combination of the two if they're frequently used together for reasons of performance.

It's probably best to make each column NOT NULL, as well.