Table Creation and Data Manipulation Commands
A table is a basic structure where data is stored in the database. It is divided into rows and columns. The CREATE TABLE command is used to create a new table. To create tables, one can either create a new database or use an existing database. When a table is created, its columns are named, data types and sizes are supplied for each column. Each table must have at least one column.
The DESC[RIBE] command is used to view table’s structure.
When a database is dropped, all its tables also get removed along with the database. A constraint refers to a condition or a check that is applied to a column or set of columns in a table. Column constraint applies only to individual columns.
Table constraint applies to groups of one or more columns.
Database Integrity Constraints ensure database integrity. There are many types of integrity constraints such as Unique, Check, Primary key, Foreign key, Default, Enum, Set constraints. UNIQUE Constraint ensures that no two rows have the same value in the specified column(s).
PRIMARY KEY Constraint declares a column as the primary key of the table. A
FOREIGN KEY constraint establishes a relationship between two columns in the same table or between different tables. To define a column as a Foreign Key, it should be defined as a Primary Key or UNIQUE KEY in the table which it is referring.
In RDBMS, tables reference one another through common fields and to ensure validity of references, referential integrity is enforced. Referential integrity is a system of rules that a DBMS uses to ensure that relationships between records in related tables are valid, and that users don’t accidently delete or change related data. Referential integrity is ensured through FOREIGN KEY constraint.