ALTER TABLE

Definition:

Alter something in an existing table.

---

Command Syntax:

---

1.PRIMARY KEY

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

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

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 DROP INDEX field1_name;

• Drop the UNIQUE constraint of the existing field field1_name

---

5.DEFAULT

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

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:

MySQL:Link
Oracle:Link

---

'ICT ALTER your school life with a crazy no life.'
By secondary 6 student