SQL CREATE ALTER DROP TABLE: Difference between revisions

From NovaOrdis Knowledge Base
Jump to navigation Jump to search
 
(26 intermediate revisions by the same user not shown)
Line 2: Line 2:
* [[SQL#CADT|SQL]]
* [[SQL#CADT|SQL]]
* [[SQL_Data_Types#Overview|SQL Data Types]]
* [[SQL_Data_Types#Overview|SQL Data Types]]
=Overview=


=<tt>CREATE</tt>=
=<tt>CREATE</tt>=
<syntaxhighlight lang='sql'>
<syntaxhighlight lang='sql'>
CREATE TABLE person  
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] person  
( id smallint,  
( id smallint,  
   name varchar(30),  
   name varchar(30) NOT NULL,  
   CONSTRAINT pk_person PRIMARY KEY (id)
   CONSTRAINT person_pkey PRIMARY KEY (id)
);
);
</syntaxhighlight>
</syntaxhighlight>
:{| class="wikitable" style="text-align: left;"
:{| class="wikitable" style="text-align: left;"
|-
|-
| [[PostgreSQL_DDL_Operations#Create_a_Table|PostgreSQL]] || MariaDB
| [[PostgreSQL_DDL_Operations#Create_a_Table|PostgreSQL]] || [[MySQL_DDL_Operations#Create_Table|MySQL]]
|-
|-
|}
|}


If the <code>TEMPORARY</code> keyword is used, the created table will be [[SQL#Temporary_Table|temporary]] instead of [[SQL#Permanent_Table|permanent]].


=<tt>ALTER</tt>=
<syntaxhighlight lang='sql'>
ALTER TABLE person [...]
</syntaxhighlight>
==Add/Remove a Column==
<syntaxhighlight lang='sql'>
ALTER TABLE person ADD COLUMN birthday DATE;
</syntaxhighlight>
<syntaxhighlight lang='sql'>
ALTER TABLE person DROP COLUMN birthday;
</syntaxhighlight>
:{| class="wikitable" style="text-align: left;"
|-
| [[PostgreSQL_DDL_Operations#Add/Remove_a_Column|PostgreSQL]] || [[MySQL_DDL_Operations#Alter_Table|MySQL]]
|-
|}
==Add/Remove a Constraint==
===<tt>NOT NULL</tt>===
<syntaxhighlight lang='sql'>
ALTER TABLE sometable ALTER COLUMN somecolumn SET NOT NULL;
</syntaxhighlight>
<syntaxhighlight lang='sql'>
ALTER TABLE sometable ALTER COLUMN somecolumn DROP NOT NULL;
</syntaxhighlight>
:{| class="wikitable" style="text-align: left;"
|-
| [[PostgreSQL_DDL_Operations#NOT_NULL|PostgreSQL]] || [[MySQL_DDL_Operations|MySQL]]
|-
|}
===Primary Key===
<syntaxhighlight lang='sql'>
ALTER TABLE sometable ADD [CONSTRAINT sometable_pkey] PRIMARY KEY (somecolumn);
</syntaxhighlight>
If the name of the primary key constraint is not provided with the optional <code>CONSTRAINT ...</code>, the command will generate the name of the primary key constraint to match the <code><table_name>_pkey</code> pattern.
<syntaxhighlight lang='sql'>
ALTER TABLE sometable DROP CONSTRAINT [IF EXISTS] constraint_name [RESTRICT|CASCADE];
</syntaxhighlight>
:{| class="wikitable" style="text-align: left;"
|-
| [[PostgreSQL_DDL_Operations#Primary_Key|PostgreSQL]] || [[MySQL_DDL_Operations|MySQL]]
|-
|}


<font color=darkkhaki>
===Foreign Key===
* Refactor [[MySQL_DDL_Operations|MySQL DDL Operations]] and surface in "Standard SQL" everything that can be handled with standard SQL.
<syntaxhighlight lang='sql'>
</font>
ALTER TABLE sometable ADD [CONSTRAINT sometable_someothertable_id_fkey] FOREIGN KEY (someothertable_id) REFERENCES someothertable(id);
</syntaxhighlight>
If the name of the constraint is not provided with <code>CONSTRAINT ...</code>, the command will generate the name of the foreign key constraint to match the <code><this_table_name>_<the_other_table_name>_<column_name>_fkey</code> pattern.
<syntaxhighlight lang='sql'>
ALTER TABLE sometable DROP CONSTRAINT [IF EXISTS] constraint_name [RESTRICT|CASCADE];
</syntaxhighlight>
:{| class="wikitable" style="text-align: left;"
|-
| [[PostgreSQL_DDL_Operations#Foregin_Key|PostgreSQL]] || [[MySQL_DDL_Operations|MySQL]]
|-
|}


=<tt>ALTER</tt>=
=<tt>DROP</tt>=
=<tt>DROP</tt>=
<syntaxhighlight lang='sql'>
DROP TABLE person [...]
</syntaxhighlight>
:{| class="wikitable" style="text-align: left;"
|-
| [[PostgreSQL_DDL_Operations#Drop_a_Table|PostgreSQL]] || [[MySQL_DDL_Operations#Drop_Table|MySQL]]
|-
|}
=Features=
==<tt>AUTO_INCREMENT</tt>==
{{Internal|SQL_AUTO_INCREMENT#Overview|<tt>AUTO_INCREMENT</tt>}}

Latest revision as of 14:32, 31 May 2024

Internal

Overview

CREATE

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] person 
( id smallint, 
  name varchar(30) NOT NULL, 
  CONSTRAINT person_pkey PRIMARY KEY (id)
);
PostgreSQL MySQL

If the TEMPORARY keyword is used, the created table will be temporary instead of permanent.

ALTER

ALTER TABLE person [...]

Add/Remove a Column

ALTER TABLE person ADD COLUMN birthday DATE;

ALTER TABLE person DROP COLUMN birthday;
PostgreSQL MySQL

Add/Remove a Constraint

NOT NULL

ALTER TABLE sometable ALTER COLUMN somecolumn SET NOT NULL;

ALTER TABLE sometable ALTER COLUMN somecolumn DROP NOT NULL;
PostgreSQL MySQL

Primary Key

ALTER TABLE sometable ADD [CONSTRAINT sometable_pkey] PRIMARY KEY (somecolumn);

If the name of the primary key constraint is not provided with the optional CONSTRAINT ..., the command will generate the name of the primary key constraint to match the <table_name>_pkey pattern. 

ALTER TABLE sometable DROP CONSTRAINT [IF EXISTS] constraint_name [RESTRICT|CASCADE];
PostgreSQL MySQL

Foreign Key

ALTER TABLE sometable ADD [CONSTRAINT sometable_someothertable_id_fkey] FOREIGN KEY (someothertable_id) REFERENCES someothertable(id);

If the name of the constraint is not provided with CONSTRAINT ..., the command will generate the name of the foreign key constraint to match the <this_table_name>_<the_other_table_name>_<column_name>_fkey pattern. 

ALTER TABLE sometable DROP CONSTRAINT [IF EXISTS] constraint_name [RESTRICT|CASCADE];
PostgreSQL MySQL

DROP

DROP TABLE person [...]
PostgreSQL MySQL

Features

AUTO_INCREMENT

AUTO_INCREMENT
Retrieved from "https://kb.novaordis.com/index.php?title=SQL_CREATE_ALTER_DROP_TABLE&oldid=111114"