SQL CREATE ALTER DROP TABLE: Difference between revisions

From NovaOrdis Knowledge Base
Jump to navigation Jump to search
Line 51: Line 51:
===Primary Key===
===Primary Key===
<syntaxhighlight lang='sql'>
<syntaxhighlight lang='sql'>
ALTER TABLE sometable ADD PRIMARY KEY (somecolumn) sometable_pkey;
ALTER TABLE sometable ADD PRIMARY KEY (somecolumn);
</syntaxhighlight>
</syntaxhighlight>
The name of the primary key constraint is optional, and if not specified, it will be generated according to the <code><table_name>_pkey</code> pattern.
The command will generate the name of the primary key constraint to match the <code><table_name>_pkey</code> pattern.
<syntaxhighlight lang='sql'>
<syntaxhighlight lang='sql'>
ALTER TABLE sometable DROP CONSTRAINT [IF EXISTS] constraint_name [RESTRICT|CASCADE];
ALTER TABLE sometable DROP CONSTRAINT [IF EXISTS] constraint_name [RESTRICT|CASCADE];

Revision as of 19:40, 23 May 2024

Internal

Overview

CREATE

CREATE [TEMPORARY] TABLE 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 PRIMARY KEY (somecolumn);

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

DROP

DROP TABLE person [...]
PostgreSQL MySQL

Features

AUTO_INCREMENT

AUTO_INCREMENT