MySQL Advanced Topics | Part 1

MySQL Example with Explanation:

CREATE TABLE "customer_data" (
	"customer_id" INT(10) NOT NULL AUTO_INCREMENT,
	"customer_code" VARCHAR(10) NULL DEFAULT NULL ,
	"customer_name" VARCHAR(50) NULL DEFAULT NULL ,
	"introducer_id" INT(10) NULL DEFAULT NULL,
	"customer_duration" INT(10) NULL DEFAULT NULL,
	"created_date" DATE NULL DEFAULT NULL,
	"updated_date" DATETIME NULL DEFAULT NULL,
	PRIMARY KEY ("customer_id") USING BTREE,
	INDEX "Index 2" ("customer_id", "customer_code") USING BTREE,
	INDEX "FK_customer_data_user_info" ("customer_code") USING BTREE,
	CONSTRAINT "FK_customer_data_user_info" FOREIGN KEY ("customer_code") REFERENCES "user_info" ("user_source") 
    ON UPDATE CASCADE ON DELETE CASCADE
    
)ENGINE=InnoDB,AUTO_INCREMENT=6;

Explanation:

• The CREATE TABLE creates a table with the name customer_data with a primary key which is an integer of maximum 10 characters and has the constraint that it cannot be "NULL" and will be auto incremented by 1 each time a new row is inserted in that table of "customer_data"
• "customer_code" is an identifier for a customer that can be maximum 10 chracters in length
• "customer_name" is the name of the customer and may take upto 50 characters
• "introducer_id" is the primary key of the user which referred this user
• "customer_duration" is the time the customer took to buy an item
• "created_date" is the date when the customer was created in the table
• "updated_date" is the date and time the customer entry was updated
• "customer_id" is the primary key meaning it uniquely identifies a row in a table
• ANother user-made index is the combination of (customer_id,customer_code)
• The name of the above index is "Index 2"
• Another user-made index is the column "customer_code" as it is also unique for all the customers, meaning no two customers will have the same "customer_code"
• Foreign key constraint is a constraint which ensure that the indexed column refers to a column in the foreign table
• For Example , in the above table creation, we have created a foreign key constraint that checks if the "user_source" in the "user_info" table matches the "customer_code" in the "customer_data" table
• ON UPDATE CASCADE ON DELETE CASCADE means Whenever the foreign key column of a row is updated or deleted, then it will delete all references to that column name in the foreign key table
• ON UPDATE RESTRICT ON DELETE RESTRICT works the opposite wayin that it will not delte or update the references in the foreign table(s)
• AUTO_INCREMENT=6 starts numbering the primary key column from 6
• The ENgine is how the data is sotred deleteed jupdated fetched and so on

ALTER TABLE inventory_list 
ADD COLUMN sel_mode VARCHAR(45) NOT NULL DEFAULT 'N';

The above SQL snippet adds a selling mode (sel_mod) column of variable characters maximum upto 45 characters

ALTER TABLE shipping_providers
DROP COLUMN redirect VARCHAR(50) NULL DEFAULT NULL;

The above SQL snippet removes the redirect column which may not be required as the company is expanding