Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

how to use triggers to performe foreign key constrain based on multiple tables

Posted on 2007-12-04
10
Medium Priority
?
281 Views
Last Modified: 2010-03-19
I am writing three triggers two mimic a Primary and Foreign key constraint between three tables and its parent table. What I mean and wan to do is the following:

I have a table called Items. Items contain a primary key on itemID field. It contains other fields such as itemTypeID, ie memory, cpu, motherboard & Also, it contains other fields such as name, series, model, brand, imageFileName, Price &.
I would like to make Items table a generic table for other tables that contain more specific item details.

Now, assume that I have the following tables for which Item works as a generic table: memoryDetail, cpuDetail, motherBoardDetail&

The specifications are as follows when trying to insert a record to the parent and child table:

* ItemID in Items table must be a primary key
* ItemID must serve as a Foreign key constrain in memoryDetail, cpuDetail,    
   motherBoardDetail&
* ItemID must exist only once in one of the derived(detail) tables. What I mean is that  
   memoryDetail and cpuDetail can not have the same ItemID. The same condition holds
   true for any conbination of the derived tables. Derived tables cannot share the same
   ItemID
* ItemID cannot be duplicated in any of the derived (detail) tables.
* If ItemID exist in one of the details table and an attempt to reuse the same itemID in another detail table occurs, an  
   error message (RAISERROR Method) should come up detailing the two tables that cost the error to be raised.

I have been trying to use a For Insert trigger to performer the above constrain. However, I cannot get the joins to work properly. Please, can some one show me how to do this query (trigger) for one of the details table and I would replicate the query and adjust it accordantly to work for the other details tables.

Thanks
0
Comment
Question by:vielkacarolina1239
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 3
10 Comments
 
LVL 27

Assisted Solution

by:MikeToole
MikeToole earned 2000 total points
ID: 20403891
What I think you need is an intermediate table to enforce the singularity:

Items
  ItemID PK

ItemType
  ItemID PK
  InfoType

memoryDetail
  ItemID, InfoType FK
   
ItemType is inserted when you insert to any of the Detail tables. Each Detail table has it's own value for InfoType.
0
 

Author Comment

by:vielkacarolina1239
ID: 20403911
Please, can you give me moere details of how to do this. Thanks
0
 

Author Comment

by:vielkacarolina1239
ID: 20403999

Would ItemID in memoryDetail have a constrain or no?
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:vielkacarolina1239
ID: 20404090
You are setting InfoType to be FK. But did not set it to be a primary key in the itemType table, would this be correct? Please, give me more details.
0
 
LVL 27

Assisted Solution

by:MikeToole
MikeToole earned 2000 total points
ID: 20404301
Yes, it would be the PK

There would need to be the same Insert Trigger on each of the detail tables that would attempt an insert on the ItemType table and Rollback the detail table insert  if it fails. For completeness you should also code a delete trigger to remove entries from ItemType on removal from the detail table
0
 

Author Comment

by:vielkacarolina1239
ID: 20405994
HI,

I am doing the following which I think is what youd described above.

My diagram looks like

  TABLE                   FIELD                    FIELD                                 TABLE                       RELATIONSHIP
ItemsTable      --> ItemIDField                                                                                                  
                                    1                                                                                                                ONE
                                     |                                                                                                                  TO
                                                                                                                                                   MANY
ItemTypeTable ---> ITemIDField        InfoTypeField                      
                                                                                                                                                 MANY
                                                          |                 |                                                                            TO
                                                         1                1                                                                          ONE
MemoryDetaiTable  --->  InfoTypeField            InfoTypeField<---ProcessorDetailTable

I  inserted a record in ItemsTable with no problem. Then I inserted a record in ProcessorDetailTable with no problem. However when I tried to insert a record in ItemTypeTable, I get an error. The error reads:

The data in row 1 was not commited.
Error Source: .Net.SqlClient Data Provider
Error Message: The INSERT statement conflicted with the FOREIGN KEY Constraint
FK_InfoTypeTable_ProcessorDetailTable. The conflict occurred in database myDataBaseName, table dbo.ProcessorDetailTable, column ItemID

Please, if you have any idea of what I am doing wrong, let me know. I am already losing my hair on this one. Thanks





0
 

Author Comment

by:vielkacarolina1239
ID: 20406090
The tables and schema are as follow:

CREATE TABLE [dbo].[ItemsTable](
      [ItemID] [int] NOT NULL,
 CONSTRAINT [PK_table1] PRIMARY KEY CLUSTERED
(
      [ItemID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

----------------------------------------------------------------------------------------------------

GO
CREATE TABLE [dbo].[InfoTypeTable](
      [ItemID] [int] NOT NULL,
      [InfoType] [int] NOT NULL,
 CONSTRAINT [PK_Table2] PRIMARY KEY CLUSTERED
(
      [ItemID] ASC,
      [InfoType] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[InfoTypeTable]  WITH CHECK ADD  CONSTRAINT [FK_InfoTypeTable_ItemsTable] FOREIGN KEY([ItemID])
REFERENCES [dbo].[ItemsTable] ([ItemID])
GO
ALTER TABLE [dbo].[InfoTypeTable] CHECK CONSTRAINT [FK_InfoTypeTable_ItemsTable]
GO
ALTER TABLE [dbo].[InfoTypeTable]  WITH CHECK ADD  CONSTRAINT [FK_InfoTypeTable_ProcessorDetailTable] FOREIGN KEY([InfoType])
REFERENCES [dbo].[ProcessorDetailTable] ([ItemID])
GO
ALTER TABLE [dbo].[InfoTypeTable] CHECK CONSTRAINT [FK_InfoTypeTable_ProcessorDetailTable]

----------------------------------------------------------------------------------------------------

GO
CREATE TABLE [dbo].[ProcessorDetailTable](
      [ItemID] [int] NOT NULL,
      [InfoType] [int] NOT NULL,
 CONSTRAINT [AK_InfoType_table4] UNIQUE NONCLUSTERED
(
      [ItemID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

----------------------------------------------------------------------------------------------------

GO
CREATE TABLE [dbo].[MemoryDetailTable](
      [ItemID] [int] NOT NULL,
      [InfoType] [int] NOT NULL,
 CONSTRAINT [IX_table3] UNIQUE NONCLUSTERED
(
      [ItemID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
0
 

Author Comment

by:vielkacarolina1239
ID: 20406188
The schema for InfoType Table above is not conplete. The schema is missing 2 ALTER staments. Howerver, this should steel not work and raise the constraint error as above.  The complte code is the following:

GO
CREATE TABLE [dbo].[InfoTypeTable](
      [ItemID] [int] NOT NULL,
      [InfoType] [int] NOT NULL,
 CONSTRAINT [PK_Table2] PRIMARY KEY CLUSTERED
(
      [ItemID] ASC,
      [InfoType] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[InfoTypeTable]  WITH CHECK ADD  CONSTRAINT [FK_InfoTypeTable_ItemsTable] FOREIGN KEY([ItemID])
REFERENCES [dbo].[ItemsTable] ([ItemID])
GO
ALTER TABLE [dbo].[InfoTypeTable] CHECK CONSTRAINT [FK_InfoTypeTable_ItemsTable]
GO
ALTER TABLE [dbo].[InfoTypeTable]  WITH CHECK ADD  CONSTRAINT [FK_InfoTypeTable_MemoryDetailTable] FOREIGN KEY([InfoType])
REFERENCES [dbo].[MemoryDetailTable] ([ItemID])
GO
ALTER TABLE [dbo].[InfoTypeTable] CHECK CONSTRAINT [FK_InfoTypeTable_MemoryDetailTable]
GO
ALTER TABLE [dbo].[InfoTypeTable]  WITH CHECK ADD  CONSTRAINT [FK_InfoTypeTable_ProcessorDetailTable] FOREIGN KEY([InfoType])
REFERENCES [dbo].[ProcessorDetailTable] ([ItemID])
GO
ALTER TABLE [dbo].[InfoTypeTable] CHECK CONSTRAINT [FK_InfoTypeTable_ProcessorDetailTable]
0
 

Author Comment

by:vielkacarolina1239
ID: 20406415

In the last comment I wrote, "Howerver, this should steel not work and raise the constraint error as above." That is not true. The code does not work only when I insert the extra FK constrain in InfoTypeTable, as above.
0
 
LVL 27

Accepted Solution

by:
MikeToole earned 2000 total points
ID: 20406509
The relationship from the detail tables  to ItemTypeTable should be 1 to 1 on ItemID, that's what keeps the single instance in the detail table.  The InfoType field in ItemTypeTable is there just to say which Detail type consumed the slot.
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question