Brauche ich einen primary key für meine Tabelle, die einen EINDEUTIGEN (zusammengesetzten 4-Säulen), von denen eine NULL ist?
Habe ich die folgende Tabelle (PostgreSQL-8.3) speichert, Preise für einige Produkte. Die Preise sind synchronisiert mit einer anderen Datenbank, im wesentlichen die meisten Felder (bis auf eines) sind nicht aktualisiert, von unserem Kunden, sondern gelöscht und aktualisiert alle once-in-a-Weile, um zu synchronisieren mit einem anderen Lager-Datenbank:
CREATE TABLE product_pricebands (
template_sku varchar(20) NOT NULL,
colourid integer REFERENCES colour (colourid) ON DELETE CASCADE,
currencyid integer NOT NULL REFERENCES currency (currencyid) ON DELETE CASCADE,
siteid integer NOT NULL REFERENCES site (siteid) ON DELETE CASCADE,
master_price numeric(10,2),
my_custom_field boolean,
UNIQUE (template_sku, siteid, currencyid, colourid)
);
An der synchronisation, die ich im Grunde LÖSCHEN Sie die meisten der obigen Daten, außer für Daten, WO my_custom_field WAHR ist (wenn es WAHR ist, bedeutet es, dass der client aktualisiert das Feld über Ihre CMS-und deshalb soll dieser Datensatz nicht gelöscht werden). Ich FÜGEN Sie dann die 100te bis 1000de von Zeilen in der Tabelle und AKTUALISIEREN Sie, wo der Vorgang schlägt fehl (D. H. wenn die Kombination von (template_sku, siteid, currencyid, colourid) bereits vorhanden ist).
Meine Frage ist - was die beste Praxis sollte hier angewandt werden, um einen Primärschlüssel zu erstellen? Ein Primärschlüssel ist Sie sogar notwendig? Ich wollte die Primärschlüssel = (template_sku, siteid, currencyid, colourid) - aber die colourid Feld kann NULL sein, und es auch in einem zusammengesetzten Primärschlüssel nicht möglich.
Von was ich gelesen habe auf anderen Foren, ich glaube, ich habe das oben richtig, und brauchen nur zu klären:
1) Sollte ich ein "serial" primary key falls ich jemals einen brauchen? Im moment weiß ich nicht, und glaube nicht, dass ich jemals wird, denn die wichtigen Daten in der Tabelle ist der Preis und mein benutzerdefiniertes Feld, nur gekennzeichnet durch das (template_sku, siteid, currencyid, colourid) Kombination.
2) Da (template_sku, siteid, currencyid, colourid) ist die Kombination, die ich verwenden, um eine Abfrage eines Produkts, Preis, sollte ich hinzufügen, eine weitere Indizierung zu meinem Spalten, wie das "template_sku" das ist ein varchar? Oder ist die UNIQUE-Einschränkung wird ein guter index schon für mein Auswählt?
- colourid null-Werte zulässt. Das macht es zu einem fiesen Mitglied für einen Primärschlüssel.
- OK, hier ist die einfache Antwort. Wenn Sie eine Tabelle mit Spalten a,b,c,d und d, die null-Werte zulässt, können Sie live mit Zeilen, die die gleiche a -, b-und c-Felder? Wenn ja, dann was du hast ist in Ordnung. Wenn Sie brauchen, um Sie auseinander zu halten, dann müssen Sie einen eindeutigen index für a,b,c oder einen partiellen index auf a,b,c, wobei d null ist.
Du musst angemeldet sein, um einen Kommentar abzugeben.
Können Sie ganz einfach eine serial-Spalte später, wenn Sie einen brauchen:
Wird die Spalte gefüllt werden mit einzigartigen Werte automatisch. Auch können Sie stellen Sie den primär-Schlüssel in der gleichen Anweisung (wenn kein Primärschlüssel definiert ist, aber dennoch):
Wenn Sie auf die Tabelle aus anderen Tabellen, die ich würde Ihnen raten, einen solchen Surrogat Primärschlüssel, denn es ist ziemlich schwerfällig, um link durch vier Spalten. Es ist auch langsamer in SELECTs mit JOINs.
Entweder Weg, Sie sollte einen Primärschlüssel definieren. Der EINDEUTIGE index, darunter eine null-Spalte ist nicht ein vollständiger Ersatz. Es kann Duplikate für Kombinationen, darunter ein NULL-Wert, da die zwei NULL-Werte sind nie als die gleichen. Dies kann zu Schwierigkeiten führen.
Als
möglicherweise möchten Sie erstellen zwei eindeutige Indizes. Die Kombination
(template_sku, siteid, currencyid, colourid)
keinePRIMARY KEY
wegen der null-Werte zulässtcolourid
, aber Sie können erstellen einUNIQUE
Einschränkung, wie Sie bereits haben (die Umsetzung eines index automatisch):Dieser index deckt perfekt den Fragen, die Sie erwähnen, in 2).
Erstellen Sie eine teilweise eindeutigen index zusätzlich, wenn Sie wollen vermeiden, "Duplikate" mit
(colourid IS NULL)
:Decken alle Basen. Ich schrieb mehr über diese Technik in einem Verwandte Antwort auf dba.SE.
Die einfache alternative zu den oben genannten ist, um
colourid
NICHT NULL, und erstellen Sie eine primäre Taste anstelle der obenproduct_pricebands_uni_idx
.Ist auch, wie Sie
für Ihre refill-Betrieb, wird es schneller sein, um die drop-Indizes, die sind nicht benötigt während der refill-Vorgang, und erstellen Sie diese anschließend. Es ist schneller, um eine Größenordnung zu bauen, die einen index von Grund auf als für alle Zeilen inkrementell.
Woher wissen Sie, welche Indexe benutzt (gebraucht)?
EXPLAIN ANALYZE
.Kann es auch schneller sein, wählen Sie die wenigen Zeilen mit
my_custom_field = TRUE
in eine temporäre Tabelle,TRUNCATE
der Basis-Tabelle und LEGEN Sie die re-überlebenden. Hängt davon ab, ob Fremdschlüssel definiert. Würde dann so Aussehen:Dies vermeidet eine Menge Staubsaugen.