Received: from golem.jpl.nasa.gov (IDENT:root@hectic-1.jpl.nasa.gov [128.149.68.203]) by hub.org (8.9.1/8.9.1) with ESMTP id AAA11149 for ; Fri, 1 Jan 1999 00:41:05 -0500 (EST) (envelope-from lockhart@alumni.caltech.edu) Received: from alumni.caltech.edu (localhost [127.0.0.1]) by golem.jpl.nasa.gov (8.8.5/8.8.5) with ESMTP id FAA08740; Fri, 1 Jan 1999 05:40:56 GMT Sender: tgl@mythos.jpl.nasa.gov Message-ID: <368C5FE8.5032023A@alumni.caltech.edu> Date: Fri, 01 Jan 1999 05:40:56 +0000 From: "Thomas G. Lockhart" Organization: Caltech/JPL X-Mailer: Mozilla 4.07 [en] (X11; I; Linux 2.0.30 i686) MIME-Version: 1.0 To: "Michael J. Sheldon" CC: "'pgsql-interfaces@postgresql.org'" Subject: Re: [INTERFACES] ODBC CREATE TABLE failures References: <816099F7DDEFD111B37F00A0C90FD97D089B0D@gyrfalcon.cbrcomm.com> Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit > The application will automatically create the database structure if it > does not already exist > However, only the first table is being created. > Code follows: You can test this code by running it through psql, and it fails there too. Each CREATE TABLE statement is trying to use the same label for the primary key constraint, but since Postgres enforces the primary key by generating an implicit index this fails. Try using unique names for constraints! If for some reason these constraint names *must* be identical, you could consider modifying the code in the parser to generate a unique internal name based on both the constraint name and the table name, for example. There is already a routine there to help you do this. Failure example follows... - Tom postgres=> CREATE TABLE FMGroups postgres-> ( postgres-> GroupID INTEGER NOT NULL, postgres-> GroupName VARCHAR(64) NOT NULL, postgres-> Description VARCHAR(100), postgres-> CONSTRAINT ID_pk PRIMARY KEY (GroupID), postgres-> CONSTRAINT check_directory UNIQUE (GroupName) postgres-> ); NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index id_pk for table fmgroups NOTICE: CREATE TABLE/UNIQUE will create implicit index check_directory for table fmgroups CREATE postgres=> CREATE TABLE FMDirectories postgres-> ( postgres-> DirectoryID INTEGER NOT NULL, postgres-> DirectoryName VARCHAR(64) NOT NULL, postgres-> Description VARCHAR(100), postgres-> ParentID INTEGER NOT NULL, postgres-> CONSTRAINT ID_pk PRIMARY KEY (DirectoryID), postgres-> CONSTRAINT check_directory UNIQUE (DirectoryName) postgres-> ); NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index id_pk for table fmdirectories NOTICE: CREATE TABLE/UNIQUE will create implicit index check_directory for table fmdirectories ERROR: Cannot create index: 'id_pk' already exists