CONSTRAINT

Definition:

It is an SQL keyword in general,but it does not apply in HKDSE.

Also,it means a restriction on table/field.

(Remark:Although some of below keywords do not exist in HKDSE ICT candidates’ reference sheet,it is better to remember those keywords.)

---

Command Example:

---

1.PRIMARY KEY

CREATE TABLE table_name(field1_name data_type PRIMARY KEY);

• Set the field called field1_name as the PRIMARY KEY of the new table table_name

ALTER TABLE table_name ADD PRIMARY KEY(field1_name);

• Set the existing field called field1_name as the PRIMARY KEY of the existing table table_name

ALTER TABLE table_name DROP PRIMARY KEY;

• Drop the PRIMARY KEY constraint of the existing table table_name

---

2.FOREIGN KEY

CREATE TABLE table_name(
field1_name data_type[constraint(s)],
...
FOREIGN KEY(field1_name)
REFERENCES reference_table_name(parent_field_name));

• Set the field called field1_name as the FOREIGN KEY with refering the dataset of the parent field parent_field_name in the reference table reference_table_name

ALTER TABLE table_name ADD FOREIGN KEY(field1_name) REFERENCES reference_table_name(parent_field_name);

• Set the existing field called field1_name as the FOREIGN KEY with refering the dataset of the parent field parent_field_name in the reference table reference_table_name

ALTER TABLE table_name DROP FOREIGN KEY foreign_key_name;

• Drop the FOREIGN KEY constraint called foreign_key_name of the existing table table_name
• foreign_key_name can be found via using SHOW CREATE TABLE - SQL statement

---

3.NOT NULL

CREATE TABLE table_name(field1_name data_type NOT NULL);

• Set the field called field1_name that should be filled in with NOT NULL values when inserting records in the table table_name

ALTER TABLE table_name MODIFY field1_name data_type NOT NULL;

• Modify the existing field called field1_name with data_type and field1_name should be filled in with NOT NULL values when inserting records in the table table_name and storing existing records

ALTER TABLE table_name MODIFY field1_name data_type;

• Modify the existing field called field1_name with data_type and drop the NOT NULL constraint

---

4.UNIQUE

ALTER TABLE table_name ADD UNIQUE(field1_name);

• Set the existing field called field1_name that should be filled in UNIQUE values when inserting records in the table table_name and storing existing records

ALTER TABLE table_name ADD UNIQUE(field1_name);

• Set the existing field called field1_name that should be filled in UNIQUE values when inserting records in the table table_name and storing existing records

ALTER TABLE table_name DROP INDEX field1_name;

• Drop the UNIQUE constraint of the existing field field1_name

---

5.DEFAULT

CREATE TABLE table_name(field1_name data_type DEFAULT default_value);

• Set the field called field1_name that would be DEFAULTly filled in default_value when that field of the inserting records or existing records does not fill in any value

ALTER TABLE table_name ALTER field1_name SET DEFAULT default_value;

• Set the existing field called field1_name that would be DEFAULTly filled in default_value when that field of the inserting records or existing records does not fill in any value

ALTER TABLE table_name ALTER field1_name DROP DEFAULT;

• Drop the DEFAULT constraint of the existing field field1_name

---

6.CHECK

CREATE TABLE table_name(
field1_name data_type [constraint(s)],
field2_name data_type [constraint(s)],
field3_name data_type [constraint(s)],
...
CHECK(criteria)
);

• Set criteria as the validation CHECK of the new table table_name
• criteria example: 

  CHECK(CLASS_NO > 0)
  CHECK(SID > 0 AND CLASS = '1B')

ALTER TABLE table_name ADD CHECK(criteria);

• Set criteria as the validation CHECK of the existing table table_name

ALTER TABLE table_name DROP valid_check_name;

• Drop the CHECK constraint called valid_check_name of the existing table table_name
• valid_check_name can be found with using SHOW CREATE TABLE - SQL statement

---

Other Reference:

IBM:Link
Oracle:Link

---

'Hard but interesting.'By secondary 6 student