Solved

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

Posted on 2007-12-04
10
277 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
  • 7
  • 3
10 Comments
 
LVL 27

Assisted Solution

by:MikeToole
MikeToole earned 500 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
Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

 

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 500 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 500 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

856 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