class ActiveRecord::ConnectionAdapters::PostgreSQLForeignKeyConstraint
Creates FOREIGN KEY constraints for PostgreSQL tables and columns.
This class is meant to be used by PostgreSQL column and table definition and manipulation methods. There are several ways to create a FOREIGN KEY constraint:
-
on a column definition
-
on a table definition
-
when altering a table
Column Definition¶ ↑
When creating a new table via PostgreSQLAdapter#create_table
, you can specify FOREIGN KEY constraints on individual columns during definition.
Example¶ ↑
create_table(:foo) do |t| t.integer :bar_id, :references => { :table => :bar, :column => :id } end # Produces: # # CREATE TABLE "foo" ( # "id" serial primary key, # "bar_id" integer DEFAULT NULL NULL, # FOREIGN KEY ("bar_id") REFERENCES "bar" ("id") # );
You can leave out the :column option if you are following the Rails standards for foreign key referral, as PostgreSQL automatically assumes that it should be looking for a “column_name_id”-style column when creating references. Alternatively, you can simply specify :references => :bar
if you don’t need to add any additional options.
See below for additional options for the :references
Hash.
Table Definition¶ ↑
FOREIGN KEY constraints can also be applied to the table directly rather than on a column definition.
Example¶ ↑
The following example produces the same result as above:
create_table(:foo) do |t| t.integer :bar_id t.foreign_key :bar_id, :bar, :id end # Produces: # # CREATE TABLE "foo" ( # "id" serial primary key, # "bar_id" integer DEFAULT NULL NULL, # FOREIGN KEY ("bar_id") REFERENCES "bar" ("id") # );
Defining a FOREIGN KEY constraint on the table-level allows you to create multicolumn foreign keys. You can define these super advanced foreign keys thusly:
create_table(:foo) {} create_table(:bar) do |t| t.integer :foo_id t.unique_constraint [ :id, :foo_id ] end create_table(:funk) do |t| t.integer :bar_id t.foreign_key [ :id, :bar_id ], :bar, [ :id, :foo_id ] end # Produces: # # CREATE TABLE "foo" ( # "id" serial primary key # ); # # CREATE TABLE "bar" ( # "id" serial primary key, # "foo_id" integer DEFAULT NULL NULL, # UNIQUE ("id", "foo_id") # ); # # CREATE TABLE "funk" ( # "id" serial primary key, # "bar_id" integer DEFAULT NULL NULL, # FOREIGN KEY ("id", "bar_id") REFERENCES "bar" ("id", "foo_id") # );
Table Manipulation¶ ↑
You can also create new FOREIGN KEY constraints outside of a table definition using PostgreSQLAdapter#add_foreign_key
.
Examples¶ ↑
add_foreign_key(:foo, :bar_id, :bar) # => ALTER TABLE "foo" ADD FOREIGN KEY ("bar_id") REFERENCES "bar"; add_foreign_key(:foo, :bar_id, :bar, :id) # => ALTER TABLE "foo" ADD FOREIGN KEY ("bar_id") REFERENCES "bar"("id"); add_foreign_key(:foo, [ :bar_id, :blort_id ], :bar, [ :id, :blort_id ], :name => 'my_fk', :match => :simple ) # => ALTER TABLE "foo" ADD CONSTRAINT "my_fk" FOREIGN KEY ("id", "blort_id") # REFERENCES "bar" ("id", "blort_id") MATCH SIMPLE;
Options for FOREIGN KEY Constraints¶ ↑
-
:deferrable
- sets whether or not the foreign key constraint check is deferrable during transactions. This value can be true for DEFERRABLE, false for NOT DEFERRABLE or a String/Symbol where you can set either:immediate
or:deferred
. -
:name
- sets the name of the constraint. -
:match
- sets how multicolumn foreign keys are matched against their referenced columns. This value can be:full
or:simple
, with PostgreSQL’s default being:full
. -
:on_delete
and:on_update
- set the action to take when the referenced value is updated or deleted. Possible values are:no_action
,:restrict
,:cascade
,:set_null
and:set_default
. PostgreSQL’s default is:no_action
. -
:not_valid
- adds the NOT VALID clause. Only useful when altering an existing table.
See the PostgreSQL documentation on foreign keys for details about the :deferrable
, :match
, :on_delete
and :on_update
options.
Dropping FOREIGN KEY Constraints¶ ↑
Like all PostgreSQL constraints, you can use PostgreSQLAdapter#drop_constraint
to remove a constraint from a table.
Constants
- ACTION_TYPES
- MATCH_TYPES