Czy kolumny tabeli z kluczem obcym mogą mieć wartość NULL?


235

Mam tabelę, która ma kilka kolumn identyfikatora do innych tabel.

Chcę, aby klucz obcy wymusił integralność tylko wtedy, gdy wstawię tam dane. Jeśli zrobię aktualizację w późniejszym czasie, aby zapełnić tę kolumnę, powinien również sprawdzić ograniczenie.

(Prawdopodobnie zależy to od serwera bazy danych, używam typu tabeli MySQL i InnoDB)

Uważam, że jest to uzasadnione oczekiwanie, ale poprawcie mnie, jeśli się mylę.


6
Nie wiem o MySQL, ale MS SQL Server pozwala, aby klucze obce były zerowalne z semantyką, którą chcesz. Oczekuję, że jest to standardowe zachowanie.
Jeffrey L Whitledge

1
klucz obcy, domyślnie nie może być pusty w mySQL, przyczyna jest prosta, jeśli coś odwołujesz się i pozwalasz mu mieć wartość null, tracisz integralność danych. podczas tworzenia zestawu tabel należy pozwolić, aby wartość NULL NIE, a następnie zastosować ograniczenie klucza obcego. Nie można ustawić wartości NULL podczas aktualizacji, powinien on wysłać błąd, ale możesz (musisz) po prostu nie zaktualizować tej kolumny i zaktualizować tylko pola, które musisz zmienić.
JoelBonetR

Odpowiedzi:


245

Tak, możesz wymusić ograniczenie tylko wtedy, gdy wartość nie jest równa NULL. Można to łatwo przetestować za pomocą następującego przykładu:

CREATE DATABASE t;
USE t;

CREATE TABLE parent (id INT NOT NULL,
                     PRIMARY KEY (id)
) ENGINE=INNODB;

CREATE TABLE child (id INT NULL, 
                    parent_id INT NULL,
                    FOREIGN KEY (parent_id) REFERENCES parent(id)
) ENGINE=INNODB;


INSERT INTO child (id, parent_id) VALUES (1, NULL);
-- Query OK, 1 row affected (0.01 sec)


INSERT INTO child (id, parent_id) VALUES (2, 1);

-- ERROR 1452 (23000): Cannot add or update a child row: a foreign key 
-- constraint fails (`t/child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY
-- (`parent_id`) REFERENCES `parent` (`id`))

Pierwsza wstawka przejdzie, ponieważ wstawiamy NULL w parent_id. Druga wstawka kończy się niepowodzeniem z powodu ograniczenia klucza obcego, ponieważ próbowaliśmy wstawić wartość, która nie istnieje w parenttabeli.


16
Tabelę nadrzędną można również zadeklarować za pomocą identyfikatora INT NOT NULL.
Czy

@CJDennis Jeśli to zrobisz, aby tylko jeden wiersz mógł mieć zerowy identyfikator, można go użyć jako wartości zastępczych dla innych wierszy. (Chociaż może to działać lepiej dla DB, jeśli użyjesz więcej kolumn.) Domyślne ograniczenie wydaje się problemem, jeśli chcesz później dowiedzieć się, czy wartość została pierwotnie ustawiona jako „domyślna” (przy użyciu wartości null), czy ustawiona na wartość wartość, która jest taka sama jak „domyślna”. Mając wiersz o zerowym identyfikatorze, możesz wyraźnie wskazać, że tego wiersza nie należy używać jako zwykłego wiersza, i można użyć tego wiersza jako sposobu zapewnienia pewnego rodzaju dynamicznej wartości domyślnej dla innych wierszy.
Ouroborus

1
myślę, że parent_id INT NULLczęść jest (mówiona) równaparent_id int default null

Dodatkowa uwaga dla użytkowników Java, jeśli użyjesz ibatis lub innej ORM i użyjesz operacji podstawowej intzamiast Integerw członkach klasy, domyślna wartość nigdy nie będzie wynosić zero, ale będzie równa 0 i nie spełnisz warunku .
Jim Ford

32

Odkryłem, że podczas wstawiania wartości kolumny zerowej musiały być wyraźnie zadeklarowane jako NULL, w przeciwnym razie dostałbym błąd naruszenia ograniczenia (w przeciwieństwie do pustego ciągu).


8
Czy nie można ustawić domyślnej wartości NULL w kolumnie, aby to umożliwić?
Kevin Coulombe

Tak, w większości języków wartość NULL różni się od pustego ciągu. Być może subtelny na początku, ale niezbędny do zapamiętania.
Gary

Hej, Backslider, mówisz „(w przeciwieństwie do pustego ciągu)”, ale nie sądzę, że miałeś na myśli, że wstawisz wartość pustego ciągu, ale raczej, że nie określisz wartości dla wartości w wszystko? tzn. nawet nie wspominasz o kolumnie w swoim INSERT INTO {table} {list_of_columns}? Bo to dla mnie prawda; pominięcie wzmianki o kolumnie powoduje błąd, ale włączenie i jawne ustawienie wartości NULL naprawia błąd. Jeśli mam rację, myślę, że komentarz @ Gary'ego nie ma zastosowania (ponieważ nie miałeś na myśli pustego ciągu), ale @Kevin Coulombe może być pomocny ...
The Red Pea

Tak, sugestia @ KevinCoulombe działa, opisałem, jak to osiągnąć za pomocą skryptów migracji Entity Framework Core, tutaj
The Red Pea

Należy zauważyć, że uzasadnienie jawności podczas aktualizacji rekordu zawierającego NULL klucze obce dotyczy tylko typów ciągów (varchar itp.), Ponieważ w przeciwnym razie domyślnie można przekazać pusty ciąg. Tak jest w przypadku MySQL i powoduje błąd integralności podczas aktualizacji.
CodeMantle

4

Tak, zadziała tak, jak tego oczekujesz. Niestety wydaje mi się, że mam problem ze znalezieniem wyraźnego stwierdzenia tego w podręczniku MySQL .

Klucze obce oznaczają, że wartość musi istnieć w drugiej tabeli. NULL odnosi się do braku wartości, więc kiedy ustawisz kolumnę na NULL, nie ma sensu próbować wymuszać na tym ograniczeń.


Z założenia Klucz obcy musi odnosić się do jakiegoś klucza (Podstawowego), który nie ma wartości NULL, ale podczas fazy programowania, kiedy musimy najpierw wstawić wiele danych do tabeli potomnej, do której nie wiemy, do kogo się odwoła (tabela nadrzędna) . Właśnie dlatego dozwolona jest wartość NULL. Przy produkcji NULL będzie przebiegiem projektowym, co można z grubsza powiedzieć.
vimal krishna

2

Powyższe działa, ale nie działa. Zwróć uwagę na KASKAD ON DELETE

CREATE DATABASE t;
USE t;

CREATE TABLE parent (id INT NOT NULL,
                 PRIMARY KEY (id)
) ENGINE=INNODB;

CREATE TABLE child (id INT NULL, 
                parent_id INT NULL,
                FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE CASCADE

) ENGINE=INNODB;


INSERT INTO child (id, parent_id) VALUES (1, NULL);
-- Query OK, 1 row affected (0.01 sec)

4
Co rozumiesz przez „powyższe”? Pamiętaj, że jeśli masz na myśli inną odpowiedź, kolejność może się zmienić.
d219

2

Tak, wartość może wynosić NULL, ale musisz być jawny. Zetknąłem się już z tą samą sytuacją i łatwo zapomnieć DLACZEGO to się dzieje, więc trochę trzeba pamiętać, co trzeba zrobić.

Jeśli przesłane dane zostaną rzutowane lub zinterpretowane jako pusty ciąg, nie powiedzie się. Jednak poprzez bezpośrednie ustawienie wartości NULL podczas WSTAWIANIA lub AKTUALIZACJI, możesz zacząć.

Ale to jest zabawa z programowaniem, prawda? Tworzenie własnych problemów, a następnie ich rozwiązywanie! Twoje zdrowie!


1

Innym sposobem obejścia tego byłoby wstawienie elementu DEFAULT do drugiej tabeli. Na przykład każde odniesienie do uuid = 00000000-0000-0000-0000-000000000000 w drugiej tabeli wskazuje na brak działania. Musisz także ustawić wszystkie wartości tego identyfikatora na „neutralne”, np. 0, pusty ciąg, null, aby nie wpływać na logikę kodu.


2
To nie to samo. Domyślna lub „neutralna” wartość nie jest taka sama jak NULL, brak wartości. Bez omawiania zalet wartości domyślnej nad wartością NULL, frazowanie jest bardzo małe. „Innym sposobem na obejście tego byłoby wstawienie pustego elementu do drugiej tabeli” powinien powiedzieć coś więcej „Innym sposobem na obejście tego byłoby wstawienie DOMYŚLNEGO elementu do drugiej tabeli”
blindguy

0

Utknąłem również w tej sprawie. Ale rozwiązałem po prostu definiując klucz obcy jako unsigned integer. Znajdź poniższy przykład

CREATE TABLE parent (
   id int(10) UNSIGNED NOT NULL,
    PRIMARY KEY (id)
) ENGINE=INNODB;

CREATE TABLE child (
    id int(10) UNSIGNED NOT NULL,
    parent_id int(10) UNSIGNED DEFAULT NULL,
    FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE CASCADE
) ENGINE=INNODB;
Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.