12

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.

user50849
  • 263
  • 1
  • 3
  • 12
  • 2
    Have you considered a view of the table? –  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 – Philᵀᴹ 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 02:32
  • As @miracle173 asked, if you want the two tables to behave exactly the same and have the same data always, why would you need 2 of them? – ypercubeᵀᴹ May 26 '12 at 07:29
  • I don't need two tables, but the application in question is designed so it won't function otherwise. Background question – user50849 May 26 '12 at 07:53
  • but if the appplication writes always in both tables that are basically the same table because you use an "alias" or a "symlink" then I would expect that you write each dataset twice in that table. – miracle173 May 26 '12 at 20:39
  • Oh, right, I didn't think about that. :/ Thanks for pointing it out. I'll see if I can think of a meaningful rewording of the question. – user50849 May 26 '12 at 22:24

2 Answers2

6

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

a1ex07
  • 9,000
  • 3
  • 24
  • 40
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

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
Community
  • 1
RolandoMySQLDBA
  • 182,700
  • 33
  • 317
  • 520