CREATE TABLE .. PRIMARY KEY quirk
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");'
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