Solved

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

Posted on 2007-12-04
10
276 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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

777 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