Received: from cummings.uol.com.br (cummings.uol.com.br [200.230.198.69]) by hub.org (8.10.1/8.10.1) with ESMTP id e6A1oNW90362 for ; Sun, 9 Jul 2000 21:50:24 -0400 (EDT) Received: from maximus ([200.227.203.78]) by cummings.uol.com.br (8.9.1/8.9.1) with SMTP id WAA01122 for ; Sun, 9 Jul 2000 22:50:09 -0300 (BRT) From: "Paulo Roberto Siqueira" To: "PGSQL SQL" Subject: CREATE TABLE with foreign key and primary key Date: Sun, 9 Jul 2000 22:49:14 -0300 Message-ID: MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook IMO, Build 9.0.2416 (9.0.2910.0) Importance: Normal X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2615.200 X-Archive-Number: 200007/92 I'm trying to create a table that has three fields as primary key. These fields must exist in two other tables. id_aluno is primary key in table pessoa, id_curso and id_polo are primary key in table curso_polo. But it seems it won't work. What am I doing wrong? See definitions below. Specifically, I didn't understand: NOTICE: Illegal FOREIGN KEY definition REFERENCES "curso_polo" ERROR: number of key attributes in referenced table must be equal to foreign key I'm using PostgreSQL 7.0 ufgvirtual=# create table matricula ( ufgvirtual(# id_aluno char(15) references pessoa, ufgvirtual(# id_curso int4 references curso_polo, ufgvirtual(# id_polo int2 references curso_polo, ufgvirtual(# local_prova varchar(50) not null, ufgvirtual(# autorizado bool default 'f' not null, ufgvirtual(# id_plano_pgto int2 references plano_pgto not null, ufgvirtual(# data_matricula date default CURRENT_DATE not null, ufgvirtual(# primary key(id_aluno,id_curso,id_polo)); NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'matricula_pkey' for table 'matricula' NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s) NOTICE: Illegal FOREIGN KEY definition REFERENCES "curso_polo" ERROR: number of key attributes in referenced table must be equal to foreign key Table "pessoa" Attribute | Type | Modifier ---------------+----------+------------------------------------------------- login | char(15) | not null nome | char(50) | not null . . . Table "curso_polo" Attribute | Type | Modifier ---------------+----------+---------- id_curso | integer | not null id_polo | smallint | not null coordenador | integer | not null data_limite | date | id_plano_pgto | smallint | num_vagas | integer | not null I have tables pessoa, curso, polo, curso_polo and matricula. Primary key in curso_polo are id_curso (references curso) and id_polo (references polo). In table matricula I want as primary key id_pessoa (references pessoa), id_curso (references curso_polo) and id_polo (references curso_polo). I hope I have provided enough information. I'm a little confused. Paulo R. Siqueira