Can I have one table be an "alias", or "symlink", to another?

Question

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.

Answer 1

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

Answer 2

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

• Can I specify a different disk for a specific MySQL table?
• Dynamically changeable database

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