Let us understand how to provide a custom name to the PRIMARY KEY constraint in MySQL. Creating PRIMARY KEY Constraint with Custom name in MySQL: Note: There is no difference in behavior whether we imposed the PRIMARY KEY Constraint at the table level or at the column level but if we imposed the Constraint at the table level, then we have the chance to impose the composite PRIMARY KEY constraints. As you can see in the below Create Table statement, first, we created all the three columns and then we created the PRIMARY KEY constraints and while creating the PRIMARY KEY constraint we specified the column name to which the constraint belongs. Let us understand how to impose MySQL PRIMARY KEY constraint at table level with an example. ) Imposing PRIMARY KEY Constraint at Table Level: As you can see in the below Create Table statement, we have specified PRIMARY KEY Constraints directly at the column level for the Id Column. Let us understand how to Impose PRIMARY KEY Constraint at column level in MySQL with an example. Imposing PRIMARY KEY Constraint at Column Level: Let us understand the same concept with PRIMARY KEY Constraint. Note: So, the Primary key neither accepts NULL Values or Duplicate Values in MySQL.Īs we already discussed except the NOT NULL constraint, all other constraints can be imposed either at the Table or Column level. When we try to execute the above Insert SQL statement, it gives us the following error which clearly states that the Id value cannot be the null value which proves that Primary Key will not accept NULL.Įrror Code: 1048. INSERT INTO Employee (Id, Name, Email, Department) VALUES (NULL, ‘Anurag’, ‘IT’) Id column by executing the below insert statement. Now, let us try to insert a NULL value into the primary key column i.e. Duplicate entry ‘1’ for key ’employee.PRIMARY’ Inserting NULL in Primary Key Column in MySQL: That means the Primary Key constraint will not accept duplicate values in it.Įrror Code: 1062. When we try to execute the above SQL statement, it gives us the below error. INSERT INTO Employee (Id, Name, Email, Department) VALUES (1, ‘Anurag’, ‘IT’) Please try to execute the below insert statement to insert a duplicate record. Let us try to insert another employee with the same ID 1. In our Employee table, one record exists with the Id 1. Now let us see what happens when we try to insert a duplicate value into the Primary key column i.e. Adding Duplicate value in the Primary Key Column: Once you execute the above INSERT Statement, one record is inserted into the Employee table as expected. INSERT INTO Employee (Id, Name, Email, Department) VALUES (1, ‘Sambit’, ‘IT’) Let’s execute the following insert statement to insert a record into the Employee table Once you execute the above SQL Statement, it will give you the below output. let us verify the Employee table structure by executing the below SQL Statement. Here, we marked the Id column as the primary key. Now when you execute the above SQL code, it executed successfully and creates the Employee table. To overcome the above error, remove one primary key and execute the CREATE TABLE SQL statement as shown below. The error message clearly tells us that we are trying to create multiple primary keys which are not possible.Įrror Code: 1068. When you try to execute the above Create Table SQL query, you will get the following error. Please try to execute the below SQL Script where we used the Primary key on more than one column (Id and Email). In MySQL, a table can have only one primary. Understanding the Primary Key Constraint in MySQL: The Primary Key constraint in MySQL can be applied to numeric and string data types such as INT, VARCHAR, etc. using the primary key we can uniquely identify each record.Ī table should contain only 1 Primary Key which can be either on a single or multiple columns i.e. Using the primary key, we can enforce entity integrity i.e. That means it will not allow either NULL or Duplicate values into a column or columns on which the primary key constraint is applied. The Primary Key in MySQL is the combination of UNIQUE and NOT NULL Constraint. Please read our previous article where we discussed CHECK Constraint in MySQL. In this article, I am going to discuss PRIMARY KEY Constraint in MySQL with Examples. Data Structures and Algorithms Tutorialsīack to: MySQL Tutorials for Beginners and Professionals PRIMARY Key Constraint in MySQL with Examples.
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |