OracleBlog

I have two tables which are created in an SQL file, and I want to have a column in common in both.

CREATE TABLE atable (atable_id VARCHAR2(32), atable_value NUMBER);
CREATE TABLE btable (btable_id VARCHAR2(32), btable_name VARCHAR2(32));

However, I don't want to have to rely on manual verification to make sure they are the same type. And if I want to change them for a future version, I'd rather only change it in one place.

I can't do this (abbreviated example):

CREATE TABLE atable (atable_id VARCHAR2(32));
SQL> CREATE TABLE btable (btable_id atable.atable_id%TYPE);
CREATE TABLE btable (btable_id atable.atable_id%TYPE)
*
ERROR at line 1:
ORA-00911: invalid character

And I can't do this:

CREATE OR REPLACE PACKAGE table_types
AS
SUBTYPE table_id IS VARCHAR2(32);
END;

SQL> CREATE TABLE atable (atable_id table_types.table_id);
CREATE TABLE atable (atable_id table_types.table_id)
*
ERROR at line 1:
ORA-00902: invalid datatype

So what should we do?

If there is a logical link between these two fields, we can use referential integrity constraints (thanks "hsheehan"):

SQL>CREATE TABLE atable(atable_id VARCHAR2(32), atable_value NUMBER);

Table created.

SQL>ALTER TABLE atable ADD UNIQUE(atable_id);

Table altered.

SQL>CREATE TABLE btable(btable_id REFERENCES atable(atable_id), btable_name VARCHAR2(32));

Table created.

However, this will require that every atable.atable_id be unique (ORA-02270), and it will also require that every btable.btable_id exists in atable.atable_id (ORA-02291)

This may not always be appropriate, because sometimes there is no such relationships between columns. You may simply want all columns in a database that are somehow similar (example: people's names) to be the same type. You may be doing this for convenience or because you have common functions on these columns.

In that case, you may need to resort to a 3rd-party schema modeller. After all, that's what they're for.

Otherwise, you can create a file of tags, use these tags in a "pre-SQL" file, then write a Perl script to do text substitutions to generate the SQL file. For example:

Tags.txt:
ID_TYPE VARCHAR2(32)
CreateTable.pre:
CREATE TABLE ATABLE (atable_id ID_TYPE, atable_value NUMBER);
CREATE TABLE BTABLE (btable_id ID_TYPE, btable_name VARCHAR2(32));
Execute your Perl script here
CreateTable.sql:
CREATE TABLE ATABLE (atable_id VARCHAR2(32), atable_value NUMBER);
CREATE TABLE BTABLE (btable_id VARCHAR2(32), atable_name VARCHAR2(32));

Thanks to the folks at the Dizwell Forum for helping me think this one through.
http://www.phpbbserver.com/phpbb/viewtopic.php?t=179&mforum=dizwellforum&sid=6802

Here are some loosely related previous blogs on this:
http://thinkoracle.blogspot.com/2005/05/dynamically-assigning-size-of-varchar2.html
http://thinkoracle.blogspot.com/2005/05/null-vs-nothing.html