Received: from lambton.sslnz.com (intranet.sslnz.com [203.109.146.4]) by postgresql.org (8.11.3/8.11.4) with ESMTP id g0DKJLB50647 for ; Sun, 13 Jan 2002 15:19:21 -0500 (EST) (envelope-from sharon.cowling@sslnz.com) Received: from sslnz.com (FIJI.sslnz.com [192.168.2.249]) by lambton.sslnz.com (8.11.4/8.11.3) with ESMTP id g0DKJLv15052 for ; Mon, 14 Jan 2002 09:19:21 +1300 Message-Id: <200201132019.g0DKJLv15052@lambton.sslnz.com> From: Sharon Cowling To: pgsql-sql@postgresql.org Subject: Problem with unique index Date: Sun, 13 Jan 2002 20:23:00 GMT X-Mailer: CorporateTime Outlook Connector 3.1 (R.3.1.3.0815) MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit X-MIME-Autoconverted: from quoted-printable to 8bit by postgresql.org id g0DKPsC51873 X-Archive-Number: 200201/110 X-Sequence-Number: 6199 I have a table which I need to have 2 unique indexes on: Drivers licence and Firearms Licence, my problem is, not everyone has a licence so I need the ability for the column to not be entered. However I get an error that I cannot insert a duplicate key into the unique index. Below is the view, rule and insert. As you can see I am excluding the 2 licences from the view, rule and insert, although they exist in the table. This is because in the application I test to see if it has been entered and then run a different sql insert depending on the data entered. I really need these to be unique values, I was sure that a null value would be ok, help please! CREATE VIEW addperson_view AS SELECT p.person_id, p.firstname, p.lastname, p.dob, p.street, p.suburb, p.city, p.homephone, p.workphone, p.mobile, p.type, p.date_approved, p.approved_by, p.vehicle_type, p.vehicle_rego, p.notes, fp.location1, fp.location2, fp.location3 FROM person p, forest_person fp; CREATE RULE addperson_rule AS ON INSERT TO addperson_view DO INSTEAD( INSERT INTO person VALUES (new.person_id, new.firstname, new.lastname, new.dob, new.street, new.suburb, new.city, new.homephone, new.workphone, new.mobile, new.type, new.date_approved, new.approved_by, new.vehicle_type, new.vehicle_rego, new.notes); INSERT INTO forest_person VALUES (new.person_id,new.location1,new.location2,new.location3); ); user=>insert into addperson_view (person_id, firstname, lastname, dob, street, suburb, city, homephone, workphone, mobile, type, date_approved, approved_by, vehicle_type, vehicle_rego, notes, location1, location2, location3) values(nextval('seq_person_id'), 'Sharon', 'Cowling', '16-10-78', 'My Street', 'My Suburb', 'My City', '5555568','5555567','0255556674','Other', '09-01-02', 'test', 'Nissan','AAA121','Test Data Notes','Forest1','Forest2','Forest3'); ERROR: Cannot insert a duplicate key into unique index person_drivers_licence_key (It also errors on firearms licence) Table Structure: CREATE TABLE person( person_id INT NOT NULL, firstname VARCHAR(25) NOT NULL, lastname VARCHAR(25) NOT NULL, dob date NOT NULL, street VARCHAR(50) NOT NULL, suburb VARCHAR(50), city VARCHAR(50) NOT NULL, homephone INT, workphone INT, mobile INT, type VARCHAR(30) NOT NULL, date_approved DATE NOT NULL, approved_by VARCHAR(50) NOT NULL, vehicle_type VARCHAR(50), vehicle_rego VARCHAR(6), drivers_licence VARCHAR(10) UNIQUE, firearms_licence VARCHAR(20) UNIQUE, notes VARCHAR(80), PRIMARY KEY (person_id)); Regards, Sharon Cowling