/* reset the id to 1 in mysql */
1. Backup your data:
2. Delete existing data:
DELETE FROM your_table_name;
3. Reset the auto-increment counter:
ALTER TABLE your_table_name AUTO_INCREMENT = 1;
4. Insert data again:
INSERT INTO your_table_name (column1, column2, ...)
VALUES (value1, value2, ...);
/* show weather a column is auto-incremented or not */
SHOW COLUMNS FROM your_table_name LIKE 'your_column_name';
--If the column is auto-incremented, you'll see auto_increment in the Extra column.
/* make existing column, auto-incremented */
ALTER TABLE your_table_name
MODIFY COLUMN book_id INT AUTO_INCREMENT PRIMARY KEY;
/* change existing table name by another name */
RENAME TABLE current_table_name TO new_table_name;
/* change existing column name by column name */
ALTER TABLE table_name
CHANGE old_column_name new_column_name data_type;
/* delete the foreign key from a COLUMN */
ALTER TABLE record
DROP FOREIGN KEY your_foreign_key_name;
--Replace your_foreign_key_name with the actual name of your foreign key constraint. If you don't know the name of the foreign key, you can usually find it by querying the information_schema database:
SELECT constraint_name
FROM information_schema.key_column_usage
WHERE table_name = 'record' AND column_name = 'record_state_i';
-- above will show the name and type of constraint ( in my case, constraint_name = record_ibfk_2 )
-- for remove the constraint
ALTER TABLE record
DROP FOREIGN KEY your_foreign_key_name;
/* find the column ,if it has some foreign key constraint or not */
SELECT
COLUMN_NAME,
CONSTRAINT_NAME,
REFERENCED_TABLE_NAME,
REFERENCED_COLUMN_NAME
FROM
INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
TABLE_SCHEMA = 'kbpro' AND
TABLE_NAME = 'record' AND
COLUMN_NAME = 'record_gword_i';
-- in my case the result is as below
-- COLUMN_NAME CONSTRAINT_NAME REFERENCED_TABLE_NAME REFERENCED_COLUMN_NAME
-- record_gword_i record_ibfk_2 gword gword_id
/* add an column to existing table */
-- syntax
-- ALTER TABLE database-name.column-name
-- ADD COLUMN new-column-name data-type;
-- Example
ALTER TABLE kbpro.record
ADD COLUMN record_user_i INT;
/* add foregin key to exsisting column */
-- syntax
-- ALTER TABLE table-1
-- ADD FOREIGN KEY (column-name-of-table-1) REFERENCES table-2 (column-name-of-table-2);
-- Example
ALTER TABLE Student
ADD FOREIGN KEY (City) REFERENCES City(Cid);
/* sample books*/
INSERT INTO book (book_n )
VALUE ("In Search of Lost Time by Marcel Proust" ),
("Ulysses by James Joyce" ),
("Don Quixote by Miguel de Cervantes" ),
("One Hundred Years of Solitude by Gabriel Garcia Marquez" ),
("The Great Gatsby by F. Scott Fitzgerald" ),
("Moby Dick by Herman Melville" ),
("War and Peace by Leo Tolstoy" ),
("Hamlet by William Shakespeare" ),
("The Odyssey by Homer" ),
("Madame Bovary by Gustave Flaubert" );
/* sample state */
INSERT INTO state (state_n )
VALUE ("Bihar" ),
("Chhattisgarh" ),
("Goa" ),
("Gujarat" ),
("Haryana" ),
("Himachal Pradesh" ),
("Jharkhand" ),
("Karnataka" ),
("Maharashtra" ),
("Mizoram" );
/* sample class */
INSERT INTO class (class_n )
VALUE ("first" ),
("second" ),
("third" ),
("fourth" ),
("fifth" ),
("sixth" ),
("seventh" ),
("eighth" ),
("ninth" ),
("tenth" );
/* sample artical */
INSERT INTO artical (artical_n )
VALUE ("Cinderella" ),
("Beauty and the Beast." ),
("Rapunzel" ),
("Snow White and the Seven Dwarfs." ),
("Little Red Riding Hood." ),
("Jack and the Beanstalk." ),
("Sleeping Beauty." ),
("Puss in Boots." ),
("Hansel and Gretel" ),
("Frog Prince" );
/* sample groupword */
INSERT INTO gword (gword_n, gword_Pid, gword_cid )
VALUE ("cat",1,2 ),
("dog",3,4 ),
("abb",3,5 ),
("add",4,4 ),
("app",2,2 ),
("bee",5,34 ),
("cab",22,43 ),
("ass",12,23 ),
("mic",22,52 ),
("boat",31,36 );