write constraint to check values in 2 tables
-
How to write a constraint that check if a value in a table contains in one of the columns in another table.
Example:
2 Tables:
- Student(Student_number, Name, librarycard_number)
- Librarycard(librarycard_number, borrowed_book, reserved_book, expired_date)
How to write a constraint to check if the values in librarycard_number in table Student is in one of the values of librarycard_number in Libarraycard table?
-
Apply foreign key constraint
My Blog:
http://dineshasanka.spaces.live.com/ -
1) open Student table in design mode
2) on toolbar at top, look for and open icon for "manage relationships"
3) create a new relationship
HTH,
Sam
-
Beside using the manage relationships in the design mode, is there other way to do that?
Because my 2 tables are in different database. I don't think I can do this in manage relationships in design mode. That's why I want to write a constraint for this.
-
Without getting into the reasoning for the two databases, the best solution will probably be triggers. You should put a trigger on each table to check the other table whenever the data changes (INSERT/UPDATE/DELETE). If you perform a lot of transactions on these tables, it could become quite a performance bottleneck.
--------------------
Colt 45 - the original point and click interface
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply