Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2009
    Posts
    16

    Unanswered: f a FK constraint exists on table

    Create an anonymous PL/SQL block to do the following
    Use Oracle data dictionary to find if a FK constraint exists on table dept.deptno to emp.deptno
    If exists Display error message Constraint Exists with the name of the constraint else create a FK constraint using dynamic SQL.

    correct me that .. any need of shortening the query

    DECLARE
    v_Statement VARCHAR2(150);
    v_count NUMBER(10);
    BEGIN

    SELECT COUNT(uc.constraint_name)
    INTO v_count
    FROM USER_CONSTRAINTS uc,USER_CONS_COLUMNS ucc
    WHERE uc.table_name = ucc.table_name
    AND uc.constraint_name = ucc.constraint_name
    AND uc.table_name = 'SATHI_DEPT'
    AND ucc.column_name = 'DEPTNO'
    AND uc.constraint_name IN ( select r_constraint_name
    FROM USER_CONSTRAINTS uc,USER_CONS_COLUMNS ucc
    WHERE uc.table_name = ucc.table_name
    AND uc.constraint_name = ucc.constraint_name
    AND uc.table_name = 'SATHI_EMP'
    AND ucc.column_name = 'DEPTNO'
    AND UC.constraint_name = 'FK_EMP_SATHI')
    IF v_count > 0 THEN
    DBMS_OUTPUT.PUT_LINE('Constraint Exists with the name of the constraint');
    ELSE
    vSQLStatement := 'ALTER TABLE SATHI_EMP ' ||
    'ADD CONSTRAINT FK_EMP_SATHI ' ||
    'FOREIGN KEY (DEPTNO)' ||
    'REFERENCES TTT(DEPTNO)';

    EXECUTE IMMEDIATE vSQLStatement;

    END IF;

    END;

    END;
    Correct me ..

    Thanks
    Last edited by SeenuGuddu; 09-21-09 at 16:50.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    You need to fix the syntax errors.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Sep 2009
    Posts
    16
    i have checked the syntax .. its working fine ..

    i am asking abt the Code ...
    is that ok .. or any need of shortening the query
    Code:
    SELECT COUNT(uc.constraint_name)
    INTO v_count
    FROM USER_CONSTRAINTS uc,USER_CONS_COLUMNS ucc
    WHERE uc.table_name = ucc.table_name
    AND uc.constraint_name = ucc.constraint_name
    AND uc.table_name = 'SATHI_DEPT'
    AND ucc.column_name = 'DEPTNO'
    AND uc.constraint_name IN ( select r_constraint_name
    FROM USER_CONSTRAINTS uc,USER_CONS_COLUMNS ucc
    WHERE uc.table_name = ucc.table_name
    AND uc.constraint_name = ucc.constraint_name
    AND uc.table_name = 'SATHI_EMP'
    AND ucc.column_name = 'DEPTNO'
    AND UC.constraint_name = 'FK_EMP_SATHI')
    Thanks

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >i have checked the syntax .. its working fine ..
    You are entitled to your opinion.


    >i am asking abt the Code ...
    >is that ok .. or any need of shortening the query
    If you are getting the expected/desired results, then all is OK.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    This forum has a fixed rule. We do not do peoples homework. At least try doing it your self.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    I would suggest that when the homework assignment says "If exists Display error message Constraint Exists with the name of the constraint" it does not mean that your program should output the text "Constraint Exists with the name of the constraint"!?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •