Create a foreign key from two tables

Question

Create a foreign key from two tables

river1

SSC-Insane

Points: 24315
More actions
March 31, 2011 at 6:41 am

#147770

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?
Thank you

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323546 More actions · Answer 1

a foreign key has to reference either a primary key or unique constraint in a single table

so if the predios.ID_predios is one of those, you could have a FK to that table, and then a separate, additional FK to fogos.ID_fogos .

alternatively, you could create a new table with all allowed combinations of ID_predios,ID_fogos, put a unique constraint on the two columns, and use that for the FK.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

river1 SSC-Insane Points: 24315 More actions · Answer 2

Both (id_Predio and Id_fogo) are primary keys in their tables.

I have tried what you say.

Create 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 you help?

Thank you

Lowell SSC Guru Points: 323546 More actions · Answer 3

this error means the FK already exists:

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'.

Since you are using the GUI,

just create the first FK and don't add FK_AVALIACOES_FOGOS, or add it a second time with a different constraint name.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

river1 SSC-Insane Points: 24315 More actions · Answer 4

I don't think so.

I think that what happens it's this:

I create a new FK referencing the table predios (Id_predio)

I then create another FK referencing table fogos (id_fogo)

When i try to save changes it gives me the error that i reported.

This happens because table predio (id_predio) does not have all the values that the table avaliacoes as.

The same happens with table fogos (id_fogo).

I can only create this two foreign keys when i refere that no data will be verify at constraint creation...

This means that SQL Server probably tries to create the first constraint (FK_predio) and befour it tryes to create the second contraction (FK_fogos) it detects that FK_predios does not meet all requirements because there are data in table avaliacao that it's not in table predio.

What do you think?

Lowell SSC Guru Points: 323546 More actions · Answer 5

can you exec sp_help fogos ?; isn't that FK already there? i think that;s what that error means.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

David Burrows SSC Guru Points: 65144 More actions · Answer 6

If I understand you correctly then you cannot have a single ID in your third table as a FK to two other tables (predios & fogos) unless the ID exists in both tables.

You would have to put both ID's Id_predio and Id_fogo in your third table as NULLable columns and both have FK to their appropriate table

Far away is close at hand in the images of elsewhere.
Anon.

river1 SSC-Insane Points: 24315 More actions · Answer 7

"If I understand you correctly then you cannot have a single ID in your third table as a FK to two other tables (predios & fogos) unless the ID exists in both tables."

Yes, SQL Server don't let me have a column that references two diferente columns without the value inserted 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 and the table avaliacoes will reference the column of the view

river1 SSC-Insane Points: 24315 More actions · Answer 8

What i need is a trigger or a check constranit to get referencial integrity.

Can someone help me on making a trigger to do this?