Jak dodać kolumnę z ograniczeniem klucza obcego do tabeli, która już istnieje?

Mam następujące tabele,

CREATE TABLE users (id int PRIMARY KEY);

-- already exists with data
CREATE TABLE message ();

Jak zmienić messagestabelę tak, aby

1. senderdodawana jest do niej nowa kolumna o nazwie
2. gdzie senderjest klucz obcy odnoszący się do userstabeli

To nie zadziałało

# ALTER TABLE message ADD FOREIGN KEY (sender) REFERENCES users;
ERROR:  column "sender" referenced in foreign key constraint does not exist

Czy to stwierdzenie również nie tworzy kolumny?

Co jest stosunkowo łatwe - wystarczy dodać kolejny krok.

FOREIGN KEYKolumna musi istnieć , aby uczynić go FK. Wykonałem następujące czynności ( stąd i dokumentacja ):

CREATE TABLE x(t INT PRIMARY KEY);

CREATE TABLE y(s INT);

ALTER TABLE y ADD COLUMN z INT;    

ALTER TABLE y
  ADD CONSTRAINT y_x_fkey FOREIGN KEY (z)
      REFERENCES x (t)
      ON UPDATE CASCADE ON DELETE CASCADE;

Kilka punktów do zapamiętania:

ZAWSZE nadawaj swoim kluczom obcym sensowne nazwy. Powiadomienie o naruszeniu klucza „SYS_C00308108” nie jest zbyt pomocne. Zobacz skrzypce tutaj dla zachowania Oracle w tych okolicznościach, nazwa klucza będzie się różnić między skrzypcami, ale jest to dowolny ciąg znaków rozpoczynający się od SYS _...)

Biorąc pod uwagę twoje oświadczenie:

ALTER TABLE message ADD FOREIGN KEY (sender) REFERENCES users;

Byłoby „miło mieć”, gdyby RDBMS mógł automatycznie utworzyć żądane pole z typem danych pasującym do pola, do którego się odwołuje. Powiedziałbym tylko, że zmiana DDL jest (a przynajmniej powinna być) rzadko używaną operacją, a nie czymś, co chciałbyś robić regularnie. Ryzykuje również dodanie do już dość znacznej dokumentacji.

Przynajmniej PostgreSQL próbuje zrobić coś rozsądnego - łączy nazwę tabeli, nazwę FOREIGN KEYpola, _fkeya nawet dodaje, DETAIL: Key (sender_id)=(56) is not present in table "user_".aby dać coś, co może mieć sens dla człowieka - patrz skrzypce tutaj .

Nie jestem pewien, dlaczego wszyscy mówią ci, że musisz to zrobić w dwóch krokach. W rzeczywistości nie . Próbowałeś dodać, FOREIGN KEYktóry zakłada, że ​​z założenia kolumna jest i zgłasza ten błąd, jeśli kolumny nie ma. Jeśli dodasz COLUMN, możesz jawnie zmienić go FOREIGN KEYna tworzenie za pomocą REFERENCES,

ALTER TABLE message
  ADD COLUMN sender INT
  REFERENCES users;  -- or REFERENCES table(unique_column)

Będzie działać dobrze. Możesz zobaczyć składnię ALTER TABLEtutaj,

ALTER TABLE [ IF EXISTS ] [ ONLY ] name [ * ]
action [, ... ]

Z „akcji” jako,

ADD [ COLUMN ] [ IF NOT EXISTS ] column_name data_type [ COLLATE collation ] [ column_constraint [ ... ] ]

Te przykłady są nawet w dokumentach,

ALTER TABLE distributors
  ADD CONSTRAINT distfk
  FOREIGN KEY (address)
  REFERENCES addresses (address);

ALTER TABLE distributors
  ADD CONSTRAINT distfk
  FOREIGN KEY (address)
  REFERENCES addresses (address)
  NOT VALID;

Ale to wszystko nie jest potrzebne, ponieważ możemy polegać na autonamingach i rozwiązywaniu klucza podstawowego (jeśli podana jest tylko nazwa tabeli, to odwołujesz się do klucza podstawowego).

PRZYPADEK 1: Jeśli musisz utworzyć klucz obcy podczas tworzenia nowej tabeli

CREATE TABLE table1(
id SERIAL PRIMARY KEY,
column1 varchar(n) NOT NULL,
table2_id SMALLINT REFERENCES table2(id)
); 

Powyższe polecenia utworzą tabelę o nazwie „table1” i trzema kolumnami o nazwie „id” (klucz podstawowy), „column1”, „table2_id” (klucz obcy tabeli 1, który odwołuje się do kolumny id tabeli 2).

DATATYPE „serial” sprawi, że kolumna, która używa tego typu danych jako kolumna automatycznie wygenerowana, podczas wstawiania wartości do tabeli nie musisz wcale wspominać o tej kolumnie, lub możesz podać „default” bez cudzysłowów w miejscu wartości.

Kolumna klucza podstawowego jest zawsze dodawana do indeksu tabeli o wartości „tablename_pkey”.

Jeśli klucz obcy zostanie dodany w czasie tworzenia tabeli, OGRANICZENIE zostanie dodane ze wzorcem „(nazwa_tabeli_tabeli) _ (nazwa_obcięcia_domeny) _fkey”.

Dodając klucz obcy, musimy wprowadzić słowo kluczowe „ODNIESIENIA” obok nazwy kolumny, ponieważ chcemy powiedzieć postgresowi, że ta kolumna odwołuje się do tabeli, a następnie obok odniesień musimy podać tabelę w celach informacyjnych, aw nawiasach podać nazwa kolumny przywoływanej tabeli, zwykle klucze obce są podawane jako kolumny klucza podstawowego.

PRZYPADEK 2: Jeśli chcesz mieć klucz obcy do istniejącej tabeli w istniejącej kolumnie

ALTER TABLE table1
ADD CONSTRAINT table1_table2_id_id_fkey
FOREIGN KEY (table2_id) REFERENCES table2(id);

UWAGA: nawiasy kwadratowe „()” po KLUCZU ZAGRANICZNYM i REFERENCJE tabela2 są obowiązkowe, w przeciwnym razie postgres zgłosi błąd.

Znam problem. Nazwy kolumn są różne. Być może w jednej kolumnie znajduje się spacja po nazwie kolumny, więc upewnij się, że nazwy kolumn zostały nazwane dokładnie tak samo.