[SQL] Foreign key problem

[Andreas Tille]

Hello,

I tried to track down the database definitions from a more complex
database which I have to convert from MS SQL to PostgreSQL.  I have
only one last syntactical error.  Here is the striped down code
to the shortest snipped which shows the problem:

CREATE TABLE ResKulturDetail
(
IdLabNr   int,
IdIndex   smallint
);

CREATE TABLE ResKulturDetailDay
(
IdLabNr int,
IdIndex smallint
);

CREATE INDEX IX_IdLabNr_KulturDetail ON ResKulturDetail(IdLabNr) ;
CLUSTER IX_IdLabNr_KulturDetail ON ResKulturDetail ;

ALTER TABLE ResKulturDetailDay ADD CONSTRAINT FK_ResKulturDetailDay
  FOREIGN KEY (IdLabNr,IdIndex)
  REFERENCES ResKulturDetail (IdLabNr,IdIndex) ;


Here is the psql log, if I try to insert the code above:

reskultur=# CREATE TABLE ResKulturDetail
reskultur-# (
reskultur(# IdLabNr   int,
reskultur(# IdIndex   smallint
reskultur(# );
CREATE
reskultur=#
reskultur=# CREATE TABLE ResKulturDetailDay
reskultur-# (
reskultur(# IdLabNr int,
reskultur(# IdIndex smallint
reskultur(# );
CREATE
reskultur=#
reskultur=# CREATE INDEX IX_IdLabNr_KulturDetail ON ResKulturDetail(IdLabNr) ;
CREATE
reskultur=# CLUSTER IX_IdLabNr_KulturDetail ON ResKulturDetail ;
CLUSTER
reskultur=#
reskultur=# ALTER TABLE ResKulturDetailDay ADD CONSTRAINT FK_ResKulturDetailDay
reskultur-#   FOREIGN KEY (IdLabNr,IdIndex)
reskultur-#   REFERENCES ResKulturDetail (IdLabNr,IdIndex) ;
NOTICE:  ALTER TABLE ... ADD CONSTRAINT will create implicit trigger(s) for FOREIGN 
KEY check(s)
ERROR:  UNIQUE constraint matching given keys for referenced table "reskulturdetail" 
not found


Can anybody explain, why the foreign key constraint fails?

Thanks and have a nice weekend

   Andreas.



---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] Foreign key problem

[Itai Zukerman]

Je Mon, 25 Jun 2001 09:34:01 +0200 (CEST),
Andreas Tille <[EMAIL PROTECTED]> scribis:

> CREATE TABLE ResKulturDetail
> (
> IdLabNr   int,
> IdIndex   smallint
> );
> 
> CREATE TABLE ResKulturDetailDay
> (
> IdLabNr int,
> IdIndex smallint
> );
> 
> CREATE INDEX IX_IdLabNr_KulturDetail ON ResKulturDetail(IdLabNr) ;
> 
> ALTER TABLE ResKulturDetailDay ADD CONSTRAINT FK_ResKulturDetailDay
>   FOREIGN KEY (IdLabNr,IdIndex)
>   REFERENCES ResKulturDetail (IdLabNr,IdIndex) ;

> Can anybody explain, why the foreign key constraint fails?

According to the documentation for CREATE TABLE:

  In addition, the referenced columns are supposed to be the columns
  of a UNIQUE constraint in the referenced table, however Postgres
  does not enforce this.

Well, it looks like PostgreSQL *does* enforce it.  Try it with:

  CREATE UNIQUE INDEX IX_IdLabNr_KulturDetail
  ON ResKulturDetail(IdLabNr, IdIndex) ;

I'm not sure why this restriction is necessary...

-- 
Itai Zukerman  

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] Foreign key problem

[Stephan Szabo]

On 25 Jun 2001, Itai Zukerman wrote:

> According to the documentation for CREATE TABLE:
> 
>   In addition, the referenced columns are supposed to be the columns
>   of a UNIQUE constraint in the referenced table, however Postgres
>   does not enforce this.
> 
> Well, it looks like PostgreSQL *does* enforce it.  Try it with:

Hmm, that paragraph should have been ripped out at the same time I added
the check.  Will see if I need to patch the docs (or if it was already
done by someone).

>   CREATE UNIQUE INDEX IX_IdLabNr_KulturDetail
>   ON ResKulturDetail(IdLabNr, IdIndex) ;
> 
> I'm not sure why this restriction is necessary...

Mostly because the semantics of the constraint as defined by the spec
don't make sense in certain cases against non-unique pk table rows.  For
example, a delete cascade would delete the *first* time a matched row was
deleted even if there were other rows that could be matched except in
MATCH PARTIAL (which we don't support).  Once we support MATCH PARTIAL,
we might make the case to allow MATCH PARTIAL references to non-unique
columns as an extension, but we'd need to think through the other effects
of that.



---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

[SQL] control structure in a transaction block?

[Wei Weng]

I am using postgresql 7.1.2.

I am trying to implement the following pseudo code:

BEGIN
aid = select id from table_a where name = 'test';
if(aid != NULL) 
then
{
update set name = 'test_test' where id = aid;
}
else
{
insert into table_a values (nextval('table_a_id_seq'), 'test');
}
COMMIT

But as we know, you can't use control structure in a transaction block.

And I can't use a function either, because in the real application,
there are way too many parameters needed to be passed in order to do the
"insert" or "update".

Is there any tricks I can play here? 

Thanks. 

-- 
Wei Weng
Network Software Engineer
KenCast Inc.



---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] Problems using a rule with the WHERE clause

[Tom Lane]

Luis Sousa <[EMAIL PROTECTED]> writes:
> CREATE RULE "updateturnodocente" AS ON UPDATE TO "docentesturno"
>  WHERE OLD.idpessoal != 0
>  DO INSTEAD (
>  ...

> When I execute the INSERT into de view docentesturno I got the message:
> ERROR:  Cannot update a view without an appropriate rule

You failed to supply a rule covering the case OLD.idpessoal = 0.

More specifically, you *must* supply an unconditional INSTEAD rule to
replace the attempt to insert/update in the view.  Possibly what you
want is

CREATE RULE "updateturnodocente" AS ON UPDATE TO "docentesturno"
WHERE OLD.idpessoal != 0
DO ( ... );

CREATE RULE "updateturnodocente_default" AS ON UPDATE TO "docentesturno"
DO INSTEAD NOTHING;

Here, the unconditional rule always fires, and the conditional one fires
only when its condition is true.

regards, tom lane

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

[SQL] select with multiple occurences in same table

[William Herring]

 
Table "inventorytrack"

 Attribute  |   Type   
| Modifier

+---+--

 tattoo | text  |

 past_group | text  |

 ranch  | text  |

 ent_date   | timestamp |

 indate | date  |

 

 

In the above table, I have multiple occurrences of ‘tattoo’.  However, some of the tattoo’s have null
value in ‘indate’, some do not.  I
would like to select those tattoos with a null indate, unless there is another
identical tattoo in the table that has 
a date value in  ‘indate’.  For example, I might have:

 

tattoo |  
past_group   |
ranch  |    ent_date   
|   indate

++++

 1039   | Barn2/Behind   | Iberia | 2000-03-16 11:03:32-08
|

 1039   | Across/Bubs    | Iberia | 2000-12-28 14:57:13-08
| 2000-12-28

 1832   | GardnerBlock#3 | Iberia |
2000-03-16 11:03:32-08 |

1832 | GardnerBlock#2 | Iberia |
2000-12-28 12:46:57-08 | 2000-12-28 

1833 | Becker1   
| Iberia | 2000-03-16 11:03:32-08 |

 

 

 

 

So the prospective SELECT would give back:

 

tattoo |  
past_group   |
ranch  |    ent_date   
|   indate

1039   |
Across/Bubs    | Iberia |
2000-12-28 14:57:13-08 | 2000-12-28

1832| GardnerBlock#2 | Iberia | 2000-12-28 12:46:57-08 | 2000-12-28

1833| Becker1   
| Iberia | 2000-03-16 11:03:32-08 |

 

Any suggestions on how to do this?

[SQL] pl/pgSQL and escaping LIKE clauses

[Robby Slaughter]

I'm having trouble getting LIKE clauses to work correctly inside a plpgSQL 
function. 

Here's my table:

 id |  val
+-
  1 | hello
  2 | there
  3 | everyone


Here's my function:

CREATE FUNCTION intable(char)
RETURNS INTEGER
AS
'
DECLARE 
  input ALIAS FOR $1;
  temp  INTEGER;
BEGIN
  SELECT INTO temp id FROM test WHERE val LIKE ''input%'';
  RAISE NOTICE ''Value of temp is %'',temp;
  RETURN temp;
END;
'
LANGUAGE 'plpgsql';

I should be able to SELECT('hello') and get back 1, correct?

No matter what I put in as a parameter, it always returns null.

If I change the LIKE clause to read "...LIKE ''hello%''" it does
in fact work. Or if I scrap the LIKE clause and have it 
read something such as " id = input" (if input is an integer)
it also works fine.

Any thoughts?

Thanks,
Robby

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

[SQL] loosing connection after function call

[Markus Wagner]

Hi,

I would like to put one table create command into a C function
"my_init", just to provide a simple interface for all tables where these
tables are needed. When I execute "select my_init();" I loose the
database connection (pgaccess). Using psql -f I get the errors below. I
reduced the complexity of my table and I found out: The error occurrs,
as soon as I have a "SERIAL" type within my statement. A simple "CREATE
TABLE atl (idx INT);" runs fine, but "CREATE TABLE (idx SERIAL);"
crashes.

Here is my C function:

bool my_init ()
{
 if (SPI_connect () < 0)
  return (0);

 SPI_exec ("CREATE TABLE atl (idx SERIAL);",0);
 SPI_finish ();

 return (1);
}

Here is my SQL code that I execute using psql -f:

...

...
SELECT my_init();
...


Here is the error:

psql:sql/test.sql:19: NOTICE:  CREATE TABLE will create implicit
sequence 'atl_idx_seq' for SERIAL column 'atl.idx'
psql:sql/test.sql:19: NOTICE:  CREATE TABLE/UNIQUE will create implicit
index 'atl_idx_key' for table 'atl'
psql:sql/test.sql:19: NOTICE:  mdopen: couldn't open HÛH$5@: No
such file or directory
psql:sql/test.sql:19: ERROR:  RelationClearRelation: relation 1034879
deleted while still in use
ERROR:  cannot open relation HÛH$5@
FATAL 2:  elog: error during error recovery, giving up!
pqReadData() -- backend closed the channel unexpectedly.
This probably means the backend terminated abnormally
before or while processing the request.
psql:sql/test.sql:19: connection to server was lost
make: *** [dbs] Error 2

Please help!
Thank you,

Markus Wagner

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html