How to set hierarchic foreign key through another table?

Question

I am not sure if my title really explains the question, so I'll try an example:

Let's say I have:

1) region table: parent regions and subregions in the same table (link A)

2) product table: each product is linked to a single parent region (link B)

3) product_price table: lists the price of a product (C) in all sub regions of that product region (link D >>> the link in question).

(the diagram is showing only the relevant fields, there are a lot more data in region and product) tables diagram

Is there a way to define the region->region_price key, to include only sub_region_id's of the parent_region in the product->region key??

Or, in the terms of the image, how do I make the D link to include only regions that are children [as in A] of the B & C link?

Hope you are getting my point...

Here are the real tables and links:

CREATE TABLE [dbo].[product](
    [product_id] [int] NOT NULL,
    [product_name] [nchar](10) NOT NULL,
    [parent_region_id] [int] NOT NULL,
 CONSTRAINT [PK_product] PRIMARY KEY CLUSTERED 
(
    [product_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[product_price](
    [product_id] [int] NOT NULL,
    [sub_region_id] [int] NOT NULL,
    [price] [decimal](18, 0) NOT NULL,
 CONSTRAINT [PK_product_price] PRIMARY KEY CLUSTERED 
(
    [product_id] ASC,
    [sub_region_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[region](
    [region_id] [int] NOT NULL,
    [region_name] [nvarchar](50) NOT NULL,
    [parent_region_id] [int] NULL,
 CONSTRAINT [PK_region] PRIMARY KEY CLUSTERED 
(
    [region_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

ALTER TABLE [dbo].[product]  WITH CHECK ADD  CONSTRAINT [FK_product_region] FOREIGN KEY([parent_region_id])
REFERENCES [dbo].[region] ([region_id])

ALTER TABLE [dbo].[product] CHECK CONSTRAINT [FK_product_region]

ALTER TABLE [dbo].[product_price]  WITH CHECK ADD  CONSTRAINT [FK_product_price_product] FOREIGN KEY([product_id])
REFERENCES [dbo].[product] ([product_id])

ALTER TABLE [dbo].[product_price] CHECK CONSTRAINT [FK_product_price_product]

ALTER TABLE [dbo].[product_price]  WITH CHECK ADD  CONSTRAINT [FK_product_price_region] FOREIGN KEY([sub_region_id])
REFERENCES [dbo].[region] ([region_id])

ALTER TABLE [dbo].[product_price] CHECK CONSTRAINT [FK_product_price_region]

ALTER TABLE [dbo].[region]  WITH CHECK ADD  CONSTRAINT [HK_region_region] FOREIGN KEY([parent_region_id])
REFERENCES [dbo].[region] ([region_id])

ALTER TABLE [dbo].[region] CHECK CONSTRAINT [HK_region_region]

http://stackoverflow.com/questions/13000698/sub-queries-in-check-constraint — bniya dev, Feb 01 '15 at 13:20

score 0 · Answer 1 · answered Feb 02 '15 at 19:35

0

I guess you are trying to enforce a business rule with a database rule, but they're not always the same. Instead, you can run validation querys in your code before inserting or updating, or you could implement a trigger that validates your rules or throws an error.

answered Feb 02 '15 at 19:35

Raul Cosio

110
6

you are right, it is a businees rule, but I wanted it to be a database rule so entity framework gets it – bniya dev Feb 04 '15 at 14:33

How to set hierarchic foreign key through another table?

1 Answers1