Thursday, June 16, 2005

Common Table Column Types

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:

SUBTYPE table_id IS VARCHAR2(32);

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:

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
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.

Here are some loosely related previous blogs on this:

This comment has been removed by a blog administrator.
Post a Comment

<< Home

This page is powered by Blogger. Isn't yours?