Add or Add Constraint for Foreign Key Constraints

Question

The term 'Foreign Key Constraint' is often tossed around. And I just want to clarify it's exact meaning. We have an employees table and a branches table. The employees table was created first but it should have a foreign key constraint on branch_id, which references the primary (surrogate) key of id on branches table:

CREATE TABLE employees (
  id INT AUTO_INCREMENT,
  first_name VARCHAR(40),
  last_name VARCHAR(40),
  birth_day DATE,
  sex BOOLEAN,
  salary INT,
  supervisor_id INT,
  branch_id INT,
  PRIMARY KEY(id)
)

CREATE TABLE branches (
  id INT AUTO_INCREMENT,
  branch_name VARCHAR(40),
  manager_id INT,
  manager_start_date DATE,
  PRIMARY KEY(id),
  FOREIGN KEY(manager_id) REFERENCES employees(id) ON DELETE SET NULL
)

And now we add the Foreign Key Constraints:

ALTER TABLE employees
ADD FOREIGN KEY(branch_id)
REFERENCES branches(id)
ON DELETE SET NULL;

ALTER TABLE employees
ADD FOREIGN KEY(supervisor_id)
REFERENCES employees(id)
ON DELETE SET NULL;

Notice here I use Add Foreign Key and not Add Constraint constraint_name. Here would be an example of using ADD CONSTRAINT:

ALTER TABLE users
ADD CONSTRAINT check_users_age 
CHECK (age>=18 AND city='Philadelphia');

Is ADD FOREIGN KEY and ADD CONSTRAINT constraint_name synonymous? Does ADD FOREIGN KEY, in effect, add a constraint without a name? And if ADD FOREIGN KEY does add a name, how can I find it in mysql?

i doubt this (My)SQL code even runs.. `supervisor_id INT, branch_id INT PRIMARY KEY(id) )` is missing a comma.. copy/paste error maybe? — Raymond Nijland, Jan 20 '19 at 17:30
@RaymondNijland the comma was missing and I added it. But that was not really the point of the post. — Daniel Viglione, Jan 20 '19 at 17:31
By the way `CHECK (age>=18 AND city='Philadelphia');` will never work in MySQL.. All MySQL versions and storage engines parse CHECK but will not execute it's in the [manual](https://dev.mysql.com/doc/refman/8.0/en/create-table.html) "CHECK The CHECK clause is parsed but ignored by all storage engines".. But you can simulate this with a [updateable view with check option](https://dev.mysql.com/doc/refman/5.6/en/view-check-option.html) — Raymond Nijland, Jan 20 '19 at 17:34
@RaymondNijland did you check out this post: https://stackoverflow.com/questions/2115497/check-constraint-in-mysql-is-not-working it says that CHECK constraint is now supported in MariaDB. — Daniel Viglione, Jan 22 '19 at 15:06
"it says that CHECK constraint is now supported in MariaDB" Where did i mention MariaDB in mine comment? @Donato ..Besides this question was tagged MySQL from the start so not sure why you bring op MariaDB, which indeed supports CHECK contraint which i knew.. MariaDB isn't a MySQL version to be clear it's a fork off it based on the MySQL source code to be clear but MySQL supports features which MariaDB does not support and the other way around if you are using MariaDB you should tag it as MariaDB. — Raymond Nijland, Jan 22 '19 at 22:19

score 0 · Accepted Answer · answered Jan 20 '19 at 17:45

Explicitly naming the constraint, i.e. use CONSTRAINT <name> FOREIGN KEY ... is optional. If you don't do it, i.e. just go with FOREIGN KEY ... the system generates a name.
The effects a constraint have are independent of its name. So both, explicitly naming the constraint and not doings so are synonym-ish -- that is except regarding the name.
You can query the constraints from the catalog, e.g. from information_schema.key_column_usage.

Consider the following example:

CREATE TABLE a
             (id integer,
              PRIMARY KEY (id));

CREATE TABLE b
             (id integer,
              PRIMARY KEY (id));

CREATE TABLE x
             (a integer,
              b integer,
              FOREIGN KEY (a)
                          REFERENCES a
                                     (id),
              CONSTRAINT fancy_name
                         FOREIGN KEY (b)
                                     REFERENCES b
                                                (id));

SELECT table_name,
       column_name,
       constraint_name,
       referenced_table_name,
       referenced_column_name
       FROM information_schema.key_column_usage
            WHERE table_schema = database()
                  AND table_name = 'x';

This will result in something like:

| table_name | column_name | constraint_name | referenced_table_name | referenced_column_name |
| ---------- | ----------- | --------------- | --------------------- | ---------------------- |
| x          | b           | fancy_name      | b                     | id                     |                    
| x          | a           | x_ibfk_1        | a                     | id                     |

DB Fiddle

You can see, the system picked a name for the constraint.

Add or Add Constraint for Foreign Key Constraints

1 Answers1