Create primary key with two columns

I have two tables, bank_data and sec_data. Table bank_data has the columns id, date, asset, and liability. The date column is divided into quarters.

  id    | date     |  asset    | liability           
--------+----------+--------------------
 1      | 6/30/2001|    333860 | 308524
 1      | 3/31/2001|    336896 | 311865
 1      | 9/30/2001|    349343 | 308524
 1      |12/31/2002|    353863 | 322659
 2      | 6/30/2001|    451297 | 425156
 2      | 3/31/2001|    411421 | 391846
 2      | 9/30/2001|    430178 | 41356
 2      |12/31/2002|    481687 | 46589
 3      | 6/30/2001|    106506 | 104532
 3      | 3/31/2001|    104196 | 102983
 3      | 9/30/2001|    106383 | 104865
 3      |12/31/2002|    107654 | 105867 

Table sec_data has columns of id, date, and security. I combined the two tables into a new table named new_table in R using this code:

dbGetQuery(con, "CREATE TABLE new_table
         AS (SELECT sec_data.id, 
         bank_data.date, 
         bank_data.asset, 
         bank_data.liability, 
         sec_data.security
         FROM bank_data,bank_sec
         WHERE (bank_data.id = sec_data.id) AND
         (bank_data.date = sec_data.date)")

I would like to set two primary keys (id and date) in this R code without using pgAdmin. I want to use something like Constraint bankkey Primary Key (id, date) but the AS and SELECT functions are throwing me off.

728x90

3 Answers Create primary key with two columns

You may run these two separate statements ( create table and Insert into )

CREATE TABLE new_table ( 
       id int, date date, asset int, liability int, security int,
        CONSTRAINT bankkey PRIMARY KEY (id, date)
           ) ;


INSERT INTO new_table (id,date,asset,liability,security)
        SELECT s.id, 
           b.date, 
           b.asset, 
           b.liability, 
           s.security
         FROM bank_data b JOIN bank_sec s
         ON b.id = s.id AND b.date = s.date;

Demo

4 months ago

To create the primary key you desire, run the following SQL statement after your CREATE TABLE ... AS statement:

ALTER TABLE new_table
   ADD CONSTRAINT bankkey PRIMARY KEY (id, date);

That has the advantage that the primary key index won't slow down the data insertion.

4 months ago

First your query is wrong.. You say table sec_data but you assign table bank_sec and i am rephrase your query

CREATE TABLE new_table AS 
    SELECT 
       sec_data.id, 
       bank_data.date, 
       bank_data.asset, 
       bank_data.liability, 
       sec_data.security
    FROM bank_data
         INNER JOIN sec_data on bank_data.id = sec_data.id
                             and bank_data.date = sec_data.date

Avoid using Implicit Join and use Explicit Join instead.. And as stated by @ a_horse_with_no_name you can't define more than 1 primary key in 1 table. So what you do are Composite Primary Key

Define :

is a combination of two or more columns in a table that can be used to uniquely identify each row in the table

So you need to Alter Function because Your create statement base on other table..

ALTER TABLE new_table
   ADD PRIMARY KEY (id, date);

4 months ago