Class: ActiveRecord::ConnectionAdapters::PostgreSQLForeignKeyConstraint

Inherits:
PostgreSQLConstraint show all
Defined in:
lib/active_record/postgresql_extensions/constraints.rb

Overview

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.

Instance Attribute Summary collapse

Attributes inherited from PostgreSQLConstraint

#base, #options

Instance Method Summary collapse

Methods included from PostgreSQLExtensions::Utils

#hash_or_array_of_hashes, #options_from_hash_or_string, #strip_heredoc

Constructor Details

#initialize(base, columns, ref_table, *args) ⇒ PostgreSQLForeignKeyConstraint

:nodoc:



572
573
574
575
576
577
578
579
580
581
582
583
 
# File 'lib/active_record/postgresql_extensions/constraints.rb', line 572

def initialize(base, columns, ref_table, *args) #:nodoc:
  options = args.extract_options!
  ref_columns = args[0] unless args.empty?

  assert_valid_match_type(options[:match]) if options[:match]
  assert_valid_action(options[:on_delete]) if options[:on_delete]
  assert_valid_action(options[:on_update]) if options[:on_update]
  assert_valid_deferrable_option(options[:deferrable])
  @columns, @ref_table, @ref_columns = columns, ref_table, ref_columns
  @schema = base.current_scoped_schema
  super(base, options)
end

Instance Attribute Details

#columns ⇒ Object

Returns the value of attribute columns.



570
571
572
 
# File 'lib/active_record/postgresql_extensions/constraints.rb', line 570

def columns
  @columns
end

#ref_columns ⇒ Object

Returns the value of attribute ref_columns.



570
571
572
 
# File 'lib/active_record/postgresql_extensions/constraints.rb', line 570

def ref_columns
  @ref_columns
end

#ref_table ⇒ Object

Returns the value of attribute ref_table.



570
571
572
 
# File 'lib/active_record/postgresql_extensions/constraints.rb', line 570

def ref_table
  @ref_table
end

Instance Method Details

#to_sql ⇒ Object Also known as: to_s

:nodoc:



585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
 
# File 'lib/active_record/postgresql_extensions/constraints.rb', line 585

def to_sql #:nodoc:
  sql = String.new
  base.with_schema(@schema) do
    table = if ref_table.respond_to?(:join)
      ref_table.join
    else
      ref_table
    end

    sql << "#{constraint_name}FOREIGN KEY ("
    sql << Array.wrap(columns).collect { |c| base.quote_column_name(c) }.join(', ')
    sql << ") REFERENCES #{base.quote_table_name(table)}"
    sql << ' (%s)' % Array.wrap(ref_columns).collect { |c| base.quote_column_name(c) }.join(', ') if ref_columns
    sql << " MATCH #{options[:match].to_s.upcase}" if options[:match]
    sql << " ON DELETE #{options[:on_delete].to_s.gsub(/_/, ' ').upcase}" if options[:on_delete]
    sql << " ON UPDATE #{options[:on_update].to_s.gsub(/_/, ' ').upcase}" if options[:on_update]
    sql << not_valid
    sql << deferrable
  end
  sql
end