MySQL Column Modifiers

0

- Advertisement -

Modifiers can optionally be used to further control column content.

  • NOT NULL
  • UNIQUE
  • AUTO_INCREMENT
  • DEFAULT
  • PRIMARY_KEY

NOT NULL

Insists that each record must include a data value in this column. This allows an empty string “” to be stored there but does not allow that column to be empty or NULL.

Ex :

UNIQUE

Insists that records may not duplicate any entry in this column. When the data stored in a column should never be duplicated that column should be defined with the UNIQUE modifier.

Ex :

AUTO_INCREMENT

Available only for numeric columns, to automatically generate a number that is one more than the previous value in that column.

The  AUTO_INCREMENT modifier is particularly useful to automatically generate incremental identity numbers for each row – the first row will be numbered 1, the second row 2, and so on. 

Ex: 

DEFAULT

Specifies a value to be used where no value is stated for this column when a record is inserted. Setting a column DEFAULT  allows records to be inserted without te-diously requiring a value for a column that is usually contant. For instance a “quantity” column might usually contain a value of 1 in each record – so 1 could be set as its default value.

PRIMARY KEY 

A PRIMARY KEY is a constraint that is applied to a column to uniquely identify each row of that database table. It ensures that the values in each row of that column are unique and never change, so those values can be used to reference any specific row.

Each field that column must have a value -it may not be empty or have a NULL value.

Each value in that column must be unique – there must be no duplication.

Each value in that column can never be modified or updated.

Each value in that column in that column cannot be re-used – when a row is deleted its  PRIMARY KEY value cannot be re-assigned as the PRIMARY KEY values of a new row.

Ex :

- Advertisement -

Leave A Reply

Your email address will not be published.