CREATE | ALTER TABLE … CONSTRAINT¶

Unter diesem Thema wird beschrieben, wie Sie Einschränkungen erstellen, indem Sie eine CONSTRAINT-Klausel in einer CREATE TABLE-, CREATE HYBRID TABLE- oder ALTER TABLE-Anweisung angeben:

Eine Inline-Einschränkung wird als Teil der Definition der einzelnen Spalten angegeben.
Eine Out-of-Line-Einschränkungen wird als unabhängige Klausel angegeben:
- Beim Erstellen einer Tabelle ist die Klausel Teil der Spaltendefinitionen für die Tabelle.
- Beim Ändern einer Tabelle wird die Klausel als explizite ADD-Aktion für die Tabelle angegeben.

Weitere Informationen dazu finden Sie unter Einschränkungen.

Wenn Sie Hybridtabellen erstellen oder ändern, ist die Syntax für die Definition von Einschränkungen dieselbe; die Regeln und Anforderungen sind jedoch unterschiedlich.

Syntax für Inline-Einschränkungen¶

CREATE TABLE <name> (
  <col1_name> <col1_type>  [ NOT NULL ] { inlineUniquePK | inlineFK | inlineCH }
  [ , <col2_name> <col2_type> [ NOT NULL ] { inlineUniquePK | inlineFK | inlineCH } ]
  [ , ... ]
)

ALTER TABLE <name> ADD COLUMN
  <col_name> <col_type> [ NOT NULL ] { inlineUniquePK | inlineFK | inlineCH }

Wobei:

inlineUniquePK ::=
  [ CONSTRAINT <constraint_name> ]
  { UNIQUE | PRIMARY KEY }
  [ [ NOT ] ENFORCED ]
  [ [ NOT ] DEFERRABLE ]
  [ INITIALLY { DEFERRED | IMMEDIATE } ]
  [ { ENABLE | DISABLE } ]
  [ { VALIDATE | NOVALIDATE } ]
  [ { RELY | NORELY } ]

inlineFK ::=
  [ CONSTRAINT <constraint_name> ]
  [ FOREIGN KEY ]
  REFERENCES <ref_table_name> [ ( <ref_col_name> ) ]
  [ MATCH { FULL | SIMPLE | PARTIAL } ]
  [ ON [ UPDATE { CASCADE | SET NULL | SET DEFAULT | RESTRICT | NO ACTION } ]
       [ DELETE { CASCADE | SET NULL | SET DEFAULT | RESTRICT | NO ACTION } ] ]
  [ [ NOT ] ENFORCED ]
  [ [ NOT ] DEFERRABLE ]
  [ INITIALLY { DEFERRED | IMMEDIATE } ]
  [ { ENABLE | DISABLE } ]
  [ { VALIDATE | NOVALIDATE } ]
  [ { RELY | NORELY } ]

inlineCH ::=
  [ CONSTRAINT <constraint_name> ] CHECK ( <expr> )
  [ ENABLE { VALIDATE | NOVALIDATE } ]

Syntax für Out-of-Line-Einschränkungen¶

CREATE TABLE <name> ... (
  <col1_name> <col1_type>
  [ , <col2_name> <col2_type> , ... ]
  [ , { outoflineUniquePK | outoflineFK | outoflineCH } ]
  [ , { outoflineUniquePK | outoflineFK | outoflineCH } ]
  [ , ... ]
)

ALTER TABLE <name> ... ADD { outoflineUniquePK | outoflineFK | outoflineCH }

Wobei:

outoflineUniquePK ::=
  [ CONSTRAINT <constraint_name> ]
  { UNIQUE | PRIMARY KEY } ( <col_name> [ , <col_name> , ... ] )
  [ [ NOT ] ENFORCED ]
  [ [ NOT ] DEFERRABLE ]
  [ INITIALLY { DEFERRED | IMMEDIATE } ]
  [ { ENABLE | DISABLE } ]
  [ { VALIDATE | NOVALIDATE } ]
  [ { RELY | NORELY } ]
  [ COMMENT '<string_literal>' ]

outoflineFK ::=
  [ CONSTRAINT <constraint_name> ]
  FOREIGN KEY ( <col_name> [ , <col_name> , ... ] )
  REFERENCES <ref_table_name> [ ( <ref_col_name> [ , <ref_col_name> , ... ] ) ]
  [ MATCH { FULL | SIMPLE | PARTIAL } ]
  [ ON [ UPDATE { CASCADE | SET NULL | SET DEFAULT | RESTRICT | NO ACTION } ]
       [ DELETE { CASCADE | SET NULL | SET DEFAULT | RESTRICT | NO ACTION } ] ]
  [ [ NOT ] ENFORCED ]
  [ [ NOT ] DEFERRABLE ]
  [ INITIALLY { DEFERRED | IMMEDIATE } ]
  [ { ENABLE | DISABLE } ]
  [ { VALIDATE | NOVALIDATE } ]
  [ { RELY | NORELY } ]
  [ COMMENT '<string_literal>' ]

outoflineCH ::=
  [ CONSTRAINT <constraint_name> ] CHECK ( <expr> )
  [ ENABLE { VALIDATE | NOVALIDATE } ]

Einschränkungseigenschaften¶

Aus Gründen der Kompatibilität mit anderen Datenbanken und für die Verwendung mit Hybridtabellen bietet Snowflake Einschränkungseigenschaften. Die Eigenschaften, die für eine Einschränkung angegeben werden können, hängen vom Typ ab:

Einige Eigenschaften gelten für alle Schlüssel (Unique/Primär/Fremd).
Andere Eigenschaften gelten nur für Fremdschlüssel.

Wichtig

Für Standard-Snowflake-Tabellen werden diese Eigenschaften bereitgestellt, um die Migration von anderen Datenbanken zu erleichtern. Sie werden nicht von Snowflake durchgesetzt oder verwaltet. Das bedeutet, dass die Standardwerte für diese Eigenschaften geändert werden können, aber das Ändern der Standardwerte führt dazu, dass Snowflake die Einschränkung nicht erstellt.

Eine Ausnahme ist die Eigenschaft RELY. Wenn Sie sichergestellt haben, dass die Daten in Ihren Standardtabellen den UNIQUE-, PRIMARY KEY- und FOREIGN KEY-Einschränkungen entsprechen, können Sie die Eigenschaft RELY für diese Einschränkungen festlegen. Siehe auch Einstellen der RELY-Einschränkungseigenschaft zum Beseitigen unnötiger Join-Verknüpfungen.

Wenn Sie Hybridtabellen erstellen oder ändern, gelten andere Regeln und Anforderungen. Siehe Übersicht über Einschränkungen.

Die meisten der unterstützten Einschränkungseigenschaften sind ANSI SQL-Standardeigenschaften; die folgenden Eigenschaften sind jedoch Snowflake-Erweiterungen:

ENABLE | DISABLE
VALIDATE | NOVALIDATE
RELY | NORELY

Sie können auch einen Kommentar innerhalb einer Definition für eine Out-of-Line-Einschränkung; siehe Kommentare zu Einschränkungen.

Eigenschaften (für alle Einschränkungen)¶

Die folgenden Eigenschaften gelten für alle Einschränkungen (die Reihenfolge der Eigenschaften ist austauschbar):

[ NOT ] ENFORCED
[ NOT ] DEFERRABLE
INITIALLY { DEFERRED | IMMEDIATE }
{ ENABLE | DISABLE }
{ VALIDATE | NOVALIDATE }
{ RELY | NORELY }

{ ENFORCED | NOT ENFORCED }

Gibt an, ob die Einschränkung in einer Transaktion erzwungen wird. Für Standardtabellen ist NOT NULL die einzige Art von Einschränkung, die von Snowflake unabhängig von dieser Eigenschaft durchgesetzt wird.

Bei Hybridtabellen können Sie die Eigenschaft NOT ENFORCED nicht für PRIMARY KEY-, FOREIGN KEY- und UNIQUE-Einschränkungen festlegen. Die Einstellung dieser Eigenschaft führt zum Fehler „invalid constraint property“ (ungültige Einschränkungseigenschaft).

Siehe auch Einschränkungen für die referenzielle Integrität.

Standard: NOTENFORCED

{ DEFERRABLE | NOT DEFERRABLE }

Gibt an, ob bei nachfolgenden Transaktionen die Einschränkungsprüfung bis zum Ende der Transaktion zurückgestellt werden kann.

Standard: NOTDEFERRABLE

INITIALLY { DEFERRED | IMMEDIATE }

Gibt für Einschränkungen vom Typ DEFERRABLE an, ob die Prüfung der Einschränkungen mit Beginn der nächsten Transaktion zurückgestellt wird.

Standard: INITIALLYDEFERRED

{ ENABLE | DISABLE }

Gibt an, ob die Einschränkung aktiviert oder deaktiviert ist. Diese Eigenschaften werden aus Gründen der Kompatibilität mit Oracle bereitgestellt.

Standard: DISABLE

{ VALIDATE | NOVALIDATE }

Gibt an, ob vorhandene Daten in der Tabelle überprüft werden sollen, wenn eine Einschränkung erstellt wird. Gilt nur, wenn entweder { ENFORCED | NOT ENFORCED } oder { ENABLE | DISABLE } angegeben ist.

Standardwert für PRIMARY KEY- und FOREIGNKEY-Einschränkungen: NOVALIDATE

Standardwert für CHECK-Einschränkungen: VALIDATE

{ RELY | NORELY }

Gibt an, ob eine Einschränkung im Modus NOVALIDATE beim Neuschreiben von Abfragen berücksichtigt wird.

Wenn Sie sichergestellt haben, dass die Daten in der Tabelle den Einschränkungen entsprechen, können Sie diese Eigenschaft in RELY ändern, um anzuzeigen, dass die Abfrageoptimierung eine solche Datenintegrität erwarten sollte. Bei Standardtabellen liegt es in Ihrer Verantwortung, die RELY-Einschränkungen durchzusetzen. Andernfalls riskieren Sie eine unbeabsichtigte Verhaltensweise und unerwartete Ergebnisse.

Wenn die RELY-Eigenschaft für eine Einschränkung festgelegt ist und es zu einer Verletzung der referenziellen Integrität kommt, könnten DML- und CTAS-Anweisungen falsche Daten einfügen.

Durch Festlegen der Eigenschaft RELY lässt sich die Abfrageleistung verbessern (z. B. indem Beseitigen unnötiger Verknüpfungen).

Für zugehörige PRIMARY KEY und FOREIGN KEY-Einschränkungen legen Sie diese Eigenschaft für beide Einschränkungen fest. Beispiel:

ALTER TABLE table_with_primary_key ALTER CONSTRAINT a_primary_key_constraint RELY;
ALTER TABLE table_with_foreign_key ALTER CONSTRAINT a_foreign_key_constraint RELY;

Standard: NORELY

Eigenschaften (nur für FOREIGN KEY-Einschränkungen)¶

Die folgenden Einschränkungseigenschaften gelten nur für Fremdschlüssel (die Reihenfolge der Eigenschaften ist austauschbar):

MATCH { FULL | SIMPLE | PARTIAL }
ON [ UPDATE { CASCADE | SET NULL | SET DEFAULT | RESTRICT | NO ACTION } ]
   [ DELETE { CASCADE | SET NULL | SET DEFAULT | RESTRICT | NO ACTION } ]

MATCH { FULL | PARTIAL | SIMPLE }

Gibt an, ob die FOREIGN KEY-Einschränkung in Bezug auf NULL-Werte in einer oder mehreren der Spalten erfüllt ist.

Standard: MATCHFULL

UPDATE { CASCADE | SET NULL | SET DEFAULT | RESTRICT | NO ACTION }

Gibt die Aktion an, die ausgeführt werden soll, wenn der Primärschlüssel oder der eindeutige Schlüssel für den Fremdschlüssel aktualisiert wird.

Standard: UPDATE NO ACTION

DELETE { CASCADE | SET NULL | SET DEFAULT | RESTRICT | NO ACTION }

Gibt die Aktion an, die ausgeführt werden soll, wenn der Primärschlüssel/eindeutige Schlüssel für den Fremdschlüssel gelöscht wird.

Standard: DELETE NO ACTION

Eigenschaften (nur für CHECK-Einschränkungen)¶

Die folgenden Einschränkungseigenschaften gelten nur fürCHECK-Einschränkungen:

CHECK ( <expr> )

CHECK ( expr )

Ein Ausdruck, der die zu erzwingende Bedingung definiert.

Der Ausdruck kann eines der folgenden Elemente enthalten:

Tabellenspalten, die in der Tabelle definiert sind, auf der die CHECK-Einschränkung ausgeführt wird.
Konstantenwerte.
Skalarfunktionen, die nicht auf die Umgebung oder den Ausführungskontext angewiesen sind.

Der Ausdruck darf keine der folgenden Elemente enthalten:

Benutzerdefinierte Funktionen (UDFs)
Aggregatfunktionen, Fensterfunktionen, Tabellenfunktionen oder Unterabfragen.
Systemdefinierte Funktionen, die den Datenbankstatus ändern, wie z. B. die SYSTEM $CANCEL_ALL_QUERIES-Funktion.
Nicht deterministische, vom System definierte Funktionen, wie z. B. die RANDOM-Funktion
Systemdefinierte Funktionen, die auf die Umgebung oder den Ausführungskontext angewiesen sind, wie z. B. die CURRENT_DATE-Funktion oder die CURRENT_ROLE-Funktion

Weitere Informationen dazu finden Sie unter CHECK-Einschränkungen.

Nicht-Standardwerte für die Eigenschaften ENABLE und VALIDATE¶

Aus Gründen der Syntaxkompatibilität mit anderen Datenbanken unterstützt Snowflake Angabe von nicht standardmäßigen Werten für Einschränkungseigenschaften.

Für PRIMARY KEY-, UNIQUE- und FOREIGN KEY-Einschränkungen gilt jedoch: Wenn Sie beim Erstellen einer neuen Einschränkung ENABLE oder VALIDATE (die Nicht-Standardwerte für diese Eigenschaften) angeben, wird die Einschränkung nicht erstellt. Dies gilt nicht für RELY. Die Angabe von RELY führt dazu, dass die neue Einschränkung erstellt wird.

Für CHECK-Einschränkungen ist ENABLE die Standardeinstellung und ist erforderlich. Wenn Sie DISABLE angeben, wird die CHECK-Einschränkung nicht erstellt. NOVALIDATE und VALIDATE werden für neue Tabellen unterstützt. VALIDATE wird für bestehende Tabellen nicht unterstützt.

Snowflake stellt einen Sitzungsparameter UNSUPPORTED_DDL_ACTION bereit, der bestimmt, ob die Angabe von Nicht-Standardwerten bei der Erstellung von Einschränkungen einen Fehler erzeugt.

Kommentare zu Einschränkungen¶

Ähnlich wie bei anderen Datenbankobjekten und -konstrukten unterstützt Snowflake Kommentare zu Einschränkungen:

Out-of-Line Einschränkungen unterstützen die COMMENT-Klausel innerhalb der Definition der Einschränkungsdefinition.
```
CREATE OR REPLACE TABLE uni (c1 INT, c2 int, CONSTRAINT uni1 UNIQUE(C1) COMMENT 'Unique column');
```
Eine COMMENT-Klausel innerhalb der einer Spaltendefinition kann verwendet werden, um die Spalte selbst oder ihre der Einschränkung zu kommentieren:
```
CREATE OR REPLACE TABLE uni (c1 INT UNIQUE COMMENT 'Unique column', c2 int);
```

Beachten Sie die folgenden Beschränkungen:

Mit dem Befehl COMMENT können Sie keine Kommentare zu der Einschränkungen einstellen.
Der Befehl DESCRIBE TABLE zeigt Kommentare an, die für Spalten definiert wurden, jedoch keine Kommentare, die für der Einschränkungen definiert wurden. Um Kommentare zu Einschränkungen zu sehen, wählen Sie aus dem Ansicht TABLE_CONSTRAINTS oder dem Ansicht REFERENTIAL_CONSTRAINTS.
Die COMMENT-Klausel innerhalb der Definitionen von Spalten und der Einschränkungen unterstützt das Gleichheitszeichen (=) nicht. Nicht angeben:
```
COMMENT = 'My comment'
```
Verwenden Sie die Syntax, die in den vorherigen Beispielen gezeigt wurde:
```
COMMENT 'My comment'
```

Nutzungshinweise¶

NOT NULL gibt an, dass die Spalte keine NULL-Werte erlaubt:
- Bei Standard-Snowflake-Tabellen ist dies die einzige der Einschränkung, die erzwungen wird. Siehe Einschränkungen für die referenzielle Integrität.
- Sie kann nur als Inline-Einschränkung innerhalb der Spaltendefinition angegeben werden.
- Standardmäßig sind NULL-Werte in Spalten zulässig.
Mehrspaltige Einschränkungen (z. B. zusammengesetzte eindeutige oder Primärschlüssel) können nur außerhalb der Zeile definiert werden.
Bei der Definition von Fremdschlüsseln, entweder inline oder out-of-line, müssen Spaltennamen für die referenzierte Tabelle nur angegeben werden, wenn die Signatur (Name und Datentyp) der Fremdschlüsselspalte(n) und der Primärschlüsselspalte(n) der referenzierten Tabellen nicht übereinstimmt.

Wenn Sie einen Fremdschlüssel erstellen, müssen die Spalten in der REFERENCES-Klausel in der gleichen Reihenfolge aufgeführt werden, wie sie für den Primärschlüssel aufgeführt wurden. Beispiel:
```
CREATE TABLE parent ... CONSTRAINT primary_key_1 PRIMARY KEY (c_1, c_2) ...
CREATE TABLE child  ... CONSTRAINT foreign_key_1 FOREIGN KEY (...) REFERENCES parent (c_1, c_2) ...
```
In beiden Fällen ist die Reihenfolge der Spalten c_1, c_2. Wäre die Reihenfolge der Spalten im Fremdschlüssel eine andere gewesen (z. B. c_2, c_1), wäre der Versuch, den Fremdschlüssel zu erstellen, fehlgeschlagen.

Anforderungen an die Zugriffssteuerung¶

Zum Erstellen von PRIMARY KEY- oderUNIQUE-Einschränkungen:

Wenn Sie eine bestehende Tabelle ändern, um die Einschränkung hinzuzufügen, müssen Sie eine Rolle verwenden, die über die OWNERSHIP-Berechtigung für die Tabelle verfügt.
Wenn Sie eine neue Tabelle erstellen, müssen Sie eine Rolle verwenden, die über die Berechtigung CREATE TABLE für das Schema verfügt, in dem die Tabelle erstellt wird.

Zum Erstellen von FOREIGN KEY-Einschränkungen:

Sie müssen eine Rolle verwenden, die über die OWNERSHIP-Berechtigung für die Tabelle mit dem Fremdschlüssel verfügt.
Sie müssen eine Rolle verwenden, die über die REFERENCES-Berechtigung für die Tabelle der eindeutigen oder primären Schlüssel verfügt.

Die Berechtigung REFERENCES kann Rollen mit den Befehlen GRANT <Berechtigungen> … TO ROLE und REVOKE <Berechtigungen> … FROM ROLE erteilt und entzogen werden:

GRANT REFERENCES ON TABLE <pk_table_name> TO ROLE <role_name>

REVOKE REFERENCES ON TABLE <pk_table_name> FROM ROLE <role_name>

Beispiele für Einschränkungen mit Standardtabellen¶

Beispiele für der Einschränkungen mit Hybridtabellen finden Sie unter CREATE HYBRID TABLE.

Das folgende Beispiel zeigt, wie Sie beim Erstellen einer Tabelle eine einfache NOT NULL-Bedingung und beim Ändern einer Tabelle eine weitere NOT NULL-Bedingung erstellen:

Erstellen Sie eine Tabelle, und erstellen Sie gleichzeitig eine Einschränkung:

CREATE TABLE table1 (col1 INTEGER NOT NULL);

Ändern Sie die Tabelle, um eine Spalte mit einer Einschränkung hinzuzufügen:

ALTER TABLE table1 ADD COLUMN col2 VARCHAR NOT NULL;

Das folgende Beispiel gibt an, dass in der Spalte eindeutige Werte gespeichert werden sollen, macht jedoch deutlich, dass die Einschränkung nicht tatsächlich erzwungen wird. In diesem Beispiel wird auch gezeigt, wie ein Name für die Einschränkung angegeben wird (in diesem Fall „uniq_col3“).

ALTER TABLE table1 
  ADD COLUMN col3 VARCHAR NOT NULL CONSTRAINT uniq_col3 UNIQUE NOT ENFORCED;

Im Folgenden wird eine übergeordnete Tabelle mit einer PRIMARY KEY-Einschränkung und eine weitere Tabelle mit einer FOREIGN KEY-Einschränkung erstellt, die auf dieselben Spalten verweist wie die PRIMARY KEY-Einschränkung der ersten Tabelle.

CREATE TABLE table2 (
  col1 INTEGER NOT NULL,
  col2 INTEGER NOT NULL,
  CONSTRAINT pkey_1 PRIMARY KEY (col1, col2) NOT ENFORCED
);
CREATE TABLE table3 (
  col_a INTEGER NOT NULL,
  col_b INTEGER NOT NULL,
  CONSTRAINT fkey_1 FOREIGN KEY (col_a, col_b) REFERENCES table2 (col1, col2) NOT ENFORCED
);

Im folgenden Beispiel wird eine Inline-CHECK-Einschränkung in einerCREATE TABLE-Anweisung angegeben:

CREATE TABLE test_check_constraint_orders (
  order_id INT,
  quantity INT CHECK (quantity > 0),
  price NUMBER(10, 2));

Dies CHECK-Einschränkung schlägt für die folgende DML-Operationen fehl, da die Menge ein negativer Wert oder Null ist:

INSERT INTO test_check_constraint_orders (order_id, quantity, price)
  VALUES (101, -5, 25.35);

UPDATE test_CHECK_constraint_orders
  SET quantity = 0
  WHERE order_id = 101;

Das folgende Beispiel gibt eine Out-of-Line-CHECK-Einschränkung für mehrere Spalten an:

CREATE TABLE test_check_constraint_max_orders (
  order_id INT,
  quantity INT,
  price NUMBER(10, 2),
  max_price NUMBER(10, 2),
  CONSTRAINT chk_price_max CHECK (price < max_price));

Die CHECK-Einschränkung stellt sicher, dass der Preis den Höchstpreis nicht überschreitet.

Im folgenden Beispiel wird eine Inline-CHECK-Einschränkung in einer CTAS-Anweisung angegeben:

CREATE TABLE high_value_products (
  product_id INT,
  product_name VARCHAR(100),
  list_price NUMBER(10, 2),
  CONSTRAINT high_price CHECK (list_price > 100)
  )
  AS SELECT product_id,
            product_name,
            list_price
  FROM products
  WHERE list_price > 100;

Die CHECK-Einschränkung stellt sicher, dass die neue high_value_products-Tabelle nur Artikel enthält, die als hochpreisig gelten.