Old Constraints
Database constraints are user-defined structures that let you restrict the behaviors of columns. There are five principal types of constraints covered in this web page.
You can create these constraints when you define a table with a SQL CREATE
statement. A constraint defined on the same line as its column is called an inline constraint, while a constraint on its own line in a CREATE
statement is an out-of-line constraint. Out-of-line constraints must reference (or point to) the column that they constrain. You can also add, modify, or drop these constraints with an ALTER
statement after you’ve created the table.
While all databases support the set of possible constraints. They sometimes provide system specific alternatives. The following chart describes the types of restrictions you can place on columns and tables by using database-level constraints and triggers.
A column-level constraint may restrict the minimum cardinality of a column. When a column is restrictied, any INSERT
or UPDATE
statements must provide a value for that column to add or change a row respectively.
Variations on the generalized concept of a constraint are found in the NOT NULL
and CHECK
constraints. A NOT NULL
can universally impose a minimum cardinality constraint on a column. That means you must provide a value to the constrained column with any INSERT
or UPDATE
statement against the table. A CHECK
constraint can sometimes (implementation dependent) let you impose a minimum cardinality constraint on a collection of columns.
The data type of a column generally sets the maximum cardinality of a column. For example, a scalar variable can only hold one thing. On the other hand a collection may hold many things.
Cardinality is the combination of the minimum and maximum cardinality, respectively the least number of things in a column or relationship and the most number of things in the same column or relationship. Cardinality is represented by numbers for the minimum and maximum cardinality separated by two dots. For example, ANSI compliant columns are nullable, or have a zero to one cardinality, 0..1
(see the UML page for broader coverage).
As mentioned, scalar data types limit the maximum cardinality to one, which means a scalar column’s cardinality is 0..1
when the column is nullable. A nullable column exists when you can create or modify the column to let it contain a null value. A column value is thereby optional. This is supported by some IDE tools. The cardinality of a not null column is constrained to require a value and is represented by 1..1
cardinality. Not null columns are mandatory because they require you to provide a value.
Collection data types set the maximum cardinality to many (dependent on the implementation, this may be a bounded or an unbounded many). The cardinality for an optional collection data type column is 0..*
, while the not null collection data type column is 1..*
.
There are five types of database constraints. They are:
CHECK
Constraint ↓
CHECK
constraints are table-level constraint. You can only create table-level constraints as out-of-line constraints. You typically restrict a column value with a CHECK
constraint to a set of values defined by the constraint. A CHECK
constraint doesn’t make the column mandatory, which means the default is the ANSI standard for nullable columns. A cardinality of 0..1
(optional) is the ANSI standard. You override it by providing a NOT NULL
constraint. The NOT NULL
constraint makes the column cardinality 1..1
, or mandatory.
Another form of a CHECK
constraint converts null values to default values. The DEFAULT
clause when available in an implementation acts is an inline constraint. It imposes a 1..1
cardinality on a column.
The following example sets the default value in an Oracle database to Mr.
with the DEFAULT
keyword. This serves as an inline constraint. The out-of-line CHECK
constraint provides the list of potential column values. If you insert or update the column with a NULL value, the Mr.
is substituted. When you insert or update the column with a NOT NULL value, the CHECK
constraint validates whether the value is in the list of valid salutation values.
CREATE TABLE customer ( customer_id NUMBER , salutation VARCHAR2(4) DEFAULT 'Mr.' , customer_name VARCHAR2(40) , CONSTRAINT cc_customer_1 CHECK (salutation IN ('Mr.','Ms.','Miss','Mrs.','Dr.'))); |
You receive an error when trying to insert or update a value outside of the qualified range. The range of values in the foregoing example includes five salutations. Oracle implements the CHECK
constraint but MySQL doesn’t. Oracle’s CHECK
constraint lets you enter a range boundary through inequality operators or a list of values with the IN
operator.
You also have the ability of checking whether a column value is: (a) in a range; (b) a null value; (c) set when another column or set of columns contain specific values or fall within specific ranges. You may also use logical operators like AND
and OR
to make compound logic statements.
More Examples of CHECK
Constraint Syntax ↓
The following are small examples of table-level or out-of-line constraints. They provide examples of syntax that works in an Oracle 10g Database:
CHECK
Constraint for a Value
This checks whether the salutation
column is equal to Ms.
, and it prevents the insertion or update of the column with any other value.
, CONSTRAINT cc_customer_1 CHECK (salutation = 'Ms.') |
CHECK
Constraint for a set of Values in two or more Columns
This checks whether one of two compound expressions exists. The first checks if the salutation
column is equal to Mr.
or Dr.
and the gender
column is 'M'
(generally used for Male). The second tests if the salutation
column is equal to Ms.
, Mrs.
, Miss.
, or Dr.
and the gender
column is 'F'
(generally used for Female). Both conditions prevent the insertion or update of any unsupported set of values in the respected columns.
, CONSTRAINT cc_customer_1 CHECK ((salutation IN ('Mr.','Dr.') AND gender = 'M') OR (salutation IN ('Ms.','Miss','Mrs.','Dr.') AND gender = 'F')) |
CHECK
Constraint for a NULL
or NOT NULL
Value Column
This checks whether a column suffix
contains either a NULL
value or NOT NULL
value that falls in a range of values. A constraint like this generally applies to a column when it is nullable.
, CONSTRAINT cc_customer_1 CHECK ((suffix IS NULL) OR (suffix IS NOT NULL AND suffix IN ('Jr.','Sr.','III.','IV','V')) |
CHECK
Constraint for a Value in a Range of Numeric Values
This checks whether a column suffix
contains either a NULL
value or NOT NULL
value that falls in a range of values. A constraint like this generally applies to a column when it is nullable. The following restricts salary by class of employee, and prevents anybody from earning more than a million dollars a year.
ALTER TABLE net_income ADD CONSTRAINT salary_range_ck CHECK ((salary BETWEEN 0 AND 49999.99 AND employee_class = 'NON-EXEMPT') OR (salary BETWEEN 50000 AND 249999.99 AND employee_class = 'EXEMPT') OR (salary BETWEEN 250000 AND 999999.99 AND employee_class = 'EXECUTIVE')); |
More Examples of MySQL Constraint-like Data Types ↓
MySQL provides the ENUM
and SET
data types as implicit CHECK
constraints as alternatives. MySQL’s ENUM
and SET
data types let you enter a list of possible values for a column. They do not impose a NOT NULL
constraint, and therefore leave the column with a 0..1
cardinality.
You could implement the same logic in MySQL with an ENUM
data type and the DEFAULT
keyword. The following example provides a list of possible genders by declaring an ENUM
data type. It uses the default value of Female
when a statement attempts to insert or update the column with a NULL value.
The table creation sample for MySQL is:
CREATE TABLE people ( people_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT , first_name VARCHAR(20) NOT NULL , last_name VARCHAR(20) NOT NULL , gender ENUM('Female','Male') DEFAULT 'Female'); |
This is a MySQL implementation specific solution. You actually have two data types that act like implementation specific constraints, they are the ENUM
and SET
data types.
ENUM
provides a list of possible values and constrains you to inserting or updating one of the values. This makes the ENUM
data type exclusive because you can only insert one value from a list of values. You can find an example of how to create an ENUM
column with a NOT NULL
constraint in the ENUM
Data Type example (click the highlighted title to display the code).
ENUM
Data Type ↓
This shows you how to create a table with an ENUM
data type, populate it, and query it. The table definition creates an ENUM
column data type and constrains it as NOT NULL
, or makes it a mandatory column. This means any insert or update with a null value to that column will fail. You should use the DEFAULT
constraint when you want to make the column optional during insert or update activity while meeting the mandatory constraint.
You should note that the insert is a lowercase string but it returns a title case Male string when you query it. This occurs because the inserted value matches a case insensitive index, which then selects the case sensitive string from the enumeration list (yes, that’s what the acronym means).
-- Conditionally drop the table. DROP TABLE IF EXISTS people; -- Create a table with an enumeration type. CREATE TABLE people ( people_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT , first_name VARCHAR(20) NOT NULL , last_name VARCHAR(20) NOT NULL , gender ENUM('Female','Male') NOT NULL); -- Insert values with an overriding signature. INSERT INTO people (first_name, last_name, gender) VALUES ('Samuel','Adams','male'); -- Query values. SELECT * FROM people; |
You should note that the INSERT
statement uses an overriding signature, which excludes the primary key column. The lack of a column value triggers the use of the auto increment from the sequence value. The result set from the query is:
+-----------+------------+-----------+--------+ | people_id | first_name | last_name | gender | +-----------+------------+-----------+--------+ | 1 | Samuel | Adams | Male | +-----------+------------+-----------+--------+ |
The alternative would require using the default signature, while providing a null value for the primary key value.
-- Insert values with a default signature. INSERT INTO people VALUES (NULL,'Samuel','Adams','male'); |
SET
provides a list up to 64 possible items and constrains that you enter one, two, or any number of those comma-separated values up to the maximum of 64 items. This makes the SET
data type inclusive because you can insert one or more of the values. You can find an example of how to create an SET
column with a NOT NULL
constraint in the SET
Data Type example (click the highlighted title to display the code).
SET
Data Type ↓
This shows you how to create a table with a SET
data type, populate it, and query it. You should note that the insert also uses lowercase and uppercase strings but the return values are all title case. This occurs because the inserted value matches a case insensitive index, which then selects the case sensitive string from the set of values.
-- Conditionally drop the table. DROP TABLE IF EXISTS pizza_order; -- Create a table with a set type. CREATE TABLE pizza_order ( menu_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT , item_name VARCHAR(20) NOT NULL , toppings SET('Cheese','Sausage','Pepperoni','Ham','Pinapple','Salami') NOT NULL); -- Insert values with default signature and null to auto increment. INSERT INTO pizza_order VALUES (NULL,'Brady''s fourth meal','cheese,cheese,SAUSAGE,pepperoni,salami'), (NULL,'Jenny''s Hawaiian','cheese,ham,pinapple'); -- Query values. SELECT * FROM pizza_order; |
Whether you insert one or more copies of an ingredient, only one copy is stored from the list. This is demonstrated by the returned set of values:
+---------+---------------------+---------------------------------+ | menu_id | item_name | toppings | +---------+---------------------+---------------------------------+ | 1 | Brady's fourth meal | Cheese,Sausage,Pepperoni,Salami | | 2 | Jenny's Hawaiian | Cheese,Ham,Pinapple | +---------+---------------------+---------------------------------+ |
As mentioned, these CHECK
constraints in MySQL are implemented as data types, which lets you define the inclusive list inside the data type and also constrain the column as NOT NULL
with a single inline constraint.
FOREIGN KEY
Constraint ↓
A FOREIGN KEY
constraint restricts the values that are acceptable in a column or group of columns to those values found in a listing of the column or group of columns used to define the primary key. Dependent on the implementation, this may or may not impose a NOT NULL
column constraint on all members of the foreign key. If the implementation make the column or set of columns mandatory, then it makes the cardinality of columns in the FOREIGN KEY
mandatory, or 1..1
to the PRIMARY KEY
. However, the default is that a FOREIGN KEY
is 0..1
to the PRIMARY KEY
, which means that a row may be inserted in the table with the FOREIGN KEY
column. At least, it can be done provided that the constrained column or set of columns are null values.
A FOREIGN KEY
constraint prevents the insertion of any row of data with values not found in the referenced primary key column or set of columns. This is why using a FOREIGN KEY
constraint is also called referential integrity. Each row must reference that the values in a foreign key are found in the list of possible values in the primary key.
You can define a FOREIGN KEY
constraints inline or out-of-line. There’s no consensus on a best practice that I could find. The advantage of an inline constraint is likewise unclear because in certain implementations, like MySQL, you can’t name an inline foreign key constraint. MySQL also makes all foreign key constrained columns appear nullable when you describe them, unless you also place a NOT NULL
inline constraint on the column.
Therefore, it appears table-level (or out-of-line) constraints is a more consistent and portable between databases. Examples of writing these constraints in both an Oracle and MySQL database are provided below.
Oracle Examples of FOREIGN KEY
Constraints
The following shows you how to define an inline PRIMARY KEY
and FOREIGN KEY
constraints in an Oracle database. When you define table-level (or out-of-line) constraints the FOREIGN KEY
takes an argument list, which is the one or comma-delimited list of columns that make up the foreign key. The data types of each column and their organizational order is also critical. Any difference between the primary and foreign keys will cause the DDL statement to fail.
CREATE TABLE tweetie_bird ( tweetie_bird_id NUMBER CONSTRAINT pk1_tweetie_bird PRIMARY KEY , tweetie_bird_house VARCHAR(30) CONSTRAINT nn1_tweetie_bird NOT NULL , grandma_id NUMBER , CONSTRAINT fk1_tweetie_bird FOREIGN KEY (grandma_id) REFERENCES grandma (grandma_id)); |
You would add primary and foreign keys after table creation by using the following syntax:
ALTER TABLE tweetie_bird ADD CONSTRAINT pk1_tweetie_bird PRIMARY KEY (tweetie_bird_id) ADD CONSTRAINT fk1_tweetie_bird FOREIGN KEY (grandma_id) REFERENCES grandma (grandma_id); |
Like the inline PRIMARY KEY
constraint, the table-level constraint takes a single tweetie_bird_id
column name as an argument, but it could easily be a parameter list when it points to a compound primary key. The REFERENCES
keyword takes a table name followed by a parameter list of comma delimited column names that are found in the referenced table.
MySQL Examples of FOREIGN KEY
Constraints
The following shows you how to define an inline PRIMARY KEY
and FOREIGN KEY
constraints in a MySQL database. When you define table-level (or out-of-line) constraints the FOREIGN KEY
takes an argument list, which is the one or comma-delimited list of columns that make up the foreign key. The data types of each column and their organizational order is also critical. Any difference between the primary and foreign keys will cause the DDL statement to fail.
CREATE TABLE tweetie_bird ( tweetie_bird_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT , tweetie_bird_house VARCHAR(30) NOT NULL , grandma_id INT UNSIGNED CONSTRAINT fk1_tweetie_bird FOREIGN KEY (grandma_id) REFERENCES grandma (grandma_id)); |
A common mistake made by folks new to MySQL is to give FOREIGN KEY
columns an int
data type. This is fine provided that the PRIMARY KEY
column is also an int
not an int unsigned
. If the PRIMARY KEY
column is an int unsigned
, you must define the FOREIGN KEY
column likewise.
You would add primary and foreign keys after table creation by using the following syntax:
ALTER TABLE tweetie_bird ADD CONSTRAINT pk1_tweetie_bird PRIMARY KEY (tweetie_bird_id) , ADD CONSTRAINT fk1_tweetie_bird FOREIGN KEY (grandma_id) REFERENCES grandma (grandma_id); |
Like the inline PRIMARY KEY
constraint, the table-level constraint takes a single tweetie_bird_id
column name as an argument, but it could easily be a parameter list when it points to a compound primary key. The REFERENCES
keyword takes a table name followed by a parameter list of comma delimited column names that are found in the referenced table.
NOT NULL
Constraint ↓
A NOT NULL
constraint restricts a column by making it mandatory. This means you can’t insert a row in the table without providing a valid data type value for all NOT NULL
constrained columns. A mandatory column has the cardinality of 1..1
.
A NOT NULL
constraint prevents the insertion of any null value in a column. NOT NULL
constraints typically must be defined at table creation and as inline constraints. A CHECK
constraint that checks whether the value is NOT NULL mimics a NOT NULL
constraint and imposes a minimum cardinality of 1 and a maximum cardinality set by the data type. For example, a scalar data type with a Check Constraint avoiding a null value has a cardinality of 1..1
and a composite array or nested table has a cardinality of 1..*
.
Oracle Examples of NOT NULL
Constraints
You have two options for defining a NOT NULL
constraint in an Oracle database. One is inline and the other is out-of-line. An out-of-line constraint is also known as a table-level constraint. Only the inline NOT NULL
constraint shows the column value as required or mandatory. The out-of-line alternative is a CHECK
constraint, which constrains the column but doesn’t evidence itself when you describe it.
In an Oracle database, you define an inline NOT NULL
constraint like this:
CREATE TABLE sample ( sample_id NUMBER CONSTRAINT pk1_sample PRIMARY KEY , sample_text VARCHAR2(20) CONSTRAINT nn1_sample NOT NULL ); |
Alternatively, the Oracle database lets you define the table without the constraint. You can then constrain the column with a CHECK
constraint, like this:
-- Create the table with an unconstrained column. CREATE TABLE sample ( sample_id NUMBER CONSTRAINT pk1_sample PRIMARY KEY , sample_text VARCHAR2(20)); -- Add the Check constraint that disallows a null value and makes the column mandatory. ALTER TABLE sample ADD CONSTRAINT ck1_sample CHECK (sample_text IS NOT NULL); |
MySQL Examples of NOT NULL
Constraints
Unlike Oracle, you have only one way to define a NOT NULL
constraint in a MySQL database. It is inline constraint, which can’t have a meaningful name. MySQL treats a NOT NULL
constraint as a table property, which explains why an alternative doesn’t exist.
In a MySQL database, you define an inline NOT NULL
constraint like this:
CREATE TABLE sample ( sample_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT , sample_text VARCHAR(20) NOT NULL ); |
PRIMARY KEY
Constraint ↓
A PRIMARY KEY
constraint checks whether a column value will be unique among all rows in a table and disallows null values. Therefore, a PRIMARY KEY
has the behaviors of both NOT NULL
and UNIQUE
constraints. A PRIMARY KEY
may span two or more columns. A multiple column PRIMARY KEY
is known as a composite or compound key, which can be confusing but essentially the terms only mean that the key spans columns.
You can implement a PRIMARY KEY
as an in-line or out-of-line constraint. An out-of-line constraint is also known as a table-level constraint. You should generally always assign a single column PRIMARY KEY
constraint in-line.
There is only one time you should assign a PRIMARY KEY
constraint as an out-of-line constraint. That is when the PRIMARY KEY
is a compound or composite key, which means across two or more columns.
There is also one time when you should NEVER define a PRIMARY KEY
through an out-of-line syntax. That is when you want automatic numbering and are using a MySQL database because it doesn’t work.
Oracle Examples of PRIMARY KEY
Constraints
The following illustrates an out-of-line constraint definition in an Oracle database. One of differences between Oracle and MySQL are the data type definitions and the automatic numbering convention. You can read more about those differences here.
The only reason for this syntax on a single column is because somebody doesn’t understand that a PRIMARY KEY
implicitly makes a column NOT NULL
constrained. Therefore, while a NOT NULL
constraint is unnecessary for a primary key column, some folks use this technique.
CREATE TABLE GRANDMA ( grandma_id NUMBER CONSTRAINT grandma_nn1 NOT NULL , grandma_house VARCHAR2(30) CONSTRAINT grandma_nn2 NOT NULL , CONSTRAINT grandma_pk PRIMARY KEY (grandma_id)); |
The CONSTRAINT
keyword precedes the constraint name and is optional if you want to accept a system assigned name. The type of constraint follows the constraint name when provided. The recommended best practices is:
CREATE TABLE GRANDMA ( grandma_id NUMBER CONSTRAINT grandma_pk PRIMARY KEY , grandma_house VARCHAR2(30) CONSTRAINT grandma_nn2 NOT NULL); |
A PRIMARY KEY
constraint may designate a collection of columns as a primary key. When a set of columns defines a PRIMARY KEY
constraint, you must define it as an out-of-line constraint. You may only define a PRIMARY KEY
inline when it only references a single column in a CREATE
statement. You must define a multiple column PRIMARY KEY
constraints through an out-of-line constraint during table creation, or through an ALTER TABLE
statement after creating the table.
MySQL Examples of PRIMARY KEY
Constraints
You implement an inline PRIMARY KEY
constraint and automatic numbering by using the following syntax:
CREATE TABLE sample ( sample_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT , sample_text VARCHAR(20) NOT NULL ); |
You would implement a compound PRIMARY KEY
constraint with the following syntax. A compound primary key generally doesn’t apply to automatic numbering, and it’s truly interesting how some programmers work around that. A programmer is typically somebody who codes without great insight, whereas a developer is somebody who codes with design responsibility. Developers are also called software engineers.
CREATE TABLE sample ( sample_id VARCHAR(10) , sample_text VARCHAR(20) , CONSTRAINT pk_sample PRIMARY KEY (sample_id, sample_text)); |
The sample_id
column data type is not numeric, which should indicate to most people that the sample’s identification comes from a manual identification system. You will revisit this in the UNIQUE
constraint section.
UNIQUE
Constraint ↓
A UNIQUE
constraint checks whether a column value will be unique among all rows in a table.
UNIQUE
constraints ensure that only one row may contain a specific value, or a row that contains a set of specific values across two or more columns. You can define a table with a UNIQUE
constraint for a column by using either an inline or out-of-line constraint syntax. The out-of-line constraint is also known as a table-level constraint. Likewise, you can ALTER
a table and add a UNIQUE
constraint against one or more than one column.
Oracle Examples of a UNIQUE
Constraint
CREATE TABLE military_lookup ( military_lookup_id NUMBER CONSTRAINT pk1_military_lookup PRIMARY KEY , TABLE_NAME VARCHAR2(30) CONSTRAINT nn1_military_lookup NOT NULL , column_name VARCHAR2(30) CONSTRAINT nn2_military_lookup NOT NULL , lookup_type VARCHAR2(30) CONSTRAINT nn3_military_lookup NOT NULL , lookup_value VARCHAR2(30) CONSTRAINT nn5_military_lookup NOT NULL , lookup_meaning VARCHAR2(80) CONSTRAINT nn6_military_lookup NOT NULL , lookup_code VARCHAR2(10) , created_by NUMBER DEFAULT -1 , creation_date DATE DEFAULT SYSDATE , last_updated_by NUMBER DEFAULT -1 , last_update_date DATE DEFAULT SYSDATE , CONSTRAINT un1_military_lookup UNIQUE (TABLE_NAME, column_name, lookup_type, lookup_value, lookup_code) , CONSTRAINT fk1_military_lookup FOREIGN KEY(created_by) REFERENCES military_user(military_user_id) , CONSTRAINT fk2_military_lookup FOREIGN KEY(last_updated_by) REFERENCES military_user(military_user_id)); |
The preceding CREATE
statement builds a UNIQUE
constraint across five columns in the table. It means that only one row can contain those values. An INSERT
or UPDATE
statement is thereby constrained from allowing a second row to contain that set of unique values in those specific columns. However, Oracle doesn’t optimize queries based on that constraint.
MySQL Example of a UNIQUE
Constraints
MySQL lets you implement a UNIQUE
constraint as either a KEY
or as an INDEX
. The choice of a KEY
acts most like a hash map. You choose INDEX
when you want the UNIQUE
constraint to guarantee uniqueness and expedite queries.
CREATE TABLE IF NOT EXISTS military_lookup ( military_lookup_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT , TABLE_NAME VARCHAR(30) NOT NULL , column_name VARCHAR(30) NOT NULL , lookup_type VARCHAR(30) NOT NULL , lookup_value VARCHAR(30) NOT NULL , lookup_meaning VARCHAR(80) NOT NULL , lookup_code VARCHAR(10) , created_by INT UNSIGNED DEFAULT 0 , creation_date DATE , last_updated_by INT UNSIGNED DEFAULT 0 , last_update_date DATE , CONSTRAINT un1_military_lookup UNIQUE INDEX (TABLE_NAME, column_name, lookup_type, lookup_value, lookup_code) , CONSTRAINT fk1_military_lookup FOREIGN KEY(created_by) REFERENCES military_user(military_user_id) , CONSTRAINT fk2_military_lookup FOREIGN KEY(last_updated_by) REFERENCES military_user(military_user_id)) ENGINE=InnoDB; |
The preceding CREATE
statement builds a UNIQUE
constraint across five columns in the table. It means that only one row can contain those values. An INSERT
or UPDATE
statement is thereby constrained from allowing a second row to contain that set of unique values in those specific columns.
You may define constraints when you create tables, or defer their creation and use an ALTER TABLE
command. However, the NOT NULL
constraint can’t be added with the ALTER TABLE
command. Oracle requires that you add a NOT NULL
constraint as an inline constraint. Unfortunately, inline NOT NULL
constraints often don’t receive meaningful names and often rely on system generated sequence names in Oracle. NOT NULL
constraints are an attribute of a table’s column in MySQL and can’t have a name.
Inline and Out-of-line (Table-level) Constraints ↓
This shows you how to define inline and out-of-line constraints in Oracle. The approach is similar for MySQL and Microsoft® SQL Server but there are slight syntax variations. It also shows you how to define meaningful names for inline constraints.
This shows you how to add an inline NOT NULL
constraint with a system generated name.
The better practice would include a meaningful constraint name, which you can add as an inline constraint with the syntax below in an Oracle database. You can’t assign a meaningful name to a NOT NULL
constraint in a MySQL database.
While a NOT NULL
constraint is unnecessary when you intend to make a column a PRIMARY KEY
because primary keys are both not null and unique by default. It’s a handy way to demonstrate how to define an out-of-line constraint.
For the “Check Constraint” under EXTERNAL, there is a green check mark. Shouldn’t that be an ‘X’? It says, “A CHECK constraint is an internal constraint and can’t reference external values in another table, like a foreign key constraint. ” To me, I understand that as not being able to reference external values, meaning it’s not possible or maybe it is. Can you please clarify?
Thanks!
Trevor D
13 Jan 10 at 10:11 pm
Yes, it’s not possible. I lost the original image. I’m working on changing it. Also, I’m going to increase the columns by creating one for a single column and another for multiple columns. Great catch! 😉
michaelmclaughlin
13 Jan 10 at 10:20 pm
MySQL does have a CHECK constraint (if you can call it a constraint), but the book says “the MySQL server allows check constraints to be defined but does not enforce them.” This seems pointless; why set a rule if it doesn’t have to be followed?
Ben Davidson
28 Apr 10 at 7:06 am
Good point. If you find an answer, let me know. I’ve no clue. That’s why I say it doesn’t. A feature devoid of implementation isn’t a feature.
michaelmclaughlin
21 Jun 10 at 10:26 pm
In the section:
Oracle Examples of FOREIGN KEY Constraints
Your code shows an inline foreign key constraint on grandma_id in the format of an out of line constraint and doesn’t run.
CREATE TABLE tweetie_bird
( tweetie_bird_id NUMBER CONSTRAINT pk1_tweetie_bird PRIMARY KEY
, tweetie_bird_house VARCHAR2(30) CONSTRAINT nn1_tweetie_bird NOT NULL
, grandma_id NUMBER CONSTRAINT fk1_tweetie_bird FOREIGN KEY (grandma_id) REFERENCES grandma (grandma_id));
If the foreign key constraint on grandma_id is suppose to be out of line then a comma will need to be added.
If the constraint should be inline then the code from “CONSTRAINT” to “(grandma_id)” needs to be deleted.
Jared Morris
28 Aug 10 at 3:50 pm
On the MySQL foreign keys, should it have a comma after each constraint you set?
ALTER TABLE tweetie_bird
ADD CONSTRAINT pk1_tweetie_bird PRIMARY KEY (tweetie_bird_id), but this comma is not shown above.
Stephen Thompson
20 Sep 10 at 10:50 am
Yes, it’s fixed. Also, a
VARCHAR2
was in the previous statement. It’s now aVARCHAR
.michaelmclaughlin
3 Oct 10 at 2:45 pm
I am still seeing commas for Oracle and not for MySQL.
Kyle Redfearn
14 Jan 11 at 12:04 am
Can you be more specific where you’re finding the error?
michaelmclaughlin
16 Mar 11 at 2:23 pm
Shouldn’t the ALTER statement for the foreign key MYSQL example have a comma between the ADD statements?
Eli
30 Apr 11 at 8:29 pm
database constraints
rajashree borah
28 Jun 12 at 10:47 am