Leech (?) content of mySQL tables

[knoak]

Hi there and a wonderful good morning!

I was wondering something about mySQL, but i don't have a clue
if it's possible (but it would be great!).

Imagine i have two tables. One is called species, and one
is called animals.
One of the species is 'birds' (others are fishes etc.)
In my table animals one of the birds is a hawk, another an eagle.
Anyway, can i get a field in 'animals', that automatically has the same
content as a defined field in 'species'. So if i would change 'birds'
to 'flying animals' in the 'species' table, it would also change
in the 'animals' table.

I'm affraid it sounds kind of vague, but i think you get what i want...

Greetings

[Pedro Graca]

Re: Leech (?) content of mySQL tables

knoak wrote:[color=blue]
> Imagine i have two tables. One is called species, and one
> is called animals.
> One of the species is 'birds' (others are fishes etc.)
> In my table animals one of the birds is a hawk, another an eagle.
> Anyway, can i get a field in 'animals', that automatically has the same
> content as a defined field in 'species'. So if i would change 'birds'
> to 'flying animals' in the 'species' table, it would also change
> in the 'animals' table.[/color]


SELECT * FROM species;
| id | description |
+----+-------------+
| 1 | birds |
| 2 | fishes |
+----+-------------+

SELECT * FROM animals;
| id | species | name |
+----+---------+-------+
| 1 | 1 | eagle |
| 2 | 1 | hawk |
+----+---------+-------+

SELECT s.description, a.name FROM species s, animals a WHERE a.species=s.id;
| description | name |
+-------------+-------+
| birds | eagle |
| birds | hawk |
+-------------+-------+

UPDATE species SET description='fl ying animals' WHERE id=1;

-- the exact same query as above!
SELECT s.description, a.name FROM species s, animals a WHERE a.species=s.id;
| description | name |
+----------------+-------+
| flying animals | eagle |
| flying animals | hawk |
+----------------+-------+

--
Mail to my "From:" address is readable by all at http://www.dodgeit.com/
== ** ## !! ------------------------------------------------ !! ## ** ==
TEXT-ONLY mail to the whole "Reply-To:" address ("My Name" <my@address>)
may bypass my spam filter. If it does, I may reply from another address!

[badr]

Re: Leech (?) content of mySQL tables

hi , knoak

if i undrestand your quz , you are talking about the referential
integrity , that mean you want change , delete or add data to some
filed and it take effect to some other joind table , if so , in MySQL
the foregin key AND referential integrity are not supported in all type
of tables, like MyISAM, or HEAP , but in INNODB type yes they are.
so when you create your won tables you have to implement the
referential integrity by specifying your foreign key and table type.

for example if you have sections and employees and each employee have
difrenet permission to do some job we can make this :
Create table employee ("
emp_id varchar(15) PRIMARY KEY,
login_name varchar(25) not null unique,
login_pass varchar(15) not null,
empname varchar(20) not null,
index (emp_id)") TYPE=INNODB #####check the type (INNODB)

create table permission ("
emp_id Varchar(15) not null,
object_id int not null,
object_name varchar(40) not null,
index (emp_id),
index (object_id),
primary key (emp_id,object_ id),
foreign key (emp_id)
references employee (emp_id) on delete cascade on update cascade") ##
here the
TYPE=INNODB #here also the type is INNODB

in this case if you delete employee from employee table its permission
will be deleted also , the same thing for updating. so it will be
cascaded in both operation

chek the versions of MySQL they may make it supported in some versions

http://www.mysql.com/search/?q=referential+integrity+&charset=iso-8859-1

for more information about referential integrity

http://www.databasejournal.com/features/mysql/article.php/2248101

[Michael Fesser]

Re: Leech (?) content of mySQL tables

.oO(knoak)
[color=blue]
>Imagine i have two tables. One is called species, and one
>is called animals.
>One of the species is 'birds' (others are fishes etc.)
>In my table animals one of the birds is a hawk, another an eagle.
>Anyway, can i get a field in 'animals', that automatically has the same
>content as a defined field in 'species'. So if i would change 'birds'
>to 'flying animals' in the 'species' table, it would also change
>in the 'animals' table.[/color]

Possible, but in most cases you don't really want to store the same
information twice in different tables. It would be a bad and not
normalized design. Such redundancy wastes space and may cause problems
on updates for example.

Giving each species a numeric ID and using that for reference in the
animals table would be better in this case. Pedro gave an example how to
do it.

Micha