CREATE TABLE store_reps (rep_ID NUMBER(5), last VARCHAR2(15), frst VARCHAR2(10),

Question

CREATE TABLE store_reps (rep_ID NUMBER(5), last VARCHAR2(15), frst VARCHAR2(10), Commchar(1) DEFAULT 'Y', CONSTRAINT "REP_ID" PRIMARY KEY ("REP_ID"));Table created.2.ALTER TABLE store_reps MODIFY (last CONSTRAINT store_reps_last_nn NOT NULL, frstCONSTRAINT store_reps_frst_nn NOT NULL);Table altered.3.ALTER TABLE store_reps ADD CONSTRAINT store_reps_comm_ck CHECK (comm IN ('Y','N'));Table altered.4.ALTER TABLE store_reps ADD (Base_salary Number(7,2)) ADD CONSTRAINTstore_reps_Base_salary_ck CHECK (Base_salary > 0);Table altered.5.CREATE TABLE Book_stores (Store_ID Number(8), Name Varchar2(30), contact varchar2(30),Rep_ID varchar2(5), CONSTRAINT book_stores_store_ID_pk PRIMARY KEY (Store_ID),CONSTRAINT book_stores_name_uk UNIQUE (name));Table created.6.alter table book_stores modi±y (rep_ID number(5)) add constraint book_stores_rep_ID_²±oreign key (Rep_ID) re±erences Store_Reps (Rep_ID);Table altered. (There was an error the frst ³me)7.(ORA-02275: such a re±eren³al constraint already exists in the table)

Explanation / Answer

This error occurs if the contraint name Already Exists in Table. To resolve this

1. Check If the contraint exists in table using below query

SELECT A.constraint_name
FROM   all_cons_columns A,
       all_constraints C
WHERE  A.constraint_name = C.constraint_name
       AND C.constraint_type = 'R'
       AND C.table_name = 'book_stores';

2. drop the constraint , using query ALTER TABLE table_name DROP CONSTRAINT constraint_name

3. run your query again to add the constraint

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.