Printable View
Is there a way to test integrity constraints between a table in your schema and a table in someone else's schema(that you have select, insert, update and delete privileges on)? I tried to create a constraint between a table in my schema and a table in someone else's schema and I got an insufficient privileges error.
Is there a privilege that we can set for developers to allow them to create constraints accross schema's with out giving them all the alter table privilege?
If the parent table in foreign key constraint is not in your own schema you have to have REFERENCES privilege on that table granted by it's owner. Unfortunately there is no appropriate system privilege for that (something like REFERENCES ANY TABLE), so you have to get REFERENCES privilege directly from that user for each table you want to referenc in your FK constraints. BTW, REFERENCES privilege can be limited on a particular column(s) if the owner does not want to grant it on a whole table.