Check Constraint
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Check Constraint

  1. #1
    Join Date
    Nov 2002
    Posts
    25

    Check Constraint

    I've got a table called PRODUCT in an 8173 database as follows:

    Name Null? Type
    ---------------------------------------- -------- ----------------------------
    PRODUCT_ID NOT NULL VARCHAR2(8)
    PRODUCT_DESC NOT NULL VARCHAR2(255)
    NOMINAL_CODE NUMBER
    PRODUCT_TYPE NUMBER(3)
    UNIT_OF_MEASURE VARCHAR2(5)
    QUANTITY NUMBER(12,2)
    LAST_UPDATE DATE
    PRODUCT_SHORT_DESC VARCHAR2(30)
    EDI_PRODUCT_ID VARCHAR2(6)
    SPS_PRODUCT_ID VARCHAR2(3)
    TXU_PRODUCT_ID NOT NULL NUMBER

    We have a developer that wants to add a column LOSSES_PRODUCT_ID that has a check constraint that says "if not null then must exist in product.product_id"
    Is this possible - if so I'm not sure how to code it. Ahy help appreciated!
    Thanks,
    Fraze

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    That would be a foreign key constraint, not check constraint. I assume you have primary key on PRODUCT_ID. So you would add the following FK constraint on LOSSES_PRODUCT_ID:

    ALTER TABLE product ADD CONSTRAINT fk_losses_product_id
    FOREIGN KEY (losses_product_id) REFERENCES product(product_id);
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #3
    Join Date
    Nov 2002
    Posts
    25
    Can a FK reference a column in the same table? the column that the required "check" constraint is for, is in the same table as the column being added. The product_id column is the primary key.

  4. #4
    Join Date
    Nov 2002
    Posts
    25
    Well I've run a test and it appears that you can have a FK to a column in the same table.
    Thanks for pointing me in the right direction

  5. #5
    Join Date
    Apr 2003
    Location
    In torment, in hell
    Posts
    18
    Yep, you can. Those are called self referential constraints.

  6. #6
    Join Date
    Jul 2003
    Location
    Sofia, Bulgaria
    Posts
    91
    Originally posted by leviathan
    Yep, you can. Those are called self referential constraints.
    Pig tail

  7. #7
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Originally posted by stancho
    Pig tail
    fish hook
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

Posting Permissions

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



Click Here to Expand Forum to Full Width