1

Can anybody help me? I want to set constraint to my database for example:

  • First entity is Triangle (Triangle_Id, Name, Side_size)
  • Second entity is Square (Square_Id, Name, Side_size)
  • Third entity is Area (Area_Id, ObjectId, Area_value)
  • Fourth entity is Circle (Circle_Id, Name, Radius)

How can I add constraints from entities Triangle and Square to Area that I am unable to write any other type of objects in ObjectId column except triangle or square?

I tried to write come FK constraints, but have had an error.

  • FK_Area_Triangle_ObjectId (area_ObjectId == triangle_Id)
  • FK_Area_Square_ObjectId (area_ObjectId == square_Id)
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Artur
  • 21
  • 4

2 Answers2

0

You cannot have two different foreign key constraints on same column, but there are some options as answered here: Foreign Key to multiple tables.

  1. You could simply create two columns in Area, SquareId and TriangleId, and have nullable Foreign Keys to each table. You can also add a constraint, that exactly one of these two columns has to be filled.
  2. Model an entity that acts as a base for both Square and Triangle, and have Area refer to that entity
Máté Gábor
  • 96
  • 5
  • Thanks for the advice, but in future, there will be more than 10 entities, and your solution doesn't fit well for me. The example was simplified, the real table has a lot of entities and common values, that's why I'm looking for a method to store only existing values in common table using ObjectId as key. – Artur Jun 01 '20 at 09:32
0

Foreign key is defined between two tables. It cannot be made between a table and a view, which would solve your task directly.

What you can do, if to create a scalar function checking for records in both tables and returning 1 if found and 0, if not. And then put a constraint that the function should be 1.

Alexander
  • 139
  • 8
  • Thanks for the advice, but your solution doesn't fit well for me. But your answer helped me to find one more problem in my solution (connected with transactions). Thanks a lot! – Artur Jun 01 '20 at 10:03