Check Constraint

[cleggfr]

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

[jmodic]

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);

[cleggfr]

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.

[cleggfr]

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

[leviathan]

Yep, you can. Those are called self referential constraints.

[stancho]

Originally posted by leviathan
Yep, you can. Those are called self referential constraints.

Pig tail

[slimdave]

Originally posted by stancho
Pig tail

fish hook