|
Home > Archive > microsoft.public.sqlserver.server > August 2002 > Referential Integrity
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Referential Integrity
|
|
|
| I am trying to set up referential integrity on a view that
i have created.
Example
Base Table is:
Store
Rep
Amount
Control table is:
Type
Code
Description
if the base table has records like this:
Vons Steve 123.45
Costco Joe 678.90
and the control table like this:
Store Vons Vons Markets
Store Costco Costco Discount
Rep Steve Steve Doe
Rep Joe Joe Blow
I want to set up ref integrity to check the store and rep
against the control file. I thought about creating a view
call store that was:
Select Code, Description From Control Where Type = 'Store';
and then use the view for referential integrity, but that
does not work.
Any Ideas?????
Thanks
Pat
| |
| Ron Talmage 2002-08-06, 6:23 pm |
| Pat,
Views cannot enforce referential integrity. You have to declare a
constraint.
To validate the stores in your base table, you need a stores table that
contains a list of all the valid store names. It would be better to use a
store id rather than name, if you can:
Stores
StoreID int not null primary key
StoreName
Then you can declare referential integrity using ALTER TABLE (and not a
view):
ALTER TABLE [Base Table]
ADD CONSTRAINT FK_BaseTable_Stores
FOREIGN KEY (StoreID)
REFERENCES dbo.Stores(StoreID)
Hope this helps,
Ron
--
Ron Talmage
SQL Server MVP
Pat <pnerad@uwgrocers.com> wrote in message
news:130301c23d9e$d5d30a20$a4e
62ecf@tkmsftngxa06...
> I am trying to set up referential integrity on a view that
> i have created.
>
> Example
>
> Base Table is:
> Store
> Rep
> Amount
>
> Control table is:
> Type
> Code
> Description
>
> if the base table has records like this:
> Vons Steve 123.45
> Costco Joe 678.90
>
> and the control table like this:
> Store Vons Vons Markets
> Store Costco Costco Discount
> Rep Steve Steve Doe
> Rep Joe Joe Blow
>
> I want to set up ref integrity to check the store and rep
> against the control file. I thought about creating a view
> call store that was:
> Select Code, Description From Control Where Type = 'Store';
>
> and then use the view for referential integrity, but that
> does not work.
>
> Any Ideas?????
>
> Thanks
>
> Pat
|
|
|
|
|