How can you create related tables or assign foreign keys between different schema’s?
To deal with cross-schema names for any operation, you just need to qualify table names with their schema names. An example should make this clear.
I have two schemas, “public” and “test”. Public has a table called “TestTable” and “test” has a table called “MyTable”. Here is a statement that adds a foreign key from TestTable’s Id column to MyTable’s Id column.
ALTER TABLE public.TestTable ADD FOREIGN KEY (Id) REFERENCES test.MyTable(Id)Carl provided a solid answer to both questions.
To answer the first - a similar SQL query will work to create related tables within the same schema. If you’re not as proficient with the SQL, most frontends have GUI windows that generate the SQL query for you. They even give you options as to what the DB should do with dependent rows for UPDATE or DELETE queries.
My example uses the free MySQL Query Browser to set up a foreign key on the dept column of the emp_images from the department column to the employees table. It instructs the MySQL to not allow DELETE queries if there is a dependency and to Cascade UPDATE queries.