If grid_col.col_id is unique on its own, why not make it the primary key and add a unique constraint on col_id,f_id? If grid_col.col_id is not unique on its own, then I don't know how you can enforce the relationship unless it's in the opposite direction (and grid.col_id is unique there).

Why can't you use a foreign key? This sounds like exactly what they are for.

@KennethFisher I think the issue is at least partially that it would have to be a multi-column foreign key, since there is no explicit constraint on col_id by itself, and the referencing table does not contain f_id.

@Dumper Ok, so that was information we didn't have, and which is why I said if it is unique on its own. You're right that a foreign key won't work here since a foreign key must apply to a unique column in the referenced table. So, can you describe the constraint you actually want here? You only want to allow an insert into grid if the col_id value is present in at least one row in grid_col?

Do you have any other table where col_id is the primary (or unique) key?

It seems like there are some issues with the design here. If col_id is an important entity, why doesn't it also have its own table where it can be the primary key? The existence check can be a foreign key to THAT table, and you can use a trigger to ensure that the col_id value is involved in at least one row in grid_col - if that is in fact the requirement (that is unclear to me). Please add some more specifics to the question.

@Dumper: I think you should describe better what you are modelling exactly. Is it something similar to Excel sheets, where grid stands for "sheet"? Then you could add grid_id to the grid_col table (and include it in the primary key) and then the FK could be easily be from table 1 referencing table 2 (but that's only a wild guess.)

@AaronBertrand I actually considered initially but I thought why create a table with with just an identity column. grid_col is just Map<integer, Map<int,String>> So basically I am looking for a constraint where the grid.col_id should be in grid_col.col_id

Well how are you coming up with col_id values?

@AaronBertrand grid_col is Map<integer, Map<int,String>>. So new col_id is added when there is no Map<int,String> associated with any of the previous Ids.

In English please? I'm a database guy.

@AaronBertrand haha. Say Rows (f_id1,f_value1),(f_id2,f_value2) is associated to col_id1. So If you want create (f_id1,f_value1),(f_id2,f_value2), (f_id3,f_value3), then only new col_id is created.

@Dumper right, but how do you determine the next col_id value? MAX(col_id) + 1?

@AaronBertrand yeah

let us continue this discussion in chat

That is problematic for concurrency reasons - either two sessions can get the same value or you have to serialize. I think you should re-think the IDENTITY column route. Also is there absolutely no other information associated with a col_id? Can you use real terms and column names so we have some concept of what you're trying to model?

In general, if you want to refer to something with an FK, then you want to have an entity with a corresponding PK. If you provide more details about the business logic, we can help you design your tables. Most likely there will be more than two tables.