ALTER TABLE DROP COLUMN does just only disabling columns in system tables. It is very fast, but it doesn’t remove data from heap files. You have to do VACUUM FULL later to compact allocated file space. So ALTER TABLE DROP COLUMN is very fast. And to compact files, you have to call the slower (with … Read more
There can only be one primary key per table – as indicated by the word “primary”. You can have additional UNIQUE columns like: CREATE TABLE test( sl_no int PRIMARY KEY, — NOT NULL due to PK emp_id int UNIQUE NOT NULL, emp_name text, emp_addr text ); Columns that are (part of) the PRIMARY KEY are … Read more
Update: SQLite 2021-03-12 (3.35.0) now supports DROP COLUMN. From: http://www.sqlite.org/faq.html: (11) How do I add or delete columns from an existing table in SQLite. SQLite has limited ALTER TABLE support that you can use to add a column to the end of a table or to change the name of a table. If you want … Read more
To use AUTO_INCREMENT you need to deifne column as INT or floating-point types, not CHAR. AUTO_INCREMENT use only unsigned value, so it’s good to use UNSIGNED as well; CREATE TABLE discussion_topics ( topic_id INT NOT NULL unsigned AUTO_INCREMENT, project_id char(36) NOT NULL, topic_subject VARCHAR(255) NOT NULL, topic_content TEXT default NULL, date_created DATETIME NOT NULL, date_last_post … Read more
Use pg_dump with this options: pg_dump -U user_name -h host database -s -t table_or_view_names -f table_or_view_names.sql Description: -s or –schema-only : Dump only ddl / the object definitions (schema), without data. -t or –table Dump : Dump only tables (or views or sequences) matching table Examples: — dump each ddl table elon build. $ pg_dump … Read more
Because the PRIMARY KEY makes the included column(s) NOT NULL automatically. I quote the manual here: The primary key constraint specifies that a column or columns of a table can contain only unique (non-duplicate), nonnull values. Technically, PRIMARY KEY is merely a combination of UNIQUE and NOT NULL. Bold emphasis mine. I ran a test … Read more
The ON DELETE CASCADE and ON DELETE RESTRICT are the foreign key properties and you set them when you create the relationship between two tables. If you set the relationship to be ON DELETE CASCADE, when you run a DELETE statement on a “parent” table, it will automatically DELETE all the corresponding rows from the … Read more
No. While other actions can be combined, that’s not possible with RENAME. The manual: All the forms of ALTER TABLE that act on a single table, except RENAME, SET SCHEMA, ATTACH PARTITION, and DETACH PARTITION can be combined into a list of multiple alterations to be applied together. Since RENAME is a tiny operation on … Read more
This script will not run successfully unless the column already exists, which is exactly when you don’t need it. SQL Scripts have to be parsed before they can be executed. If the column doesn’t exist at the time the script is parsed, then the parsing will fail. It doesn’t matter that your scripts creates the … Read more
You can do it using the mysqldump command line utility: mysqldump -d -u <username> -p -h <hostname> <dbname> The -d option means “without data”.