foreign keys - reference two different tables

Last Post 01 Apr 2011 09:17 PM by russellb. 6 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
river1
New Member
New Member

--
31 Mar 2011 06:44 AM
Hi,

I have one table named predios (field ID_predios) which has some numbers. I have a second table named fogos (field ID_fogos) which have other number.

I then have a third table named avaliacoes which has a column where values entered most be from one of the values inside ID_fogos or ID_predios.

How can i create a foreign key that reference this two columns from this two tables?

Both (id_Predio and Id_fogo) are primary keys in their tables. I have tried this:

 Created a foreign key between table fogos and table avaliacoes and then create a second foreign key between table predios and table avaliacoes. It always raises an error:

'PREDIOS' table saved successfully 'FOGOS' table saved successfully 'AVALIACOES' table - Unable to create relationship 'FK_AVALIACOES_FOGOS'. The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_AVALIACOES_FOGOS". The conflict occurred in database "SGIPU3", table "dbo.FOGOS", column 'NumFogo'. i have seen if there are values in table avaliacoes that do not exit in tables predio and fogos. There where , so i deleted them and tried again, still same errror...

Can someone help?

Thank you
russellb
New Member
New Member

--
31 Mar 2011 07:09 AM
You can create a check constraint or a trigger for this, but a single FK can't reference 2 tables.

Probably, you want another table containing the valid IDs from Predio and Fogo, and have the avaliacoes table reference that
river1
New Member
New Member

--
31 Mar 2011 07:24 AM
"Probably, you want another table containing the valid IDs from Predio and Fogo, and have the avaliacoes table reference that"

Such table doesn't exist in the database.

If I understand SQL Server error correctly, then i cannot have a single ID in my third table as a FK to two other tables (predios & fogos) unless the ID value exists in both tables."

I think that SQL Server don't let me have a column that references two diferente columns without the value inserted in this column is on both columns (id_predio and id_fogo)...

It's strange but it seems like SQL Server functions this way.

Maybe i can create a view with a join of this two tables fields (id_predio and id_fogos) and the table avaliacoes will reference the column of the view




river1
New Member
New Member

--
31 Mar 2011 07:36 AM
I have created a new view named Test with is the result of the union of this two fileds (id_predio and id_fogo) but i know (after testing) that i can not create a FK on table avaliacao based on the column codigo of the view Test because a column to serve as FK as two be unique and i can not create that on a view...

I was thinking which is better? create a check contraint or cretae a trigger two verify referencial integrity in this case?

russellb
New Member
New Member

--
31 Mar 2011 09:07 PM
Check constraint. But I have to say that the design is flawed when you find the situation you're in.

If you can't change the schema, then I'd make a check constraint that calls a function:

Create Function dbo.isValidPredioOrFogoID (@id int)
RETURNS BIT
AS
BEGIN
IF EXISTS (SELECT 1 FROM dbo.Predio WHERE ID_Predio = @id)
BEGIN
RETURN 1
END
IF EXISTS (SELECT 1 FROM dbo.Fogo WHERE ID_Fogo = @id)
BEGIN
RETURN 1
END
Return 0
END
GO

ALTER TABLE AVALIACOES
ADD CONSTRAINT CHECK (dbo.isValidPredioOrFogoID(AVALIACOES_Column) = 1);
GO
river1
New Member
New Member

--
01 Apr 2011 01:50 AM
Can't change the database schema....
russellb
New Member
New Member

--
01 Apr 2011 09:17 PM
Then I'll go with a check constraint. Should perform fairly well, as both of the fields it references are unique and clustered.
You are not authorized to post a reply.

Acceptable Use Policy