CODEWITHSUNDEEP
ruby-on-railsdatabasepostgresql
Jakxna360
Jakxna360

Reputation: 885

How to create a table that is linked to another table

I was wondering if this is possible, creating a table that is linked to already another existing table.

So I have a table of users that is at 30 columns already. In order to keep it from being too big I want to create a new table called user_data. This will contain information such as the users age, birthday and other information as well.

My question is can you create a table that is linked to another table (through the table id). And can all rows be automatically created due to existing ID.

I am using ruby rails and postgresql. Is something like this possible to do on these platforms. I am new to databases so I don't know if something like this exists.

Upvotes: 1

Views: 1000

Answers (2)

Rumbleweed
Rumbleweed

Reputation: 370

A simple table.

CREATE TABLE employee(
   employee_id int4 serial primary key,
   first_name varchar(40) NOT NULL,
   last_name varchar(40) NOT NULL,
   updated timestamp(6) NOT NULL DEFAULT now()
);

The one to one extra information table. These usually hold related but not critical information. There can be several of these. The one to one relationship is created by the unique and foreign key reference.

CREATE TABLE employee_extra (
   employee_extra_id int4 serial primary key,
   employee_id int4 NOT NULL UNIQUE REFERENCES employee(employee_id),
   birthday timestamp(6) NULL,
   updated timestamp(6) NOT NULL DEFAULT now()
);

Create a trigger to populate the extra table when the original is created.

CREATE OR REPLACE FUNCTION employee_after_insert_trigger() RETURNS TRIGGER AS $emp_trigger$
    BEGIN
        --
        -- Create a row in employee_extra matching the just created row in employee
        --
        INSERT INTO employee_extra (employee_id, updated) SELECT employee_id, updated FROM NEW;
        RETURN NEW;
    END;

$emp_trigger$ LANGUAGE plpgsql;

CREATE TRIGGER employee_after_insert
  AFTER UPDATE
  ON employee
  FOR EACH ROW
  EXECUTE PROCEDURE employee_after_insert_trigger();

Haven't tried it, but it, or something like it, should work.

Upvotes: 0

theDrifter
theDrifter

Reputation: 1696

The siplest way would be to setup a has_one belongs_to association. So one user has_one user_data record and a user_data record belongs_to a user. So you have to insert the primary_key of the user record as a foreign_key inside the user_data record. Here is some good reading material

http://edgeguides.rubyonrails.org/active_record_migrations.html#foreign-keys

The migration should someting look like this to add the user key to the user_data table:

class AddForeignKey < ActiveRecord::Base
 def self.up
   add_foreign_key :user_data_sets, :users
 end
 def self.down
   remove_foreign_key :user_data_sets, :users
 end
end

Upvotes: 1

Related Questions