The primary key in SQL is a single, or a group of fields or columns that can uniquely identify a row in a table. Putting it simply, it is a column that accepts unique values for each row. Thus, whenever you use the INSERT INTO command to insert new values in a table, the value for the primary key column or columns
needs to be unique. The most significant advantages of a primary key are: The properties of each primary key column or columns are: There are two ways to create a primary key column;
through coding, or the GUI of the SQL Server Management Studio. You will go through both ways in this article, beginning with the Management Studio method. You can create a primary key column using the SQL Server Management Studio GUI with the following steps. 1. Open the database
and right-click on the table name where you want to set the primary key. 2. Next, right-click on the column name and select the set primary key option. 3. This will make a change, and a key will appear beside the column, confirming that the column is now a primary key column. You can
create a primary key column in a table using the primary key constraint. CREATE TABLE Students( ID INT PRIMARY KEY, Name VARCHAR(20), Email_ID NVARCHAR(25) ); INSERT INTO Students VALUES (1, 'George', 'ge.com'); INSERT INTO Students VALUES (2, 'David', 'da.com'); INSERT INTO Students VALUES (3, 'Aakash', 'aa.com'); SELECT * FROM Students;
Now, try inserting a duplicate value to
check if the primary key constraint is applied or not. INSERT INTO Students VALUES (2, 'Chris', 'ch.com'); SELECT * FROM Students; As you can see in the output, it did not complete the last INSERT INTO command as the ID value 2 was duplicate. As mentioned
earlier, you can create a primary key using CREATE TABLE and ALTER TABLE commands. You can also use it to create the primary key at both column and table levels. This section will look at how to use the CREATE TABLE command to create column and table level primary keys. Column level primary key means defining it only for a single column in the entire table. Using the same Student table reference with the same
values, create the primary key at column level and checks if it works as expected. MySQLFor defining a primary key at column level in MySQL, use the following code: CREATE TABLE Students( ID INT, Name VARCHAR(20), Email_ID NVARCHAR(25), PRIMARY KEY(ID) ); INSERT INTO Students VALUES (1, 'George', 'ge.com'); INSERT INTO Students VALUES (2, 'David', 'da.com'); INSERT INTO Students VALUES (3, 'Aakash', 'aa.com'); SELECT * FROM Students; Output:Now, you need to check if it’s implemented properly. INSERT INTO Students VALUES (2, 'Chris', 'ch.com'); SELECT * FROM Students; Output:SQL Server/Oracle/MS AccessFor defining a primary key at column level in SQL Server/Oracle/MS Access, use the following code: CREATE TABLE Students( ID INT PRIMARY KEY, Name VARCHAR(20), Email_ID NVARCHAR(25) ); INSERT INTO Students VALUES (1, 'George', 'ge.com'); INSERT INTO Students VALUES (2, 'David', 'da.com'); INSERT INTO Students VALUES (3, 'Aakash', 'aa.com'); SELECT * FROM Students; Output:INSERT INTO Students VALUES (2, 'Chris', 'ch.com'); SELECT * FROM Students; Output:Example: Using Primary Key at Table Level in SQL on Create TableTable level primary key is helpful if you want to define multiple columns as primary keys in a single table. It is also referred to as a composite primary key. For this example, you have to define the Email_ID column as the primary key and the ID column. The syntax to define it for MySQL, SQL Server, Oracle, and MS Access is: CREATE TABLE Students( ID INT, Name VARCHAR(20), Email_ID NVARCHAR(25), CONSTRAINT PK_Students PRIMARY KEY (ID, Email_ID) ); INSERT INTO Students VALUES (1, 'George', 'ge.com'); INSERT INTO Students VALUES (2, 'David', 'da.com'); INSERT INTO Students VALUES (3, 'Aakash', 'aa.com'); SELECT * FROM Students; Output:-- checking the implementation of the primary key INSERT INTO Students VALUES (2, 'Chris', 'ch.com'); SELECT * FROM Students; Output:As you can see in the output, the table still accepts the entry. Why do you think that is? That’s because the data in the Email_ID column was still different. Thus, for the system, it was two different rows with different values. Now, try to enter a row with the same values for ID and Email_ID and see what happens. INSERT INTO Students VALUES (2, 'Chris', 'da.com'); SELECT * FROM Students; Output:As expected, the system threw an error. Using SQL Primary Key on Alter TableThe ALTER TABLE command can create a primary key on the column of an already created table. You can yet again define a single column or multiple columns using the ALTER TABLE command. One thing to note while using the ALTER TABLE command is that while using it, you need to ensure that all the rows already inserted into the column should be unique. It is also important to ensure that the columns must be defined with a NOT NULL constraint; otherwise, the query will fail and throw an error. Example: Using Primary Key in SQL on Alter Table for Single ColumnYou can use the ALTER TABLE command with MySQL, SQL Server, Oracle, and MS Access. For this example, you will have to use the Oracle server. First, create the Students table defining no column as the primary key to describe it using the ALTER TABLE command. CREATE TABLE Students( S_ID NUMBER NOT NULL, Name VARCHAR2(20), Email_ID VARCHAR2(25) ); INSERT INTO Students VALUES (1, 'George', 'ge.com'); INSERT INTO Students VALUES (2, 'David', 'da.com'); INSERT INTO Students VALUES (3, 'Aakash', 'aa.com'); SELECT * FROM Students; Output:Now, use the ALTER TABLE command to make the ID column primary and insert a duplicate value. ALTER TABLE Students ADD PRIMARY KEY (S_ID); INSERT INTO Students VALUES (2, 'Chris', 'ch.com'); Output:As expected, the server did not accept the row because of the primary key infringement. Example: Using Primary Key in SQL on the Alter Table for Multiple ColumnsAgain, the syntax is the same for MySQL, SQL Server, Oracle, and MS Access. Here, use the Oracle server and use the ALTER TABLE command to name and create a primary key for the S_ID and Email_ID. Use the same code as above to create the table, and then add the following ALTER TABLE command and insert duplicate values. ALTER TABLE Students ADD CONSTRAINT PK_Students PRIMARY KEY (S_ID, Email_ID); Output:-- Adding duplicate value INSERT INTO Students VALUES (2, 'Chris', 'da.com'); Output:You got the error as you were supposed to. Remember, the server will consider it the same only if the values of both the columns are the same. How to Drop a Primary Key in SQL?You can quickly drop a primary key constraint in all the databases using ALTER TABLE command. However, the syntax for MySQL and SQL Server/Oracle/MS Access is different. Dropping Primary Key in SQL Server, Oracle, and MS AccessThe syntax to drop a primary key is: ALTER TABLE table_name DROP CONSTRAINT PRIMARY_KEY_NAME Let’s drop the primary key PK_Students we created for the Students table in the last section using the Oracle server. ALTER TABLE Students DROP CONSTRAINT PK_Students; Output:Now, insert the duplicate value that threw the error previously. INSERT INTO Students VALUES (2, 'Chris', 'da.com'); SELECT * FROM Students; Output:As you can see in the output, it inserted the row in the table despite having the same ID and Email_ID column values. Thus, you can conclude that the primary key SK_Students was dropped. Dropping Primary Key in MySQLThe syntax for dropping the primary key in MySQL is: ALTER TABLE table_name DROP CONSTRAINT PRIMARY KEY Use the same Students table you have been using until now and drop the primary key from the ID column. CREATE TABLE Students( ID INT, Name VARCHAR(20), Email_ID NVARCHAR(25), PRIMARY KEY(ID) ); INSERT INTO Students VALUES (1, 'George', 'ge.com'); INSERT INTO Students VALUES (2, 'David', 'da.com'); INSERT INTO Students VALUES (3, 'Aakash', 'aa.com'); SELECT * FROM Students; Output:Let’s drop the primary key now and insert a duplicate to see if it is dropped appropriately or not. ALTER TABLE Students DROP PRIMARY KEY; INSERT INTO VALUES (2, 'Chris', 'da.com'); SELECT * FROM Students; Output:Since the table accepted duplicate values for the ID column, you can confirm that it dropped the primary key. Gain expertise in the latest Business analytics tools and techniques with the Business Analyst Certification Program. Enroll now! Conclusion:In this article, you learned everything about the SQL primary key. You have gone through how to create it in different databases at different levels using both CREATE TABLE and ALTER TABLE command. This article also explored how to drop a primary key in multiple databases. The primary key is an essential constraint that will help you maintain the integrity and authenticity of databases. If you are keen on SQL and want to pursue a career in the field, you can opt for Simplilearn’s SQL Certification Training. The training provided all the learning materials and applied learning strategies on different types of SQL servers. Thus, it is adept at helping you excel in SQL database management. Have any questions for us? Leave them in the comments section of this article. Our experts will get back to you on the same, ASAP! Why is primary key necessary in a table?Every table can have (but does not have to have) a primary key. The column or columns defined as the primary key ensure uniqueness in the table; no two rows can have the same key. The primary key of one table may also help to identify records in other tables, and be part of the second table's primary key.
Why is primary key necessary in a table Class 7?The primary key is a specific column in the table that is used to identify each record or row separately. The primary key is needed to distinguish between the different entries in a table.
Why it is necessary to have a primary key in a table Class 8?What is the use of Primary Key? Ans. A Primary key is a check on the table that uniquely identifies each record on a table. It does not contain any duplicate data.
|