[DB/SQL] What is Key and Constraint

Key

Key is attribute the set of columns that can uniquely identify rows(records) in a table. Every table has key.

1. Uniqueness
   • there are not any overlaps.
2. Minimality
   • when the key set is fewest number of attributes

Sort of Keys

1. Candidate Key
   • Set of attributes that satisfies Uniqueness and Minimality.
2. Primary Key
   • One key that is chosen from Candidate. It cannot have NULL, and redundant value.
3. Alternate Key
   • Candidate keys that are not chosen as Primary Key.
4. Super Key
   • The Set of keys that satisfies Uniqueness.
5. Foreign Key
   • attributes that refer to other tables’ primary key.

Foreign Key

Especially at Foreign Key, there are some precautions to use. if certain field in certain table refers to other tables’ field and you modify the records, other table becomes to have wrong records. It calls violation of referencial integrity.

You have to set referencial integrity constraint.

ALTER TABLE <table_name> ADD CONSTRAINT <constraint> FOREIGN KEY (<column_name>) REFERENCES <parent_table_name> (<primary_key_of_parent_table>)
	ON UPDATE <UPDATE constraint>
	ON DELETE <DELETE constraint>;

ON keyword

back of REFERENCES… you can add ON keyword. NO ACTION, RESTRICT, CASCADE, SET NULL are contained on ON UPDATE/ON DELETE constraints.

1. NO ACTION, RESTRICT
   • if you omit ON keyword, ON UPDATE/DELETE NO ACTION option set as default. It prevents deleting or modifing primary key of refering table.
2. SET NULL
   • If ON UPDATE SET NULL, If the primary key that is referd by foreign key has changed, the foreign key changes to NULL.

Constraint

Constraint is the rules that is set for preventing misinputs of records(rows).

• NOT NULL
  • must enter values
• UNIQUE
  • must has uniqueness
• PRIMARY KEY
  • set this field as primary key
• FOREIGN KEY
  • set this field as foreign key
• DEFAULT
  • set default
• AUTO_INCREMENT
  • field that data-type is number, server set numbers that increase 1 from start number. This field must set as Key.

How to set Constraints?

set with CREATE command or add with ALTER command.

CREATE TABLE table_name(
field_name data_type constraint,
constraint(field_name)
);

ALTER TABLE table_name ADD CONSTRAINT contraint_name constraint(field_name);

How to delete constraints?

When you add constraint, it cannot be changed. You have to delete it and re-add.

1. PRIMARY KEY constraint
   • ALTER TABLE <table> DROP PRIMARY KEY;
2. UNIQUE constraint
   • ALTER TABLE <table> DROP INDEX <constraint_name>;
3. FOREIGN KEY constraint
   • ALTER TABLE <table> DROP FOREIGN KEY <constraint_name>;

The end