![]() ![]() Last_login | timestamp without time zone | not null default now() Username | character varying(25) | not null Id | integer | not null default nextval('users_id_seq'::regclass) First we need to add these columns using the ALTER TABLE command. We could start adding these pieces to the users table, resulting in a table with many columns. In this chapter we want to add several more pieces of data to our database. We'll still be working with the familiar library database and users table from previous chapters, but with a blank users table to start with. Follow the directions above in the info box to retrieve starter data for this chapter. With that in mind, we'll start fresh in this chapter. In the chapter on altering a table, we ended up changing our users database in various ways. If the table doesn't exist, PostgreSQL will ignore the command and move on to executing the rest of the backup file. These are included in case that table already exists. ![]() There are some commands in place at the start of the backup file for altering and removing the users table. When running the above command, you may see the following message: ERROR: relation "ers" does not exist. First download the backup file for this chapter from here. The library database can be restored from a backup file. The library database is needed to work through this chapter's content. That way you can step through the statements and debug them step by step./manage.If you don't already have a database named "library", then make sure to run createdb library from the terminal before starting this chapter. For better debugging, you can use the sqlmigrate subcommand which will display the SQL queries instead of directly executing them. If you’re working with raw SQL migrations, you might run into trouble and all you’ll get is some context-free SQL error message. ![]() Obviously, you can do all those steps as raw SQL migrations, but then makemigrations will still nag you about changes in your model, thus you got to use SeparateDatabaseAndState, apply the changes with SQL and then tell Django what the effect is. Swapping out primary keys is too difficult for the automatic Django migration assistant, so you’ll need to get your hands dirty. Now, if you want to gain flexbility and add an internal ID, you can start off with adding a serial column to users:ĪLTER TABLE user DROP CONSTRAINT user_pkey ALTER TABLE user ADD PRIMARY KEY (id) Īt this moment, you can even put the external identifier into a different table, if you wish, thus possibly separating the concerns even better. Thus, all tables that reference any user have those long, external identifiers: user_permissions user_id At the moment you got a user table, which has this external ID as the primary key: user external_user_id (primary key) Let’s assume your application tracks users, which have external identifiers. Create new primary key constraint on base table.Drop primary key constraint from base table.Add new constraint for the new base table ID Update the referencing key values to the new onesĢ.3. Drop the existing constraint (from 1.3.)Ģ.2. Determine the foreign key constraint name (see pg_constraint table)Ģ.1. Identify all tables which reference the (old) ID:ġ.3.Create the new key ID column, make it unique (e.g.But, don’t worry, it’s still possible to change this inside a single transaction with those four-ish steps: Or it happens to others and I have to save the day.īecause PostgreSQL is not the wild west, if you reference a field in a different table, this will be enforced through a constraint, which is fantastic for data consistency, but annoying if you misdesigned your data structure and have to change your foreign keys. if you want to add a second type of external service or when you realize, that those seemingly unique identifiers are not as unique as assumed.Įven though I should know better, this kind of mistake happens to me from time to time anyway. This may seem reasonable at the beginning, but may soon cause trouble, e.g. Without a dedicated mapping table, you will carry such external identifiers through all tables. In such cases, an external ID should be stored just once in a mapping table and from there on only referenced by an internal (e.g. Applications often need to work with external IDs, e.g. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |