Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Constraints in other schema

When experimenting with the information_schema.* views, I learned that constraints can be defined in another schema. This is reflected in the information_schema.table_constraints view, which adds column such as constaint_schema to denote this:

select * from information_schema.table_constraints

constraint_catalog | constraint_schema | constraint_name | table_catalog | table_schema | table_name | constraint_type | is_deferrable | initially_deferred 

For foreign keys, this makes sense: it is possible to refer to a table in another schema.

I am now wondering if this is also applicable for other constraints. That is, is it somehow possible that a primary, unique or check constraint is defined in another schema as the schema in which the table is defined? In which cases will constraint_schema be different than table_schema in information_schema.table_constraints?

like image 899
schizofrenic_bit Avatar asked Nov 30 '25 15:11

schizofrenic_bit


1 Answers

https://www.postgresql.org/docs/current/static/sql-createindex.html

the index is always created in the same schema as its parent table

https://www.postgresql.org/docs/current/static/ddl-constraints.html

Adding a unique constraint will automatically create a unique B-tree index on the column or group of columns listed in the constraint.

and

Adding a primary key will automatically create a unique B-tree index on the column or group of columns listed in the primary key

Thus pk or unique will surely travel with table in same schema. Regarding check and not null - I can't think of any way to argue why they can't be in different schema, but either I don't see any reason how they could appear in different schema...

like image 75
Vao Tsun Avatar answered Dec 02 '25 06:12

Vao Tsun



Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!