CREATE TABLE .. PRIMARY KEY quirk

Started by Colin Stricklandover 24 years ago2 messages
#1Colin Strickland
cms@sift.co.uk

Issuing the following ( admittedly bogus ) statement against 7.1.1

CREATE TABLE dir_suppliers_var_prodtype (
dir_suppliers_var_prodtype_id INTEGER ,
dir_suppliers_var_id integer DEFAULT 0 NOT NULL,
prodtype_id smallint DEFAULT 0 NOT NULL,
PRIMARY KEY
(dir_suppliers_var_prodtype_id,dir_suppliers_var_prodtype_id)
);

gives the following , initially slightly cryptic response.

NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index
'dir_suppliers_var_prodtype_pkey' for table 'dir_suppliers_var_prodtype'

ERROR: Cannot insert a duplicate key into unique index
pg_attribute_relid_attnam_index

This is obviously because of the broken primary key definition.

My question is, should this not raise a parser error ? It took me a
little while to actually spot the problem with the users statement.

--
Colin M Strickland perl -e'print "\n",map{chr(ord()-3)}(reverse split
//,"\015%vhlwlqxpprF#ir#uhzrS#hkw#jqlvvhqudK%#\015\015nx".
"1rf1wilv1zzz22=swwk###369<#84<#:44#77.={di##339<#84<#:44#77.=ohw\015]".
"K9#4VE#/ORWVLUE#/whhuwV#dlurwflY#334#/wilV\015uhsrohyhG#ehZ#urlqhV");'

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Colin Strickland (#1)
Re: CREATE TABLE .. PRIMARY KEY quirk

Colin Strickland <cms@sift.co.uk> writes:

PRIMARY KEY
(dir_suppliers_var_prodtype_id,dir_suppliers_var_prodtype_id)

My question is, should this not raise a parser error ?

Yes, it should. SQL92 saith

4) Each <column name> in the <unique column list> shall identify
a column of T, and the same column shall not be identified more
than once.

Looks like we neglect to make that check during initial processing of
the PRIMARY KEY clause.

regards, tom lane