For MySQL, there is none: MySQL Feature Request. Allowing this is arguably a really bad idea, anyway: IF EXISTS indicates that you’re running destructive operations on a database with (to you) unknown structure. There may be situations where this is acceptable for quick-and-dirty local work, but if you’re tempted to run such a statement against … Read more
I know most databases have restrictions, but Postgres doesn’t. You can run any number table creations, column changes and index changes in a transaction, and the changes aren’t visible to other users unit COMMIT succeeds. That’s how databases should be! 🙂 As for SQL Server you can run DDL inside of a transaction, but SQL … Read more
Your understanding is correct. You would do this in many cases. One example is in a relationship like OrderHeader and OrderDetail. The PK in OrderHeader might be OrderNumber. The PK in OrderDetail might be OrderNumber AND LineNumber. If it was either of those two, it would not be unique, but the combination of the two … Read more
You can’t. Although the SQL-92 syntax to add a foreign key to your table would be as follows: ALTER TABLE child ADD CONSTRAINT fk_child_parent FOREIGN KEY (parent_id) REFERENCES parent(id); SQLite doesn’t support the ADD CONSTRAINT variant of the ALTER TABLE command (sqlite.org: SQL Features That SQLite Does Not Implement). Therefore, the only way to add … Read more
The parentheses are misplaced: SELECT setval(‘payments_id_seq’, 21, true); — next value will be 22 Otherwise you’re calling setval with a single argument, while it requires two or three. This is the same as SELECT setval(‘payments_id_seq’, 21)
Try this ALTER TABLE users ADD COLUMN `count` SMALLINT(6) NOT NULL AFTER `lastname`, ADD COLUMN `log` VARCHAR(12) NOT NULL AFTER `count`, ADD COLUMN `status` INT(10) UNSIGNED NOT NULL AFTER `log`; check the syntax
According to the documentation, it’s CREATE TABLE something ( column1, column2, column3, PRIMARY KEY (column1, column2) );
Use the following in a stored procedure: IF NOT EXISTS( SELECT NULL FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name=”tablename” AND table_schema=”db_name” AND column_name=”columnname”) THEN ALTER TABLE `TableName` ADD `ColumnName` int(1) NOT NULL default ‘0’; END IF;
Restrictions You can ask the system catalog pg_database – accessible from any database in the same database cluster. The tricky part is that CREATE DATABASE can only be executed as a single statement. The manual: CREATE DATABASE cannot be executed inside a transaction block. So it cannot be run directly inside a function or DO … Read more
Update: SQLite 2021-03-12 (3.35.0) now supports DROP COLUMN. The FAQ on the website is still outdated. 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 … Read more