DEV Community

Shelner
Shelner

Posted on

What is `CONSTRAINT` Keyword in MySQL

#mysql #tutorial

CONSTRAINT

Overview

  • A constraint is a rule you add to your table to control what kind of data is allowed.
  • The word CONSTRAINT lets you give a name to that rule.
  • This name is useful for debugging or changing the rule later.

Example: 

CONSTRAINT fk_relative FOREIGN KEY (`relative_id`) REFERENCES `persons`(`id`) ON DELETE CASCADE
Enter fullscreen mode Exit fullscreen mode

This means:
"I'm adding a rule called fk_relative that says the relative_id must match an id in the persons table. Also, if that person is deleted, automatically delete this relationship too."

What happens:

  • You can only insert person_id values that exist in the persons table.
  • If you try to insert a person_id that doesn't exist, MySQL gives an error.
  • If the referenced person is deleted (and ON DELETE CASCADE is set), their relationships are also deleted automatically.

Like:
You can only list friends who are already in your contact list. And if you remove a contact, their friendships disappear too.

UNIQUE

Overview

  • As you know, UNIQUE means "no duplicates allowd."
  • It ensures that the same combination of values doesn't appear more than once in that table.

Example: 

CONSTRAINT `unique_releationship` UNIQUE (`person_id`, `relative_id`, `relationship_type`)
Enter fullscreen mode Exit fullscreen mode

This means:
"You can't have two identical rows where the same person has the same relationship type with the same relative more than once."

You can't have two rows where person_id, relative_id, and relationship_type are all exactly the same.

Valid Example (no duplicates):

id person_id relative_id relationship_type
1 A B parent
2 B A child
3 A C sibling
4 A B sibling

Invalid Example (duplicate combination):

id person_id relative_id relationship_type
1 A B parent
2 A B parent

Summary:

Keyword Meaning
CONSTRAINT Names a rule on the table (like foreign key or unique rule)
UNIQUE Prevents duplicate values in one or more columns

Top comments (0)