For example you could do it like this: PRAGMA foreign_keys = OFF Īnd it’s true. When looking at the previous example, you might be thinking that there’s a more efficient way to do it. I’ve put it there solely to change the way my terminal outputs the result (so that you don’t have to scroll sideways to view the result). mode line) has got nothing to do with creating a foreign key. Note that the first line of my command (. This time we can see the details of the foreign key constraint. Now let’s check the table for foreign key constraints again.mode line If you need to reconstruct any indexes, triggers, or views, do that after the ALTER TABLE statement that renames the table (just prior to COMMIT). PRAGMA foreign_keys = OFF įOREIGN KEY(TypeId) REFERENCES Types(TypeId)
#Sqlitestudio alternatie code#
The following code adds a foreign key to our table by creating a new table with a foreign key constraint, transferring the data to that table, dropping the original table, then renaming the new table to the name of the original table. (That’s blank because there are no foreign key constraints on this table.) We can verify that there are no foreign keys by running the following command: PRAGMA foreign_key_list(Pets) Notice that I didn’t create a foreign key. Two tables, because one ( Types) will have the primary key and the other ( Pets) will have the foreign key. CREATE TABLE Types(Īctually, here I created two tables and populated them with data. Original Table Without Foreign Keyįirst, let’s create a table without a foreign key and populate it with data. To make it somewhat realistic, we’ll make sure the table already contains data. The SQLite documentation recommends a 12 step process for making schema changes to a table.įor the purposes of this article, we’ll just deal with adding a foreign key. There’s more than one way to do this, but there is a recommended way. Therefore, the only way you can “add” a foreign key to an existing table in SQLite is to create a new table with a foreign key, then transfer the data to the new table. In other words, you can’t use ALTER TABLE to add a foreign key to an existing table like you can in other database management systems. The only things you can do with ALTER TABLE in SQLite is rename a table, rename a column within a table, or add a new column to an existing table. SQLite supports a very limited subset of the ALTER TABLE statement.
0 kommentar(er)
