Solved

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

Posted on 2007-12-04
10
273 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
Comment Utility
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
Comment Utility
Please, can you give me moere details of how to do this. Thanks
0
 

Author Comment

by:vielkacarolina1239
Comment Utility

Would ItemID in memoryDetail have a constrain or no?
0
 

Author Comment

by:vielkacarolina1239
Comment Utility
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
Comment Utility
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

Author Comment

by:vielkacarolina1239
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility

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
Comment Utility
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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

743 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now