SQL CREATE ALTER DROP TABLE: Difference between revisions
Jump to navigation
Jump to search
(→CREATE) |
|||
| (4 intermediate revisions by the same user not shown) | |||
| Line 6: | Line 6: | ||
=<tt>CREATE</tt>= | =<tt>CREATE</tt>= | ||
<syntaxhighlight lang='sql'> | <syntaxhighlight lang='sql'> | ||
CREATE [TEMPORARY] TABLE person | CREATE [TEMPORARY] TABLE [IF NOT EXISTS] person | ||
( id smallint, | ( id smallint, | ||
name varchar(30) NOT NULL, | name varchar(30) NOT NULL, | ||
| Line 51: | Line 51: | ||
===Primary Key=== | ===Primary Key=== | ||
<syntaxhighlight lang='sql'> | <syntaxhighlight lang='sql'> | ||
ALTER TABLE sometable ADD PRIMARY KEY (somecolumn); | ALTER TABLE sometable ADD [CONSTRAINT sometable_pkey] PRIMARY KEY (somecolumn); | ||
</syntaxhighlight> | </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'> | <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]; | ||
| Line 60: | Line 60: | ||
|- | |- | ||
| [[PostgreSQL_DDL_Operations#Primary_Key|PostgreSQL]] || [[MySQL_DDL_Operations|MySQL]] | | [[PostgreSQL_DDL_Operations#Primary_Key|PostgreSQL]] || [[MySQL_DDL_Operations|MySQL]] | ||
|- | |||
|} | |||
===Foreign Key=== | |||
<syntaxhighlight lang='sql'> | |||
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]] | |||
|- | |- | ||
|} | |} | ||
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)
);
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;
Add/Remove a Constraint
NOT NULL
ALTER TABLE sometable ALTER COLUMN somecolumn SET NOT NULL;
ALTER TABLE sometable ALTER COLUMN somecolumn DROP NOT NULL;
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];
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];
DROP
DROP TABLE person [...]