pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: Use of reference table ('look-up-table') and FK constraint
5+ messages / 3 participants
[nested] [flat]
* Use of reference table ('look-up-table') and FK constraint
@ 2002-10-16 17:14 Charles Hauser <chauser@duke.edu>
2002-10-15 17:27 ` Re: Use of reference table ('look-up-table') and FK constraint Josh Berkus <josh@agliodbs.com>
2002-10-16 17:26 ` Re: Use of reference table ('look-up-table') and FK constraint Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 2 replies; 5+ messages in thread
From: Charles Hauser @ 2002-10-16 17:14 UTC (permalink / raw)
To: pgsql-sql
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')
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
)
go
exec sp_bindrule Phone_type_rule, 'Phone.type'
go
regards,
Charles
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Use of reference table ('look-up-table') and FK constraint
2002-10-16 17:14 Use of reference table ('look-up-table') and FK constraint Charles Hauser <chauser@duke.edu>
@ 2002-10-15 17:27 ` Josh Berkus <josh@agliodbs.com>
2002-10-17 14:06 ` Re: Use of reference table ('look-up-table') and FK constraint Charles Hauser <chauser@duke.edu>
1 sibling, 1 reply; 5+ messages in thread
From: Josh Berkus @ 2002-10-15 17:27 UTC (permalink / raw)
To: Charles Hauser <chauser@duke.edu>; pgsql-sql
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 Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Use of reference table ('look-up-table') and FK constraint
2002-10-16 17:14 Use of reference table ('look-up-table') and FK constraint Charles Hauser <chauser@duke.edu>
2002-10-15 17:27 ` Re: Use of reference table ('look-up-table') and FK constraint Josh Berkus <josh@agliodbs.com>
@ 2002-10-17 14:06 ` Charles Hauser <chauser@duke.edu>
2002-10-17 15:34 ` Re: Use of reference table ('look-up-table') and FK constraint Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 1 reply; 5+ messages in thread
From: Charles Hauser @ 2002-10-17 14:06 UTC (permalink / raw)
To: josh@agliodbs.com; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Use of reference table ('look-up-table') and FK constraint
2002-10-16 17:14 Use of reference table ('look-up-table') and FK constraint Charles Hauser <chauser@duke.edu>
2002-10-15 17:27 ` Re: Use of reference table ('look-up-table') and FK constraint Josh Berkus <josh@agliodbs.com>
2002-10-17 14:06 ` Re: Use of reference table ('look-up-table') and FK constraint Charles Hauser <chauser@duke.edu>
@ 2002-10-17 15:34 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 0 replies; 5+ messages in thread
From: Stephan Szabo @ 2002-10-17 15:34 UTC (permalink / raw)
To: Charles Hauser <chauser@duke.edu>; +Cc: josh@agliodbs.com; pgsql-sql
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Use of reference table ('look-up-table') and FK constraint
2002-10-16 17:14 Use of reference table ('look-up-table') and FK constraint Charles Hauser <chauser@duke.edu>
@ 2002-10-16 17:26 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
1 sibling, 0 replies; 5+ messages in thread
From: Stephan Szabo @ 2002-10-16 17:26 UTC (permalink / raw)
To: Charles Hauser <chauser@duke.edu>; +Cc: pgsql-sql
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
);
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2002-10-17 15:34 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-10-16 17:14 Use of reference table ('look-up-table') and FK constraint Charles Hauser <chauser@duke.edu>
2002-10-15 17:27 ` Josh Berkus <josh@agliodbs.com>
2002-10-17 14:06 ` Charles Hauser <chauser@duke.edu>
2002-10-17 15:34 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-10-16 17:26 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox