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" <[EMAIL PROTECTED]> 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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > Enviada: terça-feira, 20 de Julho de 2004 17:06 > Para: Rui Monteiro > Cc: [EMAIL PROTECTED] > 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" <[EMAIL PROTECTED]> 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 > >
