"Jorge Calas" <[EMAIL PROTECTED]> wrote:
>
> Hello, I'm new to SQL and MySQL, I'm developing a web application (php) and I need
> some tables relationed between them with a foreing key. The idea is the following
>
> create table persons (
> cid smallint auto_increment not null unique,
> name varchar(30),
> alias varchar(10),
> primary key (cid));
>
> create table phones(
> pid smallint auto_increment not null unique,
> cid smallint not null,
> phone varchar(10),
> obs varchar(40),
> primary key (pid));
>
> There are many other tables like phones and I will need to do with them the same
> thinks.
>
> The questions are the followings:
>
> - How do I preserve integrity? I mean, I don't want to have phones without persons,
> so when I delete a person I want to make a cascade erase.
With InnoDB tables you can use ON DELETE CASCADE. With MyISAM tables you should delete
corresponding row from phone table manually.
> - When I do a:
> select * from persons left join phones using (cid);
> I get as many rows as phones have this person, is there any way to make a query that
> returns a single row with all the information.
>
Since version 4.1 you can use function GROUP_CONCAT():
http://www.mysql.com/doc/en/GROUP-BY-Functions.html
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Victoria Reznichenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net
<___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
