![]() ![]() Where primary_col1, primary_col2, and so on are the columns on which the primary key is to be defined and are the same specified again in the list_of_columns parameter to table constraint PRIMARY KEY. Note that this syntax can also be used when the key is defined on the single column. When we have to define the primary key on more than one column then we can use the PRIMARY KEY table constraint at the end of the list of columns of the table that are specified in a comma-separated format. Where primary_col is the name of the column on which primary key is be defined. The syntax of defining the primary key on the single column is given below: When a primary key needs to defined only on a single column then we can assign the PRIMARY KEY property in front of the column and its data type as its property. Most of the time, INT datatype is sufficient but when too many records are to be stored at a large scale then the column of the primary key should be defined as BIGINT data type. While assigning the data type to the column of the primary key, we need to be careful because it should contain all the rows that we wish to insert and hence the sufficient number of keys should be available and the range of the data type of the column needs to be defined accordingly. ![]() Whenever a primary key is defined on the table, MySQL automatically internally creates an index named PRIMARY on the columns on which the primary key is defined.Most often the column on which the primary key is defined is kept of integer type and AUTO_INCREMENT property is assigned to it as it will lead to the generation of the values of the column uniquely and sequential fashion. A table can have only one column on which the AUTO_INCREMENT property can be assigned.This will lead to faster and quick retrieval of the resultset. Most often, the datatype of the column that is used to define the primary key is kept integer because MySQL works very fast with integers.Any table in MySQL can have only one or none of the primary key defined on it.If we try inserting or updating such columns with NULL value then MySQL will issue an error. Hence, the NULL value cannot be stored in the columns that define the primary key. When the primary key is defined on the column then implicitly the NOT NULL constraint is applied for the column or columns on which the primary key constraint is defined.When a single column is used for defining the primary key then it must contain the unique values stored in each of the rows of the table while in case of defining the primary key on multiple columns the combination of the value of all the columns on which the primary key is defined should have unique value together.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |