pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedproblem with uniques and foreing keys
5+ messages / 4 participants
[nested] [flat]
* problem with uniques and foreing keys
@ 2006-05-06 17:27 kernel.alert kernel.alert <kernel.alert@gmail.com>
0 siblings, 4 replies; 5+ messages in thread
From: kernel.alert kernel.alert @ 2006-05-06 17:27 UTC (permalink / raw)
To: pgsql-sql
Hi list...
Please i have a problem with this...
I create the follow tables...
--------------------------------------------------------
CREATE TABLE empresa (
id_empresa integer NOT NULL primary key,
nombre varchar(45),
);
CREATE TABLE casino (
id_casino integer NOT NULL,
id_empresa integer REFERENCES empresa(id_empresa),
nombre varchar(45),
primary key(id_casino,id_empresa)
);
CREATE TABLE maq_casino (
id_empresa integer NOT NULL REFERENCES casino(id_empresa),
id_casino integer NOT NULL REFERENCES casino(id_casino),
ubicacion_sala varchar(45) default NULL,
primary key(id_empresa,id_casino,id_tipo_maquina,id_maq_casino)
);
--------------------------------------------------------
When i'm gonna to create the last table i got this error:
ERROR: no hay restriccion unique que coincida con las columnas dadas en la
tabla referida <<casino>>
That in english is like .. there is no a unique constraint with columns
referred in casino table.
Please where is the problem...
Greetings ...
Krackem
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: problem with uniques and foreing keys
@ 2006-05-06 17:46 Terry Lee Tucker <terry@esc1.com>
parent: kernel.alert kernel.alert <kernel.alert@gmail.com>
3 siblings, 0 replies; 5+ messages in thread
From: Terry Lee Tucker @ 2006-05-06 17:46 UTC (permalink / raw)
To: pgsql-sql
On Saturday 06 May 2006 01:27 pm, "kernel.alert kernel.alert"
<kernel.alert@gmail.com> thus communicated:
--> Hi list...
-->
--> Please i have a problem with this...
-->
--> I create the follow tables...
-->
--> --------------------------------------------------------
-->
--> CREATE TABLE empresa (
--> id_empresa integer NOT NULL primary key,
--> nombre varchar(45),
--> );
--> CREATE TABLE casino (
--> id_casino integer NOT NULL,
--> id_empresa integer REFERENCES empresa(id_empresa),
-->
--> nombre varchar(45),
-->
--> primary key(id_casino,id_empresa)
--> );
--> CREATE TABLE maq_casino (
--> id_empresa integer NOT NULL REFERENCES casino(id_empresa),
--> id_casino integer NOT NULL REFERENCES casino(id_casino),
-->
--> ubicacion_sala varchar(45) default NULL,
--> primary key(id_empresa,id_casino,id_tipo_maquina,id_maq_casino)
--> );
-->
--> --------------------------------------------------------
-->
--> When i'm gonna to create the last table i got this error:
-->
--> ERROR: no hay restriccion unique que coincida con las columnas dadas en
la --> tabla referida <<casino>>
-->
--> That in english is like .. there is no a unique constraint with columns
--> referred in casino table.
-->
-->
--> Please where is the problem...
-->
--> Greetings ...
-->
-->
-->
--
The columns referenced in the maq_casino table must have UNIQUE constraints
on them in their table definition as in:
id_casino integer UNIQUE NOT NULL,
^^^^^^^
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: problem with uniques and foreing keys
@ 2006-05-06 17:56 Bruno Wolff III <bruno@wolff.to>
parent: kernel.alert kernel.alert <kernel.alert@gmail.com>
3 siblings, 0 replies; 5+ messages in thread
From: Bruno Wolff III @ 2006-05-06 17:56 UTC (permalink / raw)
To: kernel.alert kernel.alert <kernel.alert@gmail.com>; +Cc: pgsql-sql
On Sat, May 06, 2006 at 12:27:41 -0500,
"kernel.alert kernel.alert" <kernel.alert@gmail.com> wrote:
> Hi list...
>
> Please i have a problem with this...
>
> I create the follow tables...
>
> --------------------------------------------------------
>
> CREATE TABLE empresa (
> id_empresa integer NOT NULL primary key,
> nombre varchar(45),
> );
> CREATE TABLE casino (
> id_casino integer NOT NULL,
> id_empresa integer REFERENCES empresa(id_empresa),
>
> nombre varchar(45),
>
> primary key(id_casino,id_empresa)
> );
> CREATE TABLE maq_casino (
> id_empresa integer NOT NULL REFERENCES casino(id_empresa),
> id_casino integer NOT NULL REFERENCES casino(id_casino),
>
> ubicacion_sala varchar(45) default NULL,
> primary key(id_empresa,id_casino,id_tipo_maquina,id_maq_casino)
> );
>
> --------------------------------------------------------
>
> When i'm gonna to create the last table i got this error:
>
> ERROR: no hay restriccion unique que coincida con las columnas dadas en la
> tabla referida <<casino>>
>
> That in english is like .. there is no a unique constraint with columns
> referred in casino table.
>
>
> Please where is the problem...
Unless there is a performance problem, id_empresa should not appear in the
maq_casino table at all, since it is derivable from id_casino.
Is id_empresa really allowed to be NULL in casino?
If there is an actual performance problem and you really need to have it in
the mag_casino table, then you want to add another unique key (id_casino,
id_empresa) in casino and then change casino_mag to have a foreign key
reference on (id_casino, id_impresa) to casino instead of the individual
references you have now.
If id_empresa in casino_mag is not supposed to be tied to the value of
id_casino (which I doubt is the case), then it should be referencing
empresa instead of casino.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: problem with uniques and foreing keys
@ 2006-05-06 18:43 kernel.alert kernel.alert <kernel.alert@gmail.com>
parent: kernel.alert kernel.alert <kernel.alert@gmail.com>
3 siblings, 0 replies; 5+ messages in thread
From: kernel.alert kernel.alert @ 2006-05-06 18:43 UTC (permalink / raw)
To: pgsql-sql
Sorry i had a mistake in the post it was with the definition of the primary
key in the empresa table...
I'm having the same error when i build the last table...
thanks for your answers...
Hi list...
>
> Please i have a problem with this...
>
> I create the follow tables...
>
> --------------------------------------------------------
>
> CREATE TABLE empresa (
> id_empresa integer NOT NULL primary key,
> nombre varchar(45),
>
primary key(id_empresa)
);
> CREATE TABLE casino (
> id_casino integer NOT NULL,
> id_empresa integer REFERENCES empresa(id_empresa),
>
> nombre varchar(45),
>
> primary key(id_casino,id_empresa)
> );
> CREATE TABLE maq_casino (
> id_empresa integer NOT NULL REFERENCES casino(id_empresa),
> id_casino integer NOT NULL REFERENCES casino(id_casino),
>
> ubicacion_sala varchar(45) default NULL,
> primary key(id_empresa,id_casino,id_tipo_maquina,id_maq_casino)
> );
>
> --------------------------------------------------------
>
> When i'm gonna to create the last table i got this error:
>
ERROR: no hay restriccion unique que coincida con las columnas dadas en la
> tabla referida <<casino>>
>
> That in english is like .. there is no a unique constraint with columns
> referred in casino table.
>
>
> Please where is the problem...
>
> Greetings ...
>
> Krackem
>
>
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: problem with uniques and foreing keys
@ 2006-05-06 18:57 Stephan Szabo <sszabo@megazone.bigpanda.com>
parent: kernel.alert kernel.alert <kernel.alert@gmail.com>
3 siblings, 0 replies; 5+ messages in thread
From: Stephan Szabo @ 2006-05-06 18:57 UTC (permalink / raw)
To: kernel.alert kernel.alert <kernel.alert@gmail.com>; +Cc: pgsql-sql
On Sat, 6 May 2006, kernel.alert kernel.alert wrote:
> I create the follow tables...
>
> --------------------------------------------------------
>
> CREATE TABLE empresa (
> id_empresa integer NOT NULL primary key,
> nombre varchar(45),
> );
> CREATE TABLE casino (
> id_casino integer NOT NULL,
> id_empresa integer REFERENCES empresa(id_empresa),
>
> nombre varchar(45),
>
> primary key(id_casino,id_empresa)
> );
> CREATE TABLE maq_casino (
> id_empresa integer NOT NULL REFERENCES casino(id_empresa),
> id_casino integer NOT NULL REFERENCES casino(id_casino),
You probably want a table level constraint like:
foreign key (id_casino, id_empresa) references casino(id_casino,
id_empresa)
That's not the same as two single-column constraints which is what you
have above.
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2006-05-06 18:57 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-05-06 17:27 problem with uniques and foreing keys kernel.alert kernel.alert <kernel.alert@gmail.com>
2006-05-06 17:46 ` Terry Lee Tucker <terry@esc1.com>
2006-05-06 17:56 ` Bruno Wolff III <bruno@wolff.to>
2006-05-06 18:43 ` kernel.alert kernel.alert <kernel.alert@gmail.com>
2006-05-06 18:57 ` Stephan Szabo <sszabo@megazone.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