Solved

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

Posted on 2007-12-04
10
278 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 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
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…

726 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