Post reply

Disabling / dropping / creating Primary Keys in stored procedures

  • July 26, 2004 at 3:36 am

    #88176

    Just discovered something strange. If you write a stored proc in Enterprise Manager to create a primary key, click Check Syntax and then OK, the PK is created!

    In other words, "Create Procedure" seems to be running it as well with no rollback. This is a bit of a pain if you're into the old iterative build-check-test routine.

    I know DDL in a stored proc is uncommon, but couldn't avoid it here. My table is for a data load from an Excel file. In-house rules require a PK, but the load may contain null rows. I wanted to disable the PK (can do this in Oracle), so tried ALTER TABLE ALTER COLUMN... NO CHECK but I suppose the NOT NULL and PPK constraints have lives of their own. Hence went for drop / recreate as the next easiest option and discovered the oddity above. Can anyone tell me why this isn't a bug?

  • July 26, 2004 at 4:07 am

    #516050

    2 of the great rules of IT have just kicked in:

    1) As soon as you give up on a problem, the solution wanders casually into view

    2) As soon as you post to a worldwide forum, someone local has an answer

    My stored proc had GO statements in it. This caused the immediate execution. Lesson to self: don't use GO because it seems like a good idea: just batch when necessary.

    Bill.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply