-
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
-
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?
-
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.
-
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
-
Yep, you can. Those are called self referential constraints.
-
Originally posted by leviathan
Yep, you can. Those are called self referential constraints.
Pig tail
-
Originally posted by stancho
Pig tail
fish hook
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|