• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 675
  • Last Modified:

SQL alter table unique in shell script

I have a table with two fields, employee & job.

Every employee can have multiple jobs however each job must be unique.  For example, employee1 can be assigned to job1 & job2.  Employee2 can also be assigned to job2.  Employee1 cannot be assigned to a duplicate job (job2, job2).

I have a shell script to create/alter the table.

I added:
ALTER TABLE employeeJobMap
ADD UNIQUE (job);

however, that made it so the job is unique and can only be mapped to 1 employee.

I removed the alter statement above and added:

ALTER TABLE employeeJobMap
ADD CONSTRAINT employeeMap UNIQUE (employee, job);

I am still unable to add the same job to multiple employees.  I'm not sure if I have to somehow undo the 1st alter statement.  Not sure if the 2nd alter statement is correct either.
0
galaxy573
Asked:
galaxy573
  • 2
  • 2
1 Solution
 
schwertnerCommented:
You have to go to the view user_constraints and figure out the name of the constraint.

SQL> select  CONSTRAINT_NAME FROM  user_constraints where table_name='EMPLOYEES';

CONSTRAINT_NAME
------------------------------
SYS_C0084010
SYS_C0084011
SYS_C0084012
SYS_C0084013
SYS_C0084014
EMPLOYEES_DEPARTMENT_ID_FK

6 rows selected.

After that you can drop the constraints and create the needed one

ALTER TABLE           employees
DROP CONSTRAINT emp_manager_fk;

0
 
galaxy573Author Commented:
I am getting no rows selected.  Is it because I didn't name the 1st constraint when I added it?

SQL> select CONSTRAINT_NAME FROM user_constraints where table_name='employeeJobMap';

no rows selected


See shell script below.
create table employeeJobMap
(
        employee number constraint fk_employeeJobMap_employees
                references employees(id) on delete cascade,
        job number constraint fk_employeeJobMap_jobss
                references employees(id)
)
/
!
 
cat $$out
if [ -z "`grep \"Table created\" $$out`" ]
then
    exit 1
fi
 
else
    echo altering employeeJobMap
        /oracle/bin/sqlplus -s $PLAT <<! >$$out 2>&1
        set termout on;
        set trim on;
        set heading off;
 
        ALTER TABLE employeeJobMap
        drop CONSTRAINT fk_employeeJobMap_employee;
 
        ALTER TABLE employeeJobMap
        add CONSTRAINT fk_employeeJobMap_employee
        FOREIGN KEY (employee)
        REFERENCES employees(id)
        ON DELETE CASCADE;
 
        ALTER TABLE employeeJobMap
        ADD CONSTRAINT employeeMap UNIQUE (employee, job);
!
fi
echo

Open in new window

0
 
galaxy573Author Commented:
correcting typo below.
job number constraint fk_employeeJobMap_jobss
                references jobs(id)

Open in new window

0
 
sventhanCommented:
try the following and see if you get any rows ...
SQL> select CONSTRAINT_NAME FROM user_constraints where table_name='EMPLOYEEJOBMAP';

 
0
 
sventhanCommented:
thanks for the points...
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now