foreign key on same table
The Legend
can anybody tell me why this code is not right ?
I try to create a foreign key on a column thats located in the same table
both coulmn have a index prod_code,prod_color_code
i try to accomplish prod_code X with prod_color_code 1
prod_code X with prod_color_code 2
prod_code X with prod_color_code 3
and the combination has to be unique
ALTER TABLE product_code
ADD CONSTRAINT 1_color FOREIGN KEY (prod_color_code)
REFERENCES product_code (prod_code)
thanks Ed
Roland Beck
you have a product table
and a color table
both have a primary key prod_code and color_code
then you create your table where you store the combinations:
table prod_color
where you add a foreign key constraint to product and another fk constraint to color
and for uniqueness you can make the two keys combined a primary key or a unique key.
If you just want to stick with the one table you have, do not add a foreign key, but a unique key constraint on both prod_code and prod_color_code together.
hope this helps
Roland
Michael Austin
it makes no sense to have a FK in the same table... that is why they are
called "FOREIGN".
create a unique index product_code (prod_code,prod_color_code).
mysql> create table a (z int auto_increment, a char(2), b INT, primary
key (z));
mysql> insert into a (a,b) values ('a',1),('a',2);
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into a (a,b) values ('a',1),('a',2);
ERROR 1062 (23000): Duplicate entry 'a-1' for key 'a_idx'
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from a;
+---+------+------+
| z | a | b |
+---+------+------+
| 1 | a | 1 |
| 2 | a | 2 |
+---+------+------+
2 rows in set (0.00 sec)
ThanksButNo
>
> it makes no sense to have a FK in the same table... that is why they are
> called "FOREIGN".
>
You can't have a recursive relationship?
Rik Wasmus
> can anybody tell me why this code is not right ?
>
> I try to create a foreign key on a column thats located in the same table
> both coulmn have a index prod_code,prod_color_code
>
> i try to accomplish prod_code X with prod_color_code 1
> prod_code X with prod_color_code 2
> prod_code X with prod_color_code 3
> and the combination has to be unique
Unless prod_color_code can be NULL, and thereby circumvents the 'unique'
requirement, how would you expect ever to get data into the table?
> ALTER TABLE product_code
> ADD CONSTRAINT 1_color FOREIGN KEY (prod_color_code)
> REFERENCES product_code (prod_code)
mysql> CREATE TABLE product_code_test
-> (
-> prod_code INTEGER AUTO_INCREMENT PRIMARY KEY,
-> prod_color_code INTEGER NOT NULL,
-> UNIQUE(prod_code,prod_color_code)) Engine=InnoDB;
Query OK, 0 rows affected (0.14 sec)
mysql> ALTER TABLE product_code_test ADD CONSTRAINT foobar FOREIGN KEY
(prod_color_code) REFERENCES product_code_test (prod_code);
Query OK, 0 rows affected (0.11 sec)
Records: 0 Duplicates: 0 Warnings: 0
..now, the only way to insert a 'new' prod_color_code N into the table is
by doing a:
INSERT INTO product_code VALUES (N,N);
.. which just about makes it.
If this doesn't work for you, please inform us about the error.
--
Rik Wasmus
...spamrun finished
Rik Wasmus
<mau...@firstdbasource.com> wrote:
> The Legend wrote:
>> hello,
>> can anybody tell me why this code is not right ?
>> I try to create a foreign key on a column thats located in the same
>> table both coulmn have a index prod_code,prod_color_code
>> i try to accomplish prod_code X with prod_color_code 1
>> prod_code X with prod_color_code 2
>> prod_code X with prod_color_code 3
>> and the combination has to be unique
>> ALTER TABLE product_code
>> ADD CONSTRAINT 1_color FOREIGN KEY (prod_color_code)
>> REFERENCES product_code (prod_code)
>> thanks Ed
>
> it makes no sense to have a FK in the same table... that is why they are
> called "FOREIGN".
Hello? Adjacency model for hierarchies anyone? Makes perfect sense to me
in that scenario.
The Legend
"Rik Wasmus" <luiheid...@hotmail.com> schreef in bericht
news:op.uepgl...@metallium.lan...
On Sun, 20 Jul 2008 22:01:19 +0200, The Legend <ina...@invalid.com> wrote:
> can anybody tell me why this code is not right ?
>
> I try to create a foreign key on a column thats located in the same table
> both coulmn have a index prod_code,prod_color_code
>
> i try to accomplish prod_code X with prod_color_code 1
> prod_code X with prod_color_code 2
> prod_code X with prod_color_code 3
> and the combination has to be unique
>Unless prod_color_code can be NULL, and thereby circumvents the 'unique'
>requirement, how would you expect ever to get data into the table?
> ALTER TABLE product_code
> ADD CONSTRAINT 1_color FOREIGN KEY (prod_color_code)
> REFERENCES product_code (prod_code)
>mysql> CREATE TABLE product_code_test
> -> (
> -> prod_code INTEGER AUTO_INCREMENT PRIMARY KEY,
> -> prod_color_code INTEGER NOT NULL,
> -> UNIQUE(prod_code,prod_color_code)) Engine=InnoDB;
>Query OK, 0 rows affected (0.14 sec)
>mysql> ALTER TABLE product_code_test ADD CONSTRAINT foobar FOREIGN KEY
>(prod_color_code) REFERENCES product_code_test (prod_code);
>Query OK, 0 rows affected (0.11 sec)
>Records: 0 Duplicates: 0 Warnings: 0
.>.now, the only way to insert a 'new' prod_color_code N into the table is
>by doing a:
>INSERT INTO product_code VALUES (N,N);
... which just about makes it.
If this doesn't work for you, please inform us about the error.
--
Rik Wasmus
...spamrun finished
To respond to the critics i was just trying out some code.
All code i was trying is replied (the alter table) but i get this error ....
but i am using the ALTER TABLE command ???: zaina is the instance name en i
am logged in as root with all. To solve my issue on the short terms i used a
unique key on ( prod_code,prod_color_code) and did not see errors on that
one.
#ERROR 1005 (HY000)- Can't create table '.\zaina\#sql-ad0_18.frm' (errno:
150)
Ed
Rik Wasmus
> To respond to the critics i was just trying out some code.
> All code i was trying is replied (the alter table) but i get this error
> ....
> but i am using the ALTER TABLE command ???: zaina is the instance name
> en i
> am logged in as root with all. To solve my issue on the short terms i
> used a
> unique key on ( prod_code,prod_color_code) and did not see errors on that
> one.
> #ERROR 1005 (HY000)- Can't create table '.\zaina\#sql-ad0_18.frm' (errno:
> 150)
Do a SHOW CREATE TABLE product_code and give us the ouput. Keep in mind
that both fields MUST have EXACTLY the same definition (save for NULL /
NOT NULL), so you can't link an INT(10) to an INT(11) column for instance,
or a latin1 with an utf8, and it only works for InnoDB tables. Foreign Key
errors are often cryptic, something that really should be fixed...
Michael Austin
In my limited knowledge of AMfH (and yes I did consider it), it was my
judgement that this particular usage would not be very useful and could
be better served using just a simple unique index. No use in forcing
the db to do a lookup that I felt was not necessary. Certain data lends
itself very well to AMfH - IMPO just not this data. Over designing can
have it's own bag of worms and just as bad if not worse than no design
at all.
And at this point will leave that judgment call to the OP.
My favorite saying is "No amount of hardware or physical tuning efforts
will ever overcome an poor/bad logical design." - Yes, they can be
mitigated, but at some point you will have to go back to the drawing
board and do it right...
Rik Wasmus
<mau...@firstdbasource.com> wrote:
Big words. You draw many conclusions (/'judgements') without arguments
though:
- Why would the adjacency model not be served with a foreign key
constraint?
- Give me 1 query to reliably delete a whole subtree.
- Give me a simple solution to avoid race conditions when inserting (or
moving) a node to a a parent which might not be there (yes, transactions
could do it, with a lot of extra work though).
- Why does it not make sense in any circumstance to have a foreign key in
the same table?
- Where is the exact over- or bad design in these scenario's?
--
Rik Wasmus
Peter H. Coffin
And lists. I do lots of lists.
(Or would this be a hyperdeformed-chibi hierarchy? Do we have a
topologist in the house?)
--
5. The artifact which is the source of my power will not be kept on the
Mountain of Despair beyond the River of Fire guarded by the Dragons of
Eternity. It will be in my safe-deposit box. The same applies to the
object which is my one weakness. --Peter Anspach "Evil Overlord"