I understand your question. I am so sorry to be so slow today :-)
You want to know how to create a PRIMARY KEY that is composed of more than
one column.
Most of the time when we declare a PRIMARY KEY on a table, we do it by
putting the keywords "PRIMARY KEY" at the end of the column definition,
like this:
CREATE TABLE example1(
ID int not null auto_increment primary key,
field2 char(5) ,
... more fields ...
)
But if you need more than one column to define the PRIMARY KEY for a table
you CANNOT say:
CREATE TABLE example2(
id_table1 int not null primary key,
id_table2 int not null primary key
)
because, in MySQL that is a syntax error. What you need is:
CREATE TABLE example3 (
ID int auto_increment,
id_table1 int not null,
id_table2 int not null,
PRIMARY KEY(id_table1, id_table2)
)
You may be able to read:
http://dev.mysql.com/doc/mysql/pt/CREATE_TABLE.html#IDX1582
for a better explanation.
Yours,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
"Rui Monteiro" <RuiSMonteiro@stripped> wrote on 07/20/2004 01:02:36 PM:
> Hello,
>
> The example I gave you has 2 foreign keys that are primary keys on that
table.
>
> Heres na example
>
> Costumer
> Id (PK)
> Name
>
> Shopping list
> ID (PK)
> ID_costumer
> ID_product
>
>
> The relationship between these 2 tables is ?infinite? to ?infinite?.
> The way to resolve this is by creating a table in the middle like this:
>
> COS/SHOP
> ID_Cust (PK)
> ID_Shop (PK)
>
> Costumer ? 1 : N ? COS/SHOP
> Shopping list ? 1 : N ? COS/SHOP
>
> Thanks
>
>
>
> De: SGreen@stripped [mailto:SGreen@stripped]
> Enviada: terça-feira, 20 de Julho de 2004 17:06
> Para: Rui Monteiro
> Cc: mysql@stripped
> Assunto: Re: Primary Keys
>
>
> Your example has 1 Primary Key and 2 Foreign Keys.
>
> Please post a sample data structure and state (not in SQL) what
> situation you want to achieve. If you need more constraints on the
> table to prevent creating duplicates you can create additional
> UNIQUE Keys but, by definition, any table should have only one Primary
Key.
>
> Yours,
>
> Shawn Green
> Database Administrator
> Unimin Corporation - Spruce Pine
>
> "Rui Monteiro" <RuiSMonteiro@stripped> wrote on 07/20/2004 11:54:00
AM:
>
> > Mello,
> >
> >
> >
> > I was wondering why canto r how can I put 2 primary keys on a table?
> >
> >
> >
> > Here's na example on Oracle language:
> >
> >
> >
> > CREATE TABLE FacturaMusica(
> >
> > CodFactura number(4), CONSTRAINTS
FK_FacturaMusica_CodFactura
> > FOREIGN KEY(CodFactura) REFERENCES Factura(CodFactura),
> >
> > CodMusica number(4), CONSTRAINTS
FK_FacturaMusica_CodMusica
> > FOREIGN KEY(CodMusica) REFERENCES Musica(CodMusica),
> >
> > CONSTRAINT PK_FacturaMusica PRIMARY
KEY(CodFactura,CodMusica)
> >
> > );
> >
> >
> >
> > This is very usefull to break "n" to "n" relations.
> >
> >
> >
> > Any tip?
> >
> >
> >
> > Thanks
> >
> >
> >
> > Rui
> >
