SQL Constraints: Difference between revisions

From NovaOrdis Knowledge Base
Jump to navigation Jump to search
 
(10 intermediate revisions by the same user not shown)
Line 1: Line 1:
=Internal=
=Internal=
* [[SQL#Constraints|SQL]]
* [[SQL#Constraints|SQL]]
* [[SQL_CREATE_ALTER_DROP_TABLE#Overview|<tt>CREATE</tt> | <tt>ALTER</tt> | <tt>DROP</tt> <tt>TABLE</tt>]]
=Overview=
=Overview=


=<tt>NOT NULL</tt> Constraint=
=<tt>NOT NULL</tt> Constraint=
<syntaxhighlight lang='sql'>
CREATE TABLE ...
( ...
  somecolumn varchar(30) NOT NULL,
  ...
);
</syntaxhighlight>
Also see: {{Internal|SQL#SQL_NULL|<tt>NULL</tt>}}


=Primary Key Constraint=
=Primary Key Constraint=
{{Internal|Relational_Databases#Primary_Key|Primary Key}}
<syntaxhighlight lang='sql'>
<syntaxhighlight lang='sql'>
CREATE TABLE ...
CREATE TABLE ...
Line 12: Line 24:
);
);
</syntaxhighlight>
</syntaxhighlight>
The primary key constraint can also be added after table creation with <code>[[SQL_CREATE_ALTER_DROP_TABLE#Overview|ALTER TABLE]]</code>.
=Foreign Key Constraint=
A foreign key constraint can be created to verify that the values in one table exist in another table.
{{Internal|Relational_Databases#Foreign_Key|Foreign Key}}
<syntaxhighlight lang='sql'>
CREATE TABLE ...
( ...
  CONSTRAINT fk_someconstraint FOREIGN KEY (somecolumn)
);
</syntaxhighlight>
The foreign key constraint can also be added after table creation with <code>[[SQL_CREATE_ALTER_DROP_TABLE#Overview|ALTER TABLE]]</code>.
=Check Constraint=
=Check Constraint=
<syntaxhighlight lang='sql'>
<syntaxhighlight lang='sql'>

Latest revision as of 22:34, 23 May 2024

Internal

Overview

NOT NULL Constraint

CREATE TABLE ...
( ...
  somecolumn varchar(30) NOT NULL, 
  ...
);

Also see:

NULL

Primary Key Constraint

Primary Key
CREATE TABLE ...
( ...
  CONSTRAINT pk_someconstraint PRIMARY KEY (somecolumn)
);

The primary key constraint can also be added after table creation with ALTER TABLE.

Foreign Key Constraint

A foreign key constraint can be created to verify that the values in one table exist in another table.

Foreign Key
CREATE TABLE ...
( ...
  CONSTRAINT fk_someconstraint FOREIGN KEY (somecolumn)
);

The foreign key constraint can also be added after table creation with ALTER TABLE.

Check Constraint

CREATE TABLE ...
( ...
  eye_color CHAR(2) CHECK (eye_color IN ('BR', 'BL', 'GR')),
  ...
);