I'm using PostgreSQL 9.3. I want to understand if I have an option to make a constraint unique across the entire table vs. unique across a subset of the table (i.e. by using 2 columns in the unique constraint, I restrict the uniqueness), which one is better for lookups?
Consider this table where a unique alphanumeric code is allotted to each student of the class.
CREATE TABLE sc_table (
name text NOT NULL,
code text NOT NULL,
class_id integer NOT NULL,
CONSTRAINT class_fk FOREIGN KEY (class_id) REFERENCES class (id),
CONSTRAINT sc_uniq UNIQUE (code)
);
Currently the code is unique across the entire table. However the specification says that it is sufficient for the code to be unique across the class only. For my design requirements there's no restriction either way.
However if I change the constraint to be unique for a given class only, how would it affect lookup by code?
Or, in other words, which of the following combination of constraint & lookup is the best speed wise:
-- 1. unique across entire table, lookup by value
CONSTRAINT sc_uniq UNIQUE (code)
SELECT * FROM sc_table WHERE code='alpha-2-beta'
-- 2. unique across entire table, lookup by value & class
CONSTRAINT sc_uniq UNIQUE (code)
SELECT * FROM sc_table WHERE class_id=1 AND code='alpha-2-beta'
-- 3. unique per class, lookup by value
CONSTRAINT sc_uniq UNIQUE (code, class_id)
SELECT * FROM sc_table WHERE code='alpha-2-beta'
-- 4. unique per class, lookup by value & class
CONSTRAINT sc_uniq UNIQUE (code, class_id)
SELECT * FROM sc_table WHERE class_id=1 AND code='alpha-2-beta'
My understanding is that 2 is better than 1 & 4 is better than 3. But which one's better between 1-vs-3 & 2-vs-4?
Aucun commentaire:
Enregistrer un commentaire