What is `CONSTRAINT` Keyword in MySQL

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

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`)

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