MySQL - Constraints
| EMPNO |
ENAME |
SAL |
DEPTNO |
| 1 |
A |
5000 |
1 |
| 2 |
A |
6000 |
1 |
| 3 |
C |
7000 |
1 |
| 4 |
D |
9000 |
2 |
| 5 |
E |
8000 |
2 |
- Limitations/Restriction impose on a table
Primary Key
- Primary column
- Column or Set of Column that uniquely identifies a row. (eg: EMPNO)
- Duplicate values are not allowed (it has to be unique)
- Null values are not allowed (it is mandatory column)
- Having a Primary key column in the table is not compulsory but its recommended that every table should have a Primary Key, it helps from a long term perspective
- Purpose of Primary key is row uniqueness, with the help of Primary key column, you can distinguish between 2 rows of a table
- Text and Blob cannot be Primary key
- Primary key is the Best column for SEARCHING, in MySQL if you declare a column as Primary key the unique index is automatically created
Composite Primary Key
- Combine 2 or more Inter-Dependent(preferably) column together to serve the purpose of Primary Key
- In MySQL you can combine upto 32 column in Composite Primary Key
- If you have a Composite Primary Key the Composite unique INDEX is created automatically
Note:
Why you not make ROWID as a primary key?
ROWID is unique but not immutable; it can change due to table operations like EXPORT/IMPORT, TABLE MOVE, or REBUILD INDEX.