Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have two tables with the same structure, A and B. A certain application is written so that it always writes the same data to both tables.

After a discussion with a colleague about the potential to save drive space, I'm wondering if mysql or postgresql has the ability to create on table as an "alias", or "symlink", of another.

I want the behaviour to be very similar to that of a soft file symlink; where reading from either the symlink itself or it's target will yield the same output, and writing to either will update the target.

share|improve this question
2  
Have you considered a view of the table? –  user1240 May 25 '12 at 23:09
    
In SQL Server you could just use a view. –  JNK May 25 '12 at 23:10
1  
Which RDBMS? You've tagged MySQL and Postgres. Views will work in most RDBMSes. Oracle has synonyms, which are the same as Unix symbolic links –  FreshPhilOfSO May 25 '12 at 23:11
    
MySQL and Postgres, yes. I mention them specifically in the text as well. I thought Views was somehow not quite what I was looking for, but perhaps it will be just fine. Thanks a lot. Post some answers :) –  user50849 May 25 '12 at 23:15
1  
and why do you need two tables? –  miracle173 May 26 '12 at 2:32
show 4 more comments

2 Answers

As far as I know, a new Postgresql lets you have INSTEAD OF triggers on views. So having one table, one view as SELECT * FROM table1, and INSTEAD OF trigger for insert, update, delete should work for you. This approach is not gonna work in Mysql though

share|improve this answer
add comment

It is possible in MySQL (using MyISAM storage engine only) to create a table from scratch using symlinks. It is posssible in Linux and Windows (using hardlinks) :

Here are my past posts on this subject

However, what you are proposing would have to be done outside of MySQL in Linux.

For this example

  • /var/lib/mysql is datadir
  • Create table1 as MyISAM table in database mydb
  • Create table2 as pure symlinks to table1

STEP 01) Create table1

CREATE TABLE mydb.table1
(
    id int not null auto_increment,
    mydata varchar(255) not null,
    primary key (id)
) ENGINE=MyISAM;

STEP 02) Create three symlinks to mimic TableB

cd /var/lib/mysql/mydb
ln -s table1.frm table2.frm
ln -s table1.MYD table2.MYD
ln -s table1.MYI table2.MYI

STEP 03) Try inserting into table1 and reading from table2. Then try the reverse.

INSERT INTO table1 (mydata) VALUES ('rolando'),('edwards');
SELECT * FROM table2;
INSERT INTO table2 (mydata) VALUES ('abraham'),('lincoln');
SELECT * FROM table1;

If everything behaves normal, then this is how you can do this.

CAVEAT

  1. There is only one table, table1
  2. If you do any DDL
    • Perform the DDL on table1
    • You must recreate the table2 symlinks after DDL against table1
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.