Обсуждение: Constraint of only one legal value for a foreign key
I have three tables (actually more :-)
Orgs
OrgTypes
MeetGrpDescs
OrgTypes is a foreign key in Orgs, and says what type of Org an Org is
It can be one of these:
'World Service Office', 'Area', 'District',
'Group', 'Region', 'AIS', 'LDC', 'AIS/LDC'
MeetGrpDescs is a table with various further attributes of ONLY the
Orgs with type of 'Group'.
How do I enforce that the records inserted into MeetGrpDescs are:
1/ An Org (done by foreign key constraints aleady, correct?)
2/ Are of type 'Group'
Here is the DDL for the three tables:
/* organizations -----------------------------------------*/
CREATE TABLE Orgs(
org_id serial NOT NULL,
org_type_id int4 NOT NULL,
grp_type_id int4,
grp_status_id int4,
org varchar(64) NOT NULL,
wsoid varchar(16) DEFAULT 'none' NOT NULL,
created timestamp NOT NULL,
modified timestamp,
archived timestamp,
CONSTRAINT FK_An_Org_Has_An_OrgType_1 FOREIGN KEY (org_type_id) REFERENCES OrgTypes (org_type_id),
CONSTRAINT FK_An_Org_Which_Has_OrgType_Of_Group_Has_A_MeetGrpDesc_2 FOREIGN KEY
(grp_type_id,grp_status_id) REFERENCES MeetGrpDescs (grp_type_id,grp_status_id),
CONSTRAINT PK_Orgs1 PRIMARY KEY (org_id,org_type_id));
COMMENT ON COLUMN Orgs.org_id IS 'integer surr primary key';
COMMENT ON COLUMN Orgs.org_type_id IS 'integer foreign key, for relating an OrgType to zero to
many Orgs';
COMMENT ON COLUMN Orgs.org IS 'Name of Org(en, utf-8)';
COMMENT ON COLUMN Orgs.wsoid IS 'needs a constraint where the value is 'none' or unique among
values that are NOT 'one' CHANGE THIS TO -1 if no number and positive if IS number, if WSOID is a
pure number.';
CREATE INDEX IDX_WSOID ON Orgs (wsoid);
/* type of organizations -----------------------------------------*/
CREATE TABLE OrgTypes(
org_type_id serial NOT NULL CONSTRAINT PK_OrgTypes1 PRIMARY KEY,
org_type varchar(32) NOT NULL,
wsoid_reqd bool NOT NULL,
created timestamp NOT NULL,
modified timestamp,
archived timestamp,
CONSTRAINT UC_OrgTypes1 UNIQUE(org_type));
COMMENT ON COLUMN OrgTypes.org_type_id IS 'integer surr primary key';
COMMENT ON COLUMN OrgTypes.org_type IS 'Name of OrgType(en, utf-8)';
COMMENT ON COLUMN OrgTypes.wsoid_reqd IS '0 if WSOID *NOT* required, 1 if WSOID *IS* required';
/* Descriptions for Orgs of type 'Group' organizations -----------------------------------------*/
CREATE TABLE MeetGrpDescs(
org_id int4 NOT NULL,
org_type_id int4 NOT NULL,
grp_type_id int4 NOT NULL,
grp_status_id int4 NOT NULL,
special_notes text DEFAULT 'none' NOT NULL,
mail_returned bool DEFAULT 0 NOT NULL,
created timestamp NOT NULL,
modified timestamp,
archived timestamp,
CONSTRAINT FK_A_MeetGrp_Desc_Has_A_Grp_Status_1 FOREIGN KEY (grp_status_id) REFERENCES GrpStatuses
(grp_status_id),
CONSTRAINT FK_A_MeetGrpDesc_Has_A_ValidGrpType_2 FOREIGN KEY (grp_type_id) REFERENCES
ValidGrpTypes (grp_type_id),
CONSTRAINT FK_An_Org_Which_Has_OrgType_Of_Group_Has_A_MeetGrpDesc_3 FOREIGN KEY
(org_id,org_type_id) REFERENCES Orgs (org_id,org_type_id),
CONSTRAINT PK_MeetGrpDescs1 PRIMARY KEY (org_id,org_type_id,grp_type_id,grp_status_id));
COMMENT ON COLUMN MeetGrpDescs.org_id IS 'integer foreign key, for relating an Org of type
'group' to zero to many MeetGrpDescs';
COMMENT ON COLUMN MeetGrpDescs.org_type_id IS 'integer foreign key, for: 1/ reference integrity
deletions, 2/ verifying only orgs with 'group' type in this table';
COMMENT ON COLUMN MeetGrpDescs.grp_type_id IS 'integer foreign key, for relating a ValidGrpType to
zero to many MeetGrpDescs';
COMMENT ON COLUMN MeetGrpDescs.grp_status_id IS 'integer foreign key, for relating a GrpStatus to
zero to many MeetingGrpDescs';
COMMENT ON COLUMN MeetGrpDescs.special_notes IS 'special entrance directions, babysitting, etc(en,
utf-8)';
COMMENT ON COLUMN MeetGrpDescs.mail_returned IS '0 if no mail recently returned, NOT 0 if mail
recently rerturned. 'recently' to be defined';
/* Fixed values for 'OrgTypes' table */
INSERT INTO OrgAddrTypes( org_type, wsoid_reqd ) VALUES 'World Service Office', 0;
INSERT INTO OrgAddrTypes( org_type, wsoid_reqd ) VALUES 'Area', 1;
INSERT INTO OrgAddrTypes( org_type, wsoid_reqd ) VALUES 'District', 1;
INSERT INTO OrgAddrTypes( org_type, wsoid_reqd ) VALUES 'Group', 1;
INSERT INTO OrgAddrTypes( org_type, wsoid_reqd ) VALUES 'Region', 0;
INSERT INTO OrgAddrTypes( org_type, wsoid_reqd ) VALUES 'AIS', 1;
INSERT INTO OrgAddrTypes( org_type, wsoid_reqd ) VALUES 'LDC', 1;
INSERT INTO OrgAddrTypes( org_type, wsoid_reqd ) VALUES 'AIS/LDC', 1;
I was trying to connect to Pg 7.3.1 using perl and DBD::Pg 1.21, however the following error occurs:
$ perl pg_connect.pl
FATAL: No pg_hba.conf entry for host 164.35.10.17, user pgsql, database slave
DBI connect('dbname=slave;host=164.35.10.17;port=5432','pgsql',...) failed: FATAL: No pg_hba.conf entry for host
164.35.10.17,user pgsql, database slave at pg_connect.pl line 16
$
Does anybody know what is wrong? Weird that the same function in perl works fine in 7.2.3....
Thanks.
-----------------------------
Renê Salomão
Ibiz Tecnologia -- www.ibiz.com.br
Versão:
[pgsql@slave:5432]#select version();
-[ RECORD 1 ]--------------------------------------------------------
version | PostgreSQL 7.3.1 on i586-pc-linux-gnu, compiled by GCC 2.96
$ psql -l -p 5432
List of databases
Name | Owner
-----------+-------
slave | pgsql
template0 | pgsql
template1 | pgsql
(3 rows)
PG_HBA.CONF:
# TYPE DATABASE USER IP-ADDRESS IP-MASK METHOD
local all all trust
host all all 127.0.0.1 255.255.255.255 trust
host all all 164.35.10.17 255.255.0.0 trust
Versão:
$ perl -e 'use DBI; print "$DBI::VERSION\n"' --> 1.32
$ perl -e 'use DBD::Pg; print "$DBD::Pg::VERSION\n";' --> 1.21
Função Perl:
#!/usr/bin/perl
use DBI;
use DBD::Pg;
use strict;
my $dbh;
my $dbname='slave';
my $host='164.35.10.17';
my $port='5432';
my $options='';
my $username='pgsql';
my $password='pgsql';
$dbh = DBI->connect("dbi:Pg:dbname=$dbname;host=$host;port=$port",
"$username",
"$password",
{ RaiseError => 1, AutoCommit => 0 }) || die $DBI::errstr;
print "Connected!!!\n";
my $hSt = $dbh->prepare('SELECT * FROM table;');
$hSt->execute;
while(my @raD = $hSt->fetchrow_array()) { print "$raD[0]\t$raD[1]\n";}
$hSt->finish;
$dbh->disconnect;
print "Disconnected!!!\n";
=?ISO-8859-1?Q?Ren=EA_Salom=E3o?= <rene@ibiz.com.br> writes:
> I was trying to connect to Pg 7.3.1 using perl and DBD::Pg 1.21, however the following error occurs:
> $ perl pg_connect.pl
> FATAL: No pg_hba.conf entry for host 164.35.10.17, user pgsql, database slave
The error message is pretty specific. Are you sure you edited the right
pg_hba.conf? Did you SIGHUP the postmaster after you edited it?
regards, tom lane
You need to add 164.35.10.17 to your pg_hba.conf file and do a kill -HUP
on the postmaster pid.
_/_/ _/_/ _/_/ Tariq Muhammad
_/ _/ _/ _/ _/ _/ tariq@libertyrms.info
_/ _/ _/_/_/ _/_/_/ v:416-646-3304 x 111
_/ _/ _/ _/ _/ _/ c:416-455-0272
_/_/_/ _/_/_/ _/ _/ p:416-381-1457
_________________________________________________
Liberty Registry Management Services Co.
On Thu, 23 Jan 2003, Renê Salomão wrote:
> I was trying to connect to Pg 7.3.1 using perl and DBD::Pg 1.21, however the following error occurs:
>
> $ perl pg_connect.pl
> FATAL: No pg_hba.conf entry for host 164.35.10.17, user pgsql, database slave
> DBI connect('dbname=slave;host=164.35.10.17;port=5432','pgsql',...) failed: FATAL: No pg_hba.conf entry for host
164.35.10.17,user pgsql, database slave at pg_connect.pl line 16
> $
>
> Does anybody know what is wrong? Weird that the same function in perl works fine in 7.2.3....
>
>
> Thanks.
> -----------------------------
> Renê Salomão
> Ibiz Tecnologia -- www.ibiz.com.br
>
>
> Versão:
> [pgsql@slave:5432]#select version();
> -[ RECORD 1 ]--------------------------------------------------------
> version | PostgreSQL 7.3.1 on i586-pc-linux-gnu, compiled by GCC 2.96
>
> $ psql -l -p 5432
> List of databases
> Name | Owner
> -----------+-------
> slave | pgsql
> template0 | pgsql
> template1 | pgsql
> (3 rows)
>
> PG_HBA.CONF:
> # TYPE DATABASE USER IP-ADDRESS IP-MASK METHOD
>
> local all all trust
> host all all 127.0.0.1 255.255.255.255 trust
> host all all 164.35.10.17 255.255.0.0 trust
>
> Versão:
> $ perl -e 'use DBI; print "$DBI::VERSION\n"' --> 1.32
> $ perl -e 'use DBD::Pg; print "$DBD::Pg::VERSION\n";' --> 1.21
>
>
> Função Perl:
> #!/usr/bin/perl
>
> use DBI;
> use DBD::Pg;
> use strict;
>
> my $dbh;
>
> my $dbname='slave';
> my $host='164.35.10.17';
> my $port='5432';
> my $options='';
> my $username='pgsql';
> my $password='pgsql';
>
> $dbh = DBI->connect("dbi:Pg:dbname=$dbname;host=$host;port=$port",
> "$username",
> "$password",
> { RaiseError => 1, AutoCommit => 0 }) || die $DBI::errstr;
>
> print "Connected!!!\n";
>
> my $hSt = $dbh->prepare('SELECT * FROM table;');
>
> $hSt->execute;
>
> while(my @raD = $hSt->fetchrow_array()) { print "$raD[0]\t$raD[1]\n";}
>
> $hSt->finish;
>
> $dbh->disconnect;
>
> print "Disconnected!!!\n";
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
Tom lane,
I've restarted Pg before, that's why still received the error message... Now that I've stopped and started it again
thefollowing message appears...
DBI connect('dbname=slave;host=164.35.10.17;port=5432','username=pgsql',...) failed: could not connect to server:
Connectionrefused at pg_connect.pl line 16
Is there any compatible problem with Pg and DBD::Pg? What am I doing wrong? =o)
Thanks...
On Thu, 23 Jan 2003 12:58:51 -0500
Tom Lane <tgl@sss.pgh.pa.us> wrote:
> =?ISO-8859-1?Q?Ren=EA_Salom=E3o?= <rene@ibiz.com.br> writes:
> > I was trying to connect to Pg 7.3.1 using perl and DBD::Pg 1.21, however the following error occurs:
> > $ perl pg_connect.pl
> > FATAL: No pg_hba.conf entry for host 164.35.10.17, user pgsql, database slave
>
> The error message is pretty specific. Are you sure you edited the right
> pg_hba.conf? Did you SIGHUP the postmaster after you edited it?
>
> regards, tom lane
>
Thanks.
-----------------------------
Renê Salomão
Ibiz Tecnologia -- www.ibiz.com.br
$ psql -l -p 5432
List of databases
Name | Owner
-----------+-------
slave | pgsql
template0 | pgsql
template1 | pgsql
(3 rows)
Versão:
[pgsql@slave:5432]#select version();
-[ RECORD 1 ]--------------------------------------------------------
version | PostgreSQL 7.3.1 on i586-pc-linux-gnu, compiled by GCC 2.96
PG_HBA.CONF:
# TYPE DATABASE USER IP-ADDRESS IP-MASK METHOD
local all all trust
host all all 127.0.0.1 255.255.255.255 trust
host all all 164.35.10.17 255.255.255.0 trust
Versão:
$ perl -e 'use DBI; print "$DBI::VERSION\n"' --> 1.32
$ perl -e 'use DBD::Pg; print "$DBD::Pg::VERSION\n";' --> 1.21
Função Perl:
#!/usr/bin/perl
use DBI;
use DBD::Pg;
use strict;
my $dbh;
my $dbname='slave';
my $host='164.35.10.17';
my $port='5432';
my $options='';
my $username='pgsql';
my $password='pgsql';
$dbh = DBI->connect("dbi:Pg:dbname=$dbname;host=$host;port=$port",
"$username",
"$password",
{ RaiseError => 1, AutoCommit => 0 }) || die $DBI::errstr;
print "Connected!!!\n";
my $hSt = $dbh->prepare('SELECT * FROM table;');
$hSt->execute;
while(my @raD = $hSt->fetchrow_array()) { print "$raD[0]\t$raD[1]\n";}
$hSt->finish;
$dbh->disconnect;
print "Disconnected!!!\n";
=?ISO-8859-1?Q?Ren=EA_Salom=E3o?= <rene@ibiz.com.br> writes:
> DBI connect('dbname=slave;host=164.35.10.17;port=5432','username=pgsql',...) failed: could not connect to server:
Connectionrefused at pg_connect.pl line 16
"Connection refused" is not a postmaster message, it's a kernel-level
message. The implication is that there's nothing at all listening on
the port you tried to connect to. My guess is that the postmaster
isn't really running, or that you forgot to give it the -i option to
listen on TCP.
regards, tom lane
Thanks to all that helped me...
Please disregard my email asking for help... I found out the problem... Reading the reply from Tom, Tariq and Shane, I
realizedthat the problem was "the equipament" between the seat and the computer!!! =o)
I was the one to blame... =o)
Sorry bothering you all...
Living and learning!!! Hope less simple mistakes like that won't occur to me again...
Thanks all...
What happened was that I was using the pg_hda.conf from another Pg installed in my computer... Then when I realized
thatand started to used the right one... It work just fine!!!
On Fri, 24 Jan 2003 00:43:39 -0500
Tom Lane <tgl@sss.pgh.pa.us> wrote:
> =?ISO-8859-1?Q?Ren=EA_Salom=E3o?= <rene@ibiz.com.br> writes:
> > DBI connect('dbname=slave;host=164.35.10.17;port=5432','username=pgsql',...) failed: could not connect to server:
Connectionrefused at pg_connect.pl line 16
>
> "Connection refused" is not a postmaster message, it's a kernel-level
> message. The implication is that there's nothing at all listening on
> the port you tried to connect to. My guess is that the postmaster
> isn't really running, or that you forgot to give it the -i option to
> listen on TCP.
>
> regards, tom lane
>
-----------------------------
Renê Salomão
Ibiz Tecnologia -- www.ibiz.com.br
On Fri, 24 Jan 2003, Renê Salomão wrote: > Thanks to all that helped me... > > Please disregard my email asking for help... I found out the > problem... Reading the reply from Tom, Tariq and Shane, I realized > that the problem was "the equipament" between the seat and the > computer!!! =o) We call these PEBKACs. Problem Exists Between Keyboard And Chair. :-) Much better than an eye-dee-ten-tee (id10t) error. Rod -- "Open Source Software - Sometimes you get more than you paid for..."
That's a different error than the first pg_hba.conf...
Sounds like a pgoptions.conf dilemmna -- It TCP/IP enabled ? Did you start
postgres with TCP/IP enabled ?
"Ren� Salom�o" <rene@ibiz.com.br> wrote in message
news:20030123165709.356e2f0b.rene@ibiz.com.br...
> Tom lane,
> =20
> I've restarted Pg before, that's why still received the error message...
N=
> ow that I've stopped and started it again the following message
appears...=
> =20
>
> DBI
connect('dbname=3Dslave;host=3D164.35.10.17;port=3D5432','username=3Dpg=
> sql',...) failed: could not connect to server: Connection refused at
pg_con=
> nect.pl line 16
>
> Is there any compatible problem with Pg and DBD::Pg? What am I doing
wrong=
> ? =3Do)
>
> Thanks...
>
> On Thu, 23 Jan 2003 12:58:51 -0500
> Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> > =3D?ISO-8859-1?Q?Ren=3DEA_Salom=3DE3o?=3D <rene@ibiz.com.br> writes:
> > > I was trying to connect to Pg 7.3.1 using perl and DBD::Pg 1.21,
howev=
> er the following error occurs:=20
> > > $ perl pg_connect.pl=20
> > > FATAL: No pg_hba.conf entry for host 164.35.10.17, user pgsql,
databas=
> e slave
> >=20
> > The error message is pretty specific. Are you sure you edited the right
> > pg_hba.conf? Did you SIGHUP the postmaster after you edited it?
> >=20
> > regards, tom lane
> >=20
>
> Thanks.
> -----------------------------=20
> Ren=EA Salom=E3o
> Ibiz Tecnologia -- www.ibiz.com.br
>
>
> $ psql -l -p 5432=20=20=20=20=20=20
> List of databases
> Name | Owner=20
> -----------+-------
> slave | pgsql
> template0 | pgsql
> template1 | pgsql
> (3 rows)
>
> Vers=E3o:
> [pgsql@slave:5432]#select version();
> -[ RECORD 1 ]--------------------------------------------------------
> version | PostgreSQL 7.3.1 on i586-pc-linux-gnu, compiled by GCC 2.96
>
> PG_HBA.CONF:
> # TYPE DATABASE USER IP-ADDRESS IP-MASK METHOD
>
> local all all trust
> host all all 127.0.0.1 255.255.255.255 trust
> host all all 164.35.10.17 255.255.255.0 trust
>
> Vers=E3o:
> $ perl -e 'use DBI; print "$DBI::VERSION\n"' --> 1.32
> $ perl -e 'use DBD::Pg; print "$DBD::Pg::VERSION\n";' --> 1.21
>
>
> Fun=E7=E3o Perl:
> #!/usr/bin/perl
>
> use DBI;
> use DBD::Pg;
> use strict;
>
> my $dbh;
>
> my $dbname=3D'slave';
> my $host=3D'164.35.10.17';
> my $port=3D'5432';
> my $options=3D'';
> my $username=3D'pgsql';
> my $password=3D'pgsql';
>
> $dbh =3D DBI->connect("dbi:Pg:dbname=3D$dbname;host=3D$host;port=3D$port",
> "$username",
> "$password",
> { RaiseError =3D> 1, AutoCommit =3D> 0 }) || die $DBI::errstr;
>
> print "Connected!!!\n";
>
> my $hSt =3D $dbh->prepare('SELECT * FROM table;');
>
> $hSt->execute;
>
> while(my @raD =3D $hSt->fetchrow_array()) { print "$raD[0]\t$raD[1]\n";}
>
> $hSt->finish;
>
> $dbh->disconnect;
>
> print "Disconnected!!!\n";
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly