Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
Table1: grid_col (col_id,f_id,f_value) 
    (col_id,f_id) is primary key. 

Table2: grid (grid_id,col_id,text) 
    (grid_id) is primary key. 

I want to have a constraint for grid that, col_id should be present in grid_col. I can't have foriegn key constraint here. I can create a function constraint which scans the grid_col while inserting in grid but in that case it increases the chances of having deadlock. How to add a constriant here?

share|improve this question
    
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). –  Aaron Bertrand Jul 9 '13 at 13:44
1  
@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? –  Aaron Bertrand Jul 9 '13 at 14:29
1  
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. –  Aaron Bertrand Jul 9 '13 at 14:30
2  
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? –  Aaron Bertrand Jul 9 '13 at 14:49
1  
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. –  AlexKuznetsov Sep 8 '13 at 20:56

1 Answer 1

You could create a TRIGGER on the grid table that will do an outer join on the inserted values and the grid_col table.This should give you NULL values for non-matched records which you can than remove from the grid table.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.