sql - How can I use ALTER TABLE to add a column that is NOT NULL and REFERENCES? - Stack Overflow

admin管理员组

文章数量:1417577

I want to add a column of type TEXT, NOT NULL, with a DEFAULT value of empty string, which should reference a column in a different table. I can add foreign key references when creating a table.

But it's not allowing me to add a column with a foreign key reference:

ALTER TABLE Event ADD TagId TEXT NOT NULL DEFAULT '';

ALTER TABLE Event
ADD CONSTRAINT FK_TagId FOREIGN KEY ("TagId") REFERENCES "Tag" ("TagId") ON DELETE RESTRICT;

I want to add a column of type TEXT, NOT NULL, with a DEFAULT value of empty string, which should reference a column in a different table. I can add foreign key references when creating a table.

But it's not allowing me to add a column with a foreign key reference:

ALTER TABLE Event ADD TagId TEXT NOT NULL DEFAULT '';

ALTER TABLE Event
ADD CONSTRAINT FK_TagId FOREIGN KEY ("TagId") REFERENCES "Tag" ("TagId") ON DELETE RESTRICT;

• sql
• sqlite
• foreign-keys
• constraints
• alter-table

Share Improve this question Follow edited Feb 1 at 4:15 user4157124 2,9961414 gold badges3131 silver badges4646 bronze badges asked Jan 31 at 10:03 MartinMartin 2177 bronze badges 2

• Are you using the ALTER TABLE. command? Please edit your question and show the command(s) you are using and the results or error messages that you see. – OldBoy Commented Jan 31 at 10:22
• 1 Even if you use the supported ALTER TABLE syntax, there's this to consider: If foreign key constraints are enabled and a column with a REFERENCES clause is added, the column must have a default value of NULL – Shawn Commented Jan 31 at 13:41

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

Following the comments from @user4157124 I have removed my original answer and offer the following script, which I hope is closer to the actual details referred to in the question:

DROP TABLE IF EXISTS Event;
DROP TABLE IF EXISTS Tag;

CREATE TABLE Event (Name TEXT, Id INTEGER PRIMARY KEY AUTOINCREMENT);

INSERT INTO Event(Name) VALUES("EventA");
INSERT INTO Event(Name) VALUES("EventB");

CREATE TABLE Tag (Name TEXT, TagId TEXT);

INSERT INTO Tag VALUES ("TagA", "TagA");
INSERT INTO Tag VALUES ("TagB", "TagB");

SELECT * FROM Event;

ALTER TABLE Event ADD COLUMN TagId TEXT NOT NULL DEFAULT '' REFERENCES Tag(TagId);

UPDATE Event Set TagId = "TagA" WHERE Name = "EventA";
UPDATE Event Set TagId = "TagB" WHERE Name = "EventB";

SELECT * FROM Event;

The script can be run from the SQLite command prompt via the following commands:

sqlite3 test.db
.headers on
.mode columns
.read script.sql

本文标签： sqlHow can I use ALTER TABLE to add a column that is NOT NULL and REFERENCESStack Overflow