Thread: Use of reference table ('look-up-table') and FK constraint

Hi,

In fits and starts I am working through converting a sybase schema ->
postgres and am hoping to gain some insight on the use of reference
tables ('look-up-table') and FK constraints. 

In the example below I believe the sybase RULE Phone_type_rule is used
to restrict input, so that the only values which can be inserted or
updated into Phone have to be one of ('work', 'home', 'fax', 'mobile',
'lab', 'unknown').

How would this be accomplished using a look-up or reference table::FK
constraint combination?


CREATE RULE Phone_type_rule AS @col IN ('work', 'home', 'fax', 'mobile', 'lab', 'unknown')
goCREATE TABLE Phone (       phone_id             Identifier_type IDENTITY,       phone_number         varchar(20) NOT
NULL,      community_id         numeric(12) NOT NULL,       type                 varchar(10) NULL)
 
goexec sp_bindrule Phone_type_rule, 'Phone.type'
go
regards,

Charles

On 16 Oct 2002, Charles Hauser wrote:

> In fits and starts I am working through converting a sybase schema ->
> postgres and am hoping to gain some insight on the use of reference
> tables ('look-up-table') and FK constraints.
>
> In the example below I believe the sybase RULE Phone_type_rule is used
> to restrict input, so that the only values which can be inserted or
> updated into Phone have to be one of ('work', 'home', 'fax', 'mobile',
> 'lab', 'unknown').
>
> How would this be accomplished using a look-up or reference table::FK
> constraint combination?
>
>  CREATE RULE Phone_type_rule
>   AS @col IN ('work', 'home', 'fax', 'mobile', 'lab', 'unknown')
> go
>
>  CREATE TABLE Phone (
>         phone_id             Identifier_type IDENTITY,
>         phone_number         varchar(20) NOT NULL,
>         community_id         numeric(12) NOT NULL,
>         type                 varchar(10) NULL
>  )

I think something like this:

create table phone_types (typename varchar(10) primary key;
);
-- insert types into phone_types
create table phone(-- ...type varchar(10) references phone_types
);

Charles,

CREATE TABLE phone_types (type VARCHAR(10) NOT NULL PRIMARY KEY
);

INSERT INTO phone_types
VALUE ( 'Work' );
INSERT INTO phone_types
VALUE ( 'Home' );
etc ...

CREATE TABLE phone (        phone_id             Identifier_type IDENTITY,        phone_number         varchar(20) NOT
NULL,       community_id         numeric(12) NOT NULL,        type                 varchar(10) NULL REFERENCES
phone_types(type)ON  
DELETE SET NULL );

Simple, neh?   And it lets you add new types, at any time, without re-building
the table.   IMHO, the guy who built the database you're converting was
either a total hack or working around some serious platform shortcomings.

BTW, are you sure you want phone.type to be NULLable?   Were it me, I would
make it required, in which case the declaration would be:
        type                 varchar(10) NOT NULL REFERENCES
phone_types(type)

--
-Josh BerkusAglio Database SolutionsSan Francisco

Josh & Stephan,

This helps a lot - thanks!

Any idea what datatype 'Identifier_type IDENTITY' specifies?  I don't
see this in the postgres documentation.  I'll see what I can find in
sybase lingo.


CREATE TABLE phone (        phone_id             Identifier_type IDENTITY,        phone_number         varchar(20) NOT
NULL,       community_id         numeric(12) NOT NULL,        type                 varchar(10) NULL REFERENCES
 
phone_types(type) ON 
DELETE SET NULL );

regards,

Charles

On 17 Oct 2002, Charles Hauser wrote:

> Josh & Stephan,
>
> This helps a lot - thanks!
>
> Any idea what datatype 'Identifier_type IDENTITY' specifies?  I don't
> see this in the postgres documentation.  I'll see what I can find in
> sybase lingo.

As a rough guess, I'd say probably a serial column,
"phone_id serial primary key" is likely.