CODEWITHSUNDEEP
mysqlsql
user2129623
user2129623

Reputation: 2257

Creating table in which with primary key is combination of two fields in two different table

I want to create table which contains primary key which has fields of two different tables.

URL_TABLE

---------            
url varchar(255)    No           
desc    varchar(2048)   No           
preview varchar(255)    No           
img_url varchar(128)    No           
title   varchar(128)    No           
hash    varchar(128)    No       // This is one  
rate    varchar(20)     Yes     NULL

USER

------
id      varchar(40) No       //This is 2nd
name    varchar(50) Yes     NULL         
email   varchar(50) Yes     NULL         
picture varchar(50) No

Query:

create table post_table as 
    select id 
    from USER 
  UNION 
    select url, desc, preview, img_url, title, hash, rate 
    from URL_TABLE 
primary key (id,hash);

Is this a correct query?

Upvotes: 0

Views: 142

Answers (1)

Dan Bracuk
Dan Bracuk

Reputation: 20804

From this comment, "My purpose is on my page I have user's running session(which will give me his ID) and I want to bind his action on each URL he processed. So post_table will contains id from user table and rest fields from url table"

You are describing a many to many relationship between users and urls. A user can process more than one url and a url can be processed by more than one user.

A better way to store this data would be with a table that had the user_id, url, and datetime_processed as the primary key. You can get other information about the url from the url table with a simple join.

Upvotes: 3

Related Questions