Home » SQL Tutorial » SQL Foreign Key Constraint

SQL Foreign Key Constraint

Summary : in this tutorial, you  will learn about the SQL foreign key and how to create a FOREIGN KEY constraint to enforce the relationship between tables.

Introduction to SQL foreign key constraint

A foreign key is a column or a group of columns that enforces a link between the data in two tables. In a foreign key reference, the primary key column (or columns) of the first table is referenced by the column (or columns) of the second table. The column (or columns) of the second table becomes the foreign key.

You use the FOREIGN KEY constraint to create a foreign key when you create or alter table. Let’s take a simple example to get a better understanding.

SQL FOREIGN KEY constraint examples

See the following projects and project_assignments tables:

Each project may have zero or more milestones while one milestone must belong to one and only one project. The application that uses these tables must ensure that for each row in the project_milestones table there exists the corresponding row in the projects table. In other words, a milestone cannot exist without a project.

Unfortunately, users may edit the database using client tool or if there is a bug in the application, a row might be added to the project_milestones table that does not correspond to any row in the projects table. Or user may delete a row in the projects table, leaving orphaned rows in the project_milestones table. This causes the application not to work properly.

The solution is to add an SQL FOREIGN KEY constraint to the project_milestones table to enforce the relationship between the projects and project_milestones tables.

You can create the FOREIGN KEY constraint when you create the table as follows:

The FOREIGN KEY clause promotes the project_id of the project_milestones table to become the foreign key that is referenced to the project_id of the projects table.

You can assign a name to a FOREIGN KEY constraint as follows:

fk_project is the name of the FOREIGN KEY constraint.

Adding FOREIGN KEY contraints to existing tables

To add a FOREIGN KEY constraint to existing table, you use the ALTER TABLE statement.

Suppose the project_milestones already exists without any predefined foreign key and you want to define a FOREIGN KEY constraint for the project_id column. To do so, you use the following ALTER TABLE statement:

Removing foreign key constraints

To remove a foreign key constraint, you also use the ALTER TABLE statement as follows:

If you are using MySQL, you can use a cleaner syntax as follows:

For example, to remove the fk_project foreign key constraint, you use the following statement:

In this tutorial, we have introduced you to the foreign key concept and shown you how to create foreign key using SQL FOREIGN KEY constraint.

This browser is no longer supported.

Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.

Primary and Foreign Key Constraints

  • 10 contributors

Primary keys and foreign keys are two types of constraints that can be used to enforce data integrity in SQL Server tables. These are important database objects.

Primary Key Constraints

A table typically has a column or combination of columns that contain values that uniquely identify each row in the table. This column, or columns, is called the primary key (PK) of the table and enforces the entity integrity of the table. Because primary key constraints guarantee unique data, they're frequently defined on an identity column.

When you specify a primary key constraint for a table, the Database Engine enforces data uniqueness by automatically creating a unique index for the primary key columns. This index also permits fast access to data when the primary key is used in queries. If a primary key constraint is defined on more than one column, values may be duplicated within one column, but each combination of values from all the columns in the primary key constraint definition must be unique.

As shown in the following illustration, the ProductID and VendorID columns in the Purchasing.ProductVendor table form a composite primary key constraint for this table. This makes sure that every row in the ProductVendor table has a unique combination of ProductID and VendorID . This prevents the insertion of duplicate rows.

A table can contain only one primary key constraint.

A primary key can't exceed 16 columns and a total key length of 900 bytes.

The index generated by a primary key constraint can't cause the number of indexes on the table to exceed 999 nonclustered indexes and 1 clustered index.

If clustered or nonclustered isn't specified for a primary key constraint, clustered is used if there's no clustered index on the table.

All columns defined within a primary key constraint must be defined as not null. If nullability isn't specified, all columns participating in a primary key constraint have their nullability set to not null.

If a primary key is defined on a CLR user-defined type column, the implementation of the type must support binary ordering.

Foreign Key Constraints

A foreign key (FK) is a column or combination of columns that is used to establish and enforce a link between the data in two tables to control the data that can be stored in the foreign key table. In a foreign key reference, a link is created between two tables when the column or columns that hold the primary key value for one table are referenced by the column or columns in another table. This column becomes a foreign key in the second table.

For example, the Sales.SalesOrderHeader table has a foreign key link to the Sales.SalesPerson table because there's a logical relationship between sales orders and salespeople. The SalesPersonID column in the SalesOrderHeader table matches the primary key column of the SalesPerson table. The SalesPersonID column in the SalesOrderHeader table is the foreign key to the SalesPerson table. By creating this foreign key relationship, a value for SalesPersonID can't be inserted into the SalesOrderHeader table if it doesn't already exist in the SalesPerson table.

A table can reference a maximum of 253 other tables and columns as foreign keys (outgoing references). SQL Server 2016 (13.x) increases the limit for the number of other tables and columns that can reference columns in a single table (incoming references), from 253 to 10,000. (Requires at least 130 compatibility level.) The increase has the following restrictions:

Greater than 253 foreign key references are only supported for DELETE DML operations. UPDATE and MERGE operations aren't supported.

A table with a foreign key reference to itself is still limited to 253 foreign key references.

Greater than 253 foreign key references aren't currently available for columnstore indexes, memory-optimized tables, Stretch Database, or partitioned foreign key tables.

Stretch Database is deprecated in SQL Server 2022 (16.x) and Azure SQL Database. This feature will be removed in a future version of the Database Engine. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

Indexes on Foreign Key Constraints

Unlike primary key constraints, creating a foreign key constraint doesn't automatically create a corresponding index. However, manually creating an index on a foreign key is often useful for the following reasons:

Foreign key columns are frequently used in join criteria when the data from related tables is combined in queries by matching the column or columns in the foreign key constraint of one table with the primary or unique key column or columns in the other table. An index enables the Database Engine to quickly find related data in the foreign key table. However, creating this index isn't required. Data from two related tables can be combined even if no primary key or foreign key constraints are defined between the tables, but a foreign key relationship between two tables indicates that the two tables have been optimized to be combined in a query that uses the keys as its criteria.

Changes to primary key constraints are checked with foreign key constraints in related tables.

Referential Integrity

Although the main purpose of a foreign key constraint is to control the data that can be stored in the foreign key table, it also controls changes to data in the primary key table. For example, if the row for a salesperson is deleted from the Sales.SalesPerson table, and the salesperson's ID is used for sales orders in the Sales.SalesOrderHeader table, the relational integrity between the two tables is broken; the deleted salesperson's sales orders are orphaned in the SalesOrderHeader table without a link to the data in the SalesPerson table.

A foreign key constraint prevents this situation. The constraint enforces referential integrity by guaranteeing that changes can't be made to data in the primary key table if those changes invalidate the link to data in the foreign key table. If an attempt is made to delete the row in a primary key table or to change a primary key value, the action fails when the deleted or changed primary key value corresponds to a value in the foreign key constraint of another table. To successfully change or delete a row in a foreign key constraint, you must first either delete the foreign key data in the foreign key table or change the foreign key data in the foreign key table, which links the foreign key to different primary key data.

Cascading Referential Integrity

By using cascading referential integrity constraints, you can define the actions that the Database Engine takes when a user tries to delete or update a key to which existing foreign keys point. The following cascading actions can be defined.

NO ACTION The Database Engine raises an error and the delete or update action on the row in the parent table is rolled back.

CASCADE Corresponding rows are updated or deleted in the referencing table when that row is updated or deleted in the parent table. CASCADE can't be specified if a timestamp column is part of either the foreign key or the referenced key. ON DELETE CASCADE can't be specified for a table that has an INSTEAD OF DELETE trigger. ON UPDATE CASCADE can't be specified for tables that have INSTEAD OF UPDATE triggers.

SET NULL All the values that make up the foreign key are set to NULL when the corresponding row in the parent table is updated or deleted. For this constraint to execute, the foreign key columns must be nullable. Can't be specified for tables that have INSTEAD OF UPDATE triggers.

SET DEFAULT All the values that make up the foreign key are set to their default values if the corresponding row in the parent table is updated or deleted. For this constraint to execute, all foreign key columns must have default definitions. If a column is nullable, and there's no explicit default value set, NULL becomes the implicit default value of the column. Can't be specified for tables that have INSTEAD OF UPDATE triggers.

CASCADE, SET NULL, SET DEFAULT and NO ACTION can be combined on tables that have referential relationships with each other. If the Database Engine encounters NO ACTION, it stops and rolls back related CASCADE, SET NULL and SET DEFAULT actions. When a DELETE statement causes a combination of CASCADE, SET NULL, SET DEFAULT and NO ACTION actions, all the CASCADE, SET NULL and SET DEFAULT actions are applied before the Database Engine checks for any NO ACTION.

Triggers and Cascading Referential Actions

Cascading referential actions fire the AFTER UPDATE or AFTER DELETE triggers in the following manner:

All the cascading referential actions directly caused by the original DELETE or UPDATE are performed first.

If there are any AFTER triggers defined on the affected tables, these triggers fire after all cascading actions are performed. These triggers fire in opposite order of the cascading action. If there are multiple triggers on a single table, they fire in random order, unless there's a dedicated first or last trigger for the table. This order is as specified by using sp_settriggerorder .

If multiple cascading chains originate from the table that was the direct target of an UPDATE or DELETE action, the order in which these chains fire their respective triggers is unspecified. However, one chain always fires all its triggers before another chain starts firing.

An AFTER trigger on the table that is the direct target of an UPDATE or DELETE action fires regardless of whether any rows are affected. There are no other tables affected by cascading in this case.

If any one of the previous triggers performs UPDATE or DELETE operations on other tables, these actions can start secondary cascading chains. These secondary chains are processed for each UPDATE or DELETE operation at a time after all triggers on all primary chains fire. This process may be recursively repeated for subsequent UPDATE or DELETE operations.

Performing CREATE, ALTER, DELETE, or other data definition language (DDL) operations inside the triggers may cause DDL triggers to fire. This may subsequently perform DELETE or UPDATE operations that start additional cascading chains and triggers.

If an error is generated inside any particular cascading referential action chain, an error is raised, no AFTER triggers are fired in that chain, and the DELETE or UPDATE operation that created the chain is rolled back.

A table that has an INSTEAD OF trigger can't also have a REFERENCES clause that specifies a cascading action. However, an AFTER trigger on a table targeted by a cascading action can execute an INSERT, UPDATE, or DELETE statement on another table or view that fires an INSTEAD OF trigger defined on that object.

The following table lists the common tasks associated with primary key and foreign key constraints.

Coming soon: Throughout 2024 we will be phasing out GitHub Issues as the feedback mechanism for content and replacing it with a new feedback system. For more information see: https://aka.ms/ContentUserFeedback .

Submit and view feedback for

Additional resources

Simple SQL Tutorials

Simple SQL Tutorials

Your guide to querying and developing SQL Server databases

SQL Server Primary Key: How it works and 6 rules you need to know

sql server primary key featured image

If you are new to SQL Server and the process of database design, you’ve probably heard of the need to create a primary key column in your tables. You’ve likely been told that “ every table needs a primary key “.

But do you understand why we need a primary key constraint in our tables? Do you really understand what a primary key constraint does ?

A proper understanding of primary key constraints is the first step towards understanding database normalization and design. It’s very important that you know why we need a primary key constraint on our tables and that you understand the limitations put in place on a column with a primary key constraint.

In this tutorial, we’ll discuss everything you need to know about the primary key constraint and give you several important rules you need to know when designing your tables in your database.

We’ll discuss these topics:

  • What is a primary key?
  • A discussion about database normalization
  • Syntax for creating a primary key constraint
  • The best primary key column is an automatic, ever-increasing integer
  • The 6 rules you should remember about SQL Server primary keys

Also, don’t forget to download your FREE Ebook :

FREE Ebook on SQL Server Constraints!

sql key assignment

This FREE Ebook contains absolutely everything you need to know about all the different constraints available to us in Microsoft SQL Server, including:

  • Primary Key constraints
  • Foreign Key constraints
  • Default constraints
  • Check constraints
  • Unique constraints

Everything found in this tutorial is thoroughly discussed in the Ebook. This Ebook will definitely be a great resource for you to keep and reference throughout your career as a data professional. Get it today!

Let’s get into it.

1. What is a primary key?

A primary key is a constraint put on a column or columns in a table to guarantee each row in a table is unique.

One aspect that makes a table relational is that it has unique rows. If there are two rows in a table with exactly the same data, that table is not   considered relational.

If we add a primary key constraint to a column in a table, it basically guarantees that each row in that table will be different from every other row in the table (even if it’s only by that one column value).

Again, in that case, the table will be considered  relational , which is what we want (Microsoft SQL Server is, after all, a Relational Database Management System ).

One last thing you should know is that a primary key column cannot contain any NULL values. Every row must have a non-NULL value for the primary key column.

2. A discussion about database normalization

To further understand why we need a primary key column on our tables, we need to quickly go over a few simple rules about database normalization . 

Normalization is basically a set of rules we apply to tables and columns in our database to maintain data integrity and reduce data redundancy .

The steps to normalizing a database are split into 5 stages (called “ normal forms “). The first normal form is where we understand why we need primary key constraints.

Part of the first normal form states: All rows in a table must be unique .

In other words, we can’t have two or more rows in a table with the exact same data. If each row in a table has a unique primary key value that no other row has, then we have satisfied that portion of the first normal form.

I have a full tutorial for beginners on the basics of table normalization and First Normal Form . Check it out here:

First Normal Form: An introduction to SQL table normalization

So again folks, if we want to set ourselves up for success, we need to make sure our tables always have a primary key constraint.

3. Syntax for creating a primary key constraint

Ok, now we have a better idea of why  we need a primary key constraint. Let’s talk about how  you would create one.

There are a few different ways we can create a primary key constraint when creating or modifying a table. We’ll go over each one.

1. Simply using the keywords “ PRIMARY KEY ” next to the column you want set to be the primary key.

This is the easiest way to set a primary key constraint on a specific column. Here’s an example of a Products table with a primary key constraint on the ProductID column:

Fairly straightforward, right?

When I run this statement, the Products table will be created and a primary key constraint will also be created. The constraint is actually named, and we can see it in the object explorer (seen in the Keys folder and not the Constraints folder):

sql server primary key object explorer big name

When we begin adding rows to this table, we need to make sure this ProductID column is given a unique value for every insert. If we don’t, SQL Server won’t let us add the row!

I’ll demonstrate. Let’s add a row, specifying a ProductID of 1 :

Then let’s check the data:

sql server primary key first insert

So far so good. Now let’s try to add the exact same row a second time.

We get an error message:

sql server primary key error message

Here’s that full message:

Violation of PRIMARY KEY constraint ‘PK__Products__B40CC6EDAE880115’. Cannot insert duplicate key in object ‘dbo.Products’. The duplicate key value is (1).

Folks, this is exactly what the primary key constraint is there for. It’s there to basically force each row to be unique . I can’t enter a second row that contains exactly the same primary key value   as an existing row!

But let’s try again with a different ProductID value:

Yep, that works:

sql server primary key results after third insert

All rows in this table are now unique , aren’t they? (say yes)

2. Specifying a constraint name

In the previous example, you might have noticed how the primary key constraint was given a very long name:  PK__Products__B40CC6EDAE880115

While that name is fine if you don’t really  care  about the name of your constraint, there is another way to create the primary key constraint if you do care about the name.

Here is an example:

Next to the column we want to use as the primary key, we outline the CONSTRAINT keyword, followed by whatever name we want to give the constraint, followed by the  kind  of constraint we want (in our case, a PRIMARY KEY constraint).

If we look at the constraint in object explorer, we see it was given the name!

sql server primary key name of constraint

3. Creating the primary key constraint after the column list

The final way we can create a SQL Server primary key constraint is by outlining the constraint after the column list, instead of directly next to the column we want to use as the primary key.

Here’s what it looks like:

Notice it’s very similar to the previous example. We can still specify the name we want to give the constraint, which is great. But notice we needed to specify which column we wanted to set the constraint on.

This is how you would create what’s called a composite primary key (a primary key composed of multiple columns). For example, if we wanted to say our primary key was the combination of the ProductID and the ProductName , we could do that:

Easy peasy .

4. The best primary key column is an automatic, ever-increasing integer

In the previous point, we demonstrated a problem that can happen when adding rows to a table that has a primary key constraint. Remember when we tried to add two rows with the same ProductID value?:

It was easy enough to see what we did wrong in our second insert. Of course, we were trying to specify a ProductID of 1 when there was already a ProductID of 1 in the table!

It was easy enough to see the mistake and change our second insert to use a different primary key. But what if there were tens of thousands of rows in the database ? Do you think it would be difficult to tell if the number we want to use is already being used? (say yes).

The IDENTITY property

Luckily, there is an easy way to basically let SQL Server handle the creation of integer values for a column. We can add the IDENTITY property to a column that we want to set as the primary key:

Now, SQL Server will handle the task of adding a new value to the ProductID column when we insert new rows. In the INSERT statement, we would NOT specify a value for that column:

Let’s check the data:

sql server primary key identity

Awesome, now that’s one less thing we need to do!

Special note: The IDENTITY property does not enforce uniqueness .

The property simply keeps track of the last integer value it generated for a table, then increments that number each time an INSERT is performed. It does  not  check to see if a value was generated previously. That uniqueness validation is handled by the primary key constraint , of course.

It just so happens that as long as you don’t f*ck with it, the IDENTITY values being generated ought to be unique anyway.

Check out the full tutorial on the IDENTITY property to learn more:

IDENTITY Column in SQL Server: Everything you need to know

A clustered index is created on the primary key column(s) automatically.

Another reason an integer is great to have as our primary key column is because behind the scenes, SQL Server is going to automatically create a clustered index for our table, where the clustered index key is the primary key column.

Let’s think about the clustered index data structure . It orders data on disk in order by the clustered index key. So if the ProductID column becomes the clustered index key, we can say the data is physically ordered on disk in order by ProductID.

What if we add a new row to the table? Well, the new ProductID would just get tacked on to the end of the clustered index data structure, which is easy to do. If we add another row, it gets tacked on to the end again. So on and so forth. Tacking onto the end is easy .

But think about what would happen if we didn’t have a ProductID column, and decided to use the ProductName column as our primary key.

Whenever we add a new row, the ProductName  might  get tacked on to the end, but there’s a better chance it will need to go somewhere in the middle . That means data shifting needs to occur, which takes time.

Data shifting and foreign key references

Think about if a ProductName needs to change. Again, data shifting might need to happen. But also, if the column is referenced as a Foreign Key in some other table, any references to the ProductName will need to be updated there too 🙁

If we have a simple integer column as a primary key, maybe we don’t really care  what the value is, and therefore probably won’t need to change it. No data shifting, and no foreign key reference changes.

Lastly, the INT data type is relatively small, only using 4 byte  to store a value. Compare that to our ProductName column that uses 20 bytes to store a value.

ProductID is a surrogate key

A primary key like our ProductId is what’s known as a surrogate key . It’s basically a key created specifically because it’s easier than using any natural keys . Of course, a natural key is something that naturally uniquely identifies an entity, like a Social Security Number or a VIN number.

If we had an Employees table, for example, with a surrogate key that is an EmplID column, and still had a SSN column, that SSN column can be referred to as a candidate key . A candidate key is a column we recognize should have unique, non-NULL values, but it’s  not labeled as the primary key because of reasons already discussed (large data type, data shifting for new and updated rows)

In summary, a surrogate key made up with an INT data type and the IDENTITY property makes for an excellent primary key column!

5. The 6 rules you should know about SQL Server primary keys

We’ll finish this tutorial with a handful of rules you need to remember when working with SQL Server primary key constraints. Some of these rules we have discussed, but other’s we have not.

1. A SQL Server primary key column cannot contain duplicate values

No two rows can contain the same value in a primary key column. This goes back to relational database theory. In order for a table to be considered relational , all rows must be unique!

2. A table can only have one primary key constraint

This is one of those rules you need to remember. I think this ties back to the fact that a clustered index is automatically created when you create a primary key constraint. Data on disk can only be ordered in one way, which means we can only have one clustered index on a table. Ergo, we can only have one primary key constraint on a table!

3. A primary key column cannot contain any NULL values

This is yet another thing to remember, and it deals with relational database theory. Primary key columns must contain non-NULL values.

4. A unique key constraint is different from a primary key constraint

A unique key constraint makes sure that all the values in it’s column are unique. It’s great if you want a column to always contain unique values, but you don’t necessarily want to set that column as the primary key. For example, it would be great to set a unique constraint on a SSN  column.

Some of the rules that apply to primary key constraints do not  apply to unique key constraints. Unique key constraints differ from primary key constraints in two ways:

  • A unique key column can contain one (and only one ) NULL value.
  • You can have more than one unique key constraint on a table. You can add two or three (or more!) unique constraints to columns in a table if you want to.

Check out the full tutorial on unique constraints to learn more:

SQL Server Unique Index: Everything you need to know

5. the clustered index will be labeled as unique to enforce the uniqueness of the values in the column..

If you look at the properties of the index that was made when the primary key constraint was created, it will be labeled as unique . Here’s our clustered index created from our primary key constraint:

sql server primary key clustered index screenshot

6. You can add a primary key constraint to an already-existing table by using the ALTER TABLE statement

Let’s drop the Products table and re-create it without a primary key constraint:

Ok, so let’s say we meant to add a PRIMARY KEY constraint to the ProductID column.

First, we need to remember one of the rules of primary key constraints: It cannot contain NULL values.

This means we first need to label the ProductID column as NOT NULL . That’s easy enough to do with an ALTER TABLE …. ALTER COLUMN statement:

Ok, so now that the column follows the rules for primary key constraints, we can run another ALTER TABLE statement to add the PRIMARY KEY constraint:

So we use the ADD CONSTRAINT keywords, followed by the name we want to give the constraint, followed by the  kind  of constraint, followed by the column(s) we want to be in the primary key constraint.

Easy peasy !

Next Steps:

Leave a comment if you found this tutorial helpful!

If you found this tutorial helpful, make sure you…

Download your FREE SQL Server Constraints Ebook!

Now that you know a thing or two about the Primary Key constraint, you should definitely check out the beloved cousin of the Primary Key constraint: The Foreign Key Constraint. 

Check out the full tutorial here:

SQL Server Foreign Key: Everything you need to know

I also touched on the difference between a unique constraint and a primary key constraint. While they are similar, there are some slight differences you need to know. Learn more about unique constraints by reading my full tutorial:

Thank you very much for reading!

Make sure you subscribe to my newsletter to receive special offers and notifications anytime a new tutorial is released!

If you have any questions, or if you are struggling with a different topic related to SQL Server, I’d be happy to discuss it. Leave a comment or visit my contact page and send me an email!

Related Post

datetime2 featured image

DATETIME2 Data Type: Explained

charlize theron null

SQL Server NULL: Are you making these 7 mistakes?

SQL VARCHAR featured image

SQL VARCHAR Data Type: The Ultimate Guide for Beginners

2 thoughts on “ sql server primary key: how it works and 6 rules you need to know ”.

What an article! You have outdone yourself once again Josh. Wouldn’t it be easier if we can add a constraint at the table level as CONSTRAINT PRIMARY KEY AS constraint_name? I’ll make sure to recommend your papers to everybody in the process of learning SQL!

Thanks for the compliment, Charles! Well we need to associate the PRIMARY KEY constraint with a column. That is how SQL Server knows to order the content of the table on disk. And if we guarantee values in a specific column are unique, we can say every row is unique.

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Save my name, email, and website in this browser for the next time I comment.

Learn SQL practically and Get Certified .

Popular tutorials, learn sql interactively.

Learn Python practically and Get Certified .

Popular Examples

  • SQL Introduction

SQL SELECT (I)

  • SQL AND, OR, NOT
  • SQL SELECT DISTINCT
  • SQL SELECT AS
  • SQL LIMIT, TOP, FETCH FIRST
  • SQL IN Operator
  • SQL BETWEEN Operator
  • SQL IS NULL and NOT NULL
  • SQL MIN() and MAX()
  • SQL COUNT()
  • SQL SUM() and AVG()

SQL SELECT (II)

  • SQL ORDER BY
  • SQL GROUP BY
  • SQL Wildcards
  • SQL Subquery
  • SQL ANY and ALL
  • SQL INNER JOIN
  • SQL LEFT JOIN
  • SQL RIGHT JOIN
  • SQL FULL OUTER JOIN

SQL DATABASE & TABLE

  • SQL Create Database
  • SQL Create Table
  • SQL Drop Database
  • SQL Drop Table
  • SQL Alter Table
  • SQL Backup Database

SQL Insert, Update and Delete

  • SQL Insert Into
  • SQL Select Into
  • SQL Insert Into Select
  • SQL Delete and Truncate Rows

SQL Constraints

  • SQL Not Null Constraint
  • SQL Unique Constraints
  • SQL Primary Key
  • SQL Foreign Key
  • SQL Default
  • SQL Create Index

SQL Additional Topics

  • SQL Data Types
  • SQL Date and Time
  • SQL Operators
  • SQL Comments
  • SQL Stored Procedures
  • SQL Injection

SQL Tutorials

SQL UNIQUE Constraint

SQL NOT NULL Constraint

  • SQL DEFAULT Constraint

SQL CREATE INDEX

  • SQL CHECK Constraint

SQL PRIMARY KEY Constraint

In SQL, the PRIMARY KEY constraint is used to uniquely identify rows. It is a combination of NOT NULL and UNIQUE constraints i.e. it cannot contain duplicate or NULL values.

Here, the college_id column is the PRIMARY KEY . This means that the values of this column must be unique and it cannot contain NULL values.

Note that the above code works in all major database systems. However, some databases may have a different syntax.

  • SQL PRIMARY KEY Syntax

The syntax of the SQL PRIMARY KEY constraint is:

  • table_name is the name of the table to be created
  • column1 is the name of the column where the PRIMARY KEY constraint is to be defined
  • constraint_name is the arbitrary name given to the constraint
  • [...] signifies that the code inside is optional.

Note: Although naming a constraint using [CONSTRAINT constraint_name] is optional, doing so makes it easier to make changes to and delete the constraint.

  • Primary Key Error

In SQL, we will get an error If we try to insert NULL or duplicate values in the primary key column.

NOT NULL Constraint Error

We get this error when we supply the primary key with a NULL value. This is because primary keys need to obey the NOT NULL constraint. For example,

Here, the SQL command gives us an error because we have supplied a NULL value to the primary key college_id in the Colleges table.

Fix the NOT NULL Constraint Error

Here, the SQL command runs without errors because we have supplied the value 1 to the primary key i.e. college_id .

UNIQUE Constraint Error

We get this error when we supply duplicate values to the primary key, which violates its UNIQUE constraint. For example,

Here, the SQL command gives us an error because we have inserted the duplicate value 1 into the primary key college_id .

Fix the UNIQUE Constraint Error

Here, the SQL command runs without errors because we have supplied unique values 1 and 2 to college_id .

Note: There can only be one primary key in a table. However, that single primary key can contain multiple columns.

  • Primary Key With Multiple Columns

A primary key may also be made up of multiple columns. For example,

Here, the PRIMARY KEY constraint named CollegePK is made up of the college_id and college_code columns.

This means that the combination of college_id and college_code must be unique and these two columns cannot contain NULL values.

  • Primary Key Constraint With Alter Table

We can also add the PRIMARY KEY constraint to a column in an existing table using the ALTER TABLE command. For example,

For a Single Column

Here, the SQL command adds the PRIMARY KEY constraint to the college_id column in the existing Colleges table.

For Multiple Columns

Here, the SQL command adds the PRIMARY KEY constraint to the college_id and college_id columns in the existing Colleges table.

Note: This command is not supported by our online SQL editor as it is based on SQLite.

  • Auto Increment Primary Key

It is a common practice to automatically increase the value of the primary key whenever a new row is inserted. For example,

  • Remove Primary Key Constraint

We can remove the PRIMARY KEY constraint in a table using the DROP clause. For example,

SQL Server, Oracle

Here, the SQL command removes the PRIMARY KEY constraint from the Colleges table.

  • SQL CREATE TABLE
  • SQL INSERT INTO
  • SQL ALTER TABLE
  • SQL FORIEGN KEY

Table of Contents

  • Introduction

Sorry about that.

Related Tutorials

Programming

  • SQL Server training
  • Write for us!

Emil Drkusic

Learn SQL: Practice SQL Queries

Today is the day for SQL practice #1. In this series, so far, we’ve covered most important SQL commands ( CREATE DATABASE & CREATE TABLE , INSERT , SELECT ) and some concepts ( primary key , foreign key ) and theory ( stored procedures , user-defined functions , views ). Now it’s time to discuss some interesting SQL queries.

Let’s take a quick look at the model we’ll use in this practice.

SQL Practice - the data model we'll use in the article

You can expect that in real-life situations (e.g., interview), you’ll have a data model at your disposal. If not, then you’ll have the description of the database (tables and data types + additional description of what is stored where and how the tables are related).

The worst option is that you have to check all the tables first. E.g., you should run a SELECT statement on each table and conclude what is where and how the tables are related. This won’t probably happen at the interview but could happen in the real-life, e.g., when you continue working on an existing project.

Before We Start

The goal of this SQL practice is to analyze some typical assignments you could run into at the interview. Other places where this might help you are college assignments or completing tasks related to online courses.

The focus shall be on understanding what is required and what is the learning goal behind such a question. Before you continue, feel free to refresh your knowledge on INNER JOIN and LEFT JOIN , how to join multiple tables , SQL aggregate functions , and the approach to how to write complex queries . If you feel ready, let’s take a look at the first 2 queries (we’ll have some more in upcoming articles). For each query, we’ll describe the result we need, take a look at the query, analyze what is important for that query, and take a look at the result.

SQL Practice #1 – Aggregating & LEFT JOIN

Create a report that returns a list of all country names (in English), together with the number of related cities we have in the database. You need to show all countries as well as give a reasonable name to the aggregate column. Order the result by country name ascending.

Let’s analyze the most important parts of this query:

  • We’ve used LEFT JOIN ( LEFT JOIN city ON country.id = city.country_id ) because we need to include all countries, even those without any related city
  • We must use COUNT(city.id) AS number_of_cities and not only COUNT(*) AS number_of_cities because COUNT(*) would count if there is a row in the result (LEFT JOIN creates a row no matter if there is related data in other table or not). If we count the city.id , we’ll get the number of related cities
  • The last important thing is that we’ve used GROUP BY country.id, country.country_name_eng instead of using only GROUP BY country.country_name_eng . In theory (and most cases), grouping by name should be enough. This will work OK if the name is defined as UNIQUE. Still, including a primary key from the dictionary, in cases similar to this one, is more than desired

You can see the result returned in the picture below.

combining LEFT JOIN with aggregate function

SQL Practice #2 – Combining Subquery & Aggregate Function

Write a query that returns customer id and name and the number of calls related to that customer. Return only customers that have more than the average number of calls of all customers.

The important things I would like to emphasize here are:

  • Please notice that we’ve used aggregate functions twice, once in the “main” query, and once in the subquery. This is expected because we need to calculate these two aggregate values separately – once for all customers (subquery) and for each customer separately (“main” query)
  • The aggregate function in the “main” query is COUNT(call.id) . It’s used in the SELECT part of the query, but we also need it in the HAVING part of the query (Note: HAVING clause is playing the role of the WHERE clause but for aggregate values)
  • Group is created by id and customer name. These values are the ones we need to have in the result
  • In the subquery, we’ve divided the total number of rows ( COUNT(*) ) by the number of distinct customers these calls were related to ( COUNT(DISTINCT customer_id) ). This gave us the average number of calls per customer
  • The last important thing here is that we used the CAST operator ( CAST(… AS DECIMAL(5,2)) ). This is needed because the final result would probably be a decimal number. Since both COUNTs are integers, SQL Server would also return an integer result. To prevent this from happening, we need to CAST both divider and the divisor as decimal numbers

Let’s take a look at what the query actually returned.

SQL Practice - the result returned by the subquery using aggregate function

In today’s SQL practice, we’ve analyzed only two examples. Still, these two contain some parts you’ll often meet at assignments – either in your work, either in a testing (job interview, college assignments, online courses, etc.). In the next part, we’ll continue with a few more interesting queries that should help you solve problems you might run into.

Table of contents

  • Recent Posts

Emil Drkusic

  • Learn SQL: How to prevent SQL Injection attacks - May 17, 2021
  • Learn SQL: Dynamic SQL - March 3, 2021
  • Learn SQL: SQL Injection - November 2, 2020

Related posts:

  • Learn SQL: How to Write a Complex SELECT Query
  • Learn SQL: Join multiple tables
  • Learn SQL: Aggregate Functions
  • Learn SQL: Set Theory
  • Top SQL Server Books

Home » Oracle Basics » Oracle PRIMARY KEY

Oracle PRIMARY KEY

Summary : in this tutorial, you will learn how to use Oracle PRIMARY KEY constraint to manage the primary key of a table.

Introduction to the primary key

A primary key is a column of a combination of columns in a table that uniquely identifies a row in the table.

The following are rules that make a column a primary key:

  • A primary key column cannot contain a NULL value or an empty string.
  • A primary key value must be unique within the entire table.
  • A primary key value should not be changed over time.

According to these rules, the following are the recommendations for the primary keys:

  • First, the primary key should be meaningless. Sometimes, you may want use meaningful data, which considers being unique, for the primary keys e.g., social security number (SSN), vehicle identification number (VIN), email, and phone number. However, you don’t know when the email or phone number changes or is reused by another person. In such cases, it will create many data problems. In the database world, the artificial keys are known as surrogate keys which are as opposed to natural primary keys.
  • Second, the primary keys should be compact. The primary keys typically are numeric because Oracle typically processes numbers faster than any other data types.

To create a primary key in a table, you use the PRIMARY KEY constraint.

Oracle PRIMARY KEY constraint examples

Typically, you create a primary key for a table when you create that table. In addition, you can add a primary key to a table after the fact by using the ALTER TABLE statement.

Creating a primary key that consists of one column

The following CREATE TABLE statement creates the purchase_orders table:

The purchase_orders table has four columns purchase order number ( po_nr ), vendor id ( vendor_id ), purchase order status ( po_status ), and the timestamp ( created_at ) of which the purchase order is created.

In this table, defined the po_nr column as the primary key by using the PRIMARY KEY clause.

Note that the PRIMARY KEY clause implicitly makes the po_nr column NOT NULL so you don’t have to define the column like:

The PRIMARY KEY constraint in this example is an inline constraint because it is on the same line as the po_nr column.

Consider the following statement.

This example used the PRIMARY KEY constraint as the table constraint. Notice the following clause:

In addition, we explicitly assigned the PRIMARY KEY constraint a name pk_purchase_orders .

Creating a primary key that consists of multiple columns

The following statement creates the purchase order line items table:

In this example, the primary key of the purchase_order_items table consists of two columns: po_nr and item_nr . It means that the combination of values of these columns uniquely identifies a purchase order line item.

This example did not use the CONSTRAINT clause to explicitly assign the PRIMARY KEY constraint a name. Therefore, Oracle implicitly assigned the primary key constraint a system-generated name such as SYS_C0010617 .

Adding a primary key to a table

Sometimes, you may want to add a primary key constraint to an existing table. To do it, you use the ALTER TABLE statement as follows:

The following example creates the vendors table first and then adds a primary key constraint to it:

Dropping an Oracle  PRIMARY KEY constraint

You will rarely drop a PRIMARY KEY constraint from a table. If you have to do so, you use the following ALTER TABLE statement.

For example, you can drop the primary key constraint of the vendors table as follows:

It is possible to use the following statement to drop the primary key of a table:

For example:

Enable / Disable an Oracle  PRIMARY KEY constraint

To improve the performance when loading a large amount of data into a table or updating mass data, you can temporarily disable the PRIMARY KEY constraint.

To disable a PRIMARY KEY constraint of a table, you use the ALTER TABLE statement:

For example, to disable the primary key constraint of the purchase_orders table, you use the following statement:

To enable a primary key constraint, you use the following ALTER TABLE statement:

The following example enables the PRIMARY KEY constraint of the purchase_orders table:

In this tutorial, you have learned how to use Oracle PRIMARY KEY constraint to create, add, disable, enable, and drop a primary key of a table.

10 Beginner SQL Practice Exercises With Solutions

Author's photo

  • online practice
  • sql practice

Solve these ten SQL practice problems and test where you stand with your SQL knowledge!

This article is all about SQL practice. It’s the best way to learn SQL. We show you ten SQL practice exercises where you need to apply essential SQL concepts. If you’re an SQL rookie, no need to worry – these examples are for beginners.

Use them as a practice or a way to learn new SQL concepts. For more theoretical background and (even more!) exercises, there’s our interactive SQL Basics course. It teaches you how to select data from one or more tables, aggregate and group data, write subqueries, and use set operations. The course comprises 129 interactive exercises so there is no lack of opportunities for SQL practice, especially if you add some of the 12 ways of learning SQL online to it.

Speaking of practice, let’s start with our exercises!

The Dataset

Exercise 1: selecting all columns from a table, exercise 2: selecting a few columns from a table, exercise 3: selecting a few columns and filtering numeric data in where, exercise 4: selecting a few columns and filtering text data in where, exercise 5: selecting a few columns and filtering data using two conditions in where, exercise 6: filtering data using where and sorting the output, exercise 7: grouping data by one column, exercise 8: grouping data by multiple columns, exercise 9: filtering data after grouping, exercise 10: selecting columns from two tables.

The question is always where to find data for practicing SQL. We’ll use our dataset for all exercises. No need to limit yourself to this, though – you can find other free online datasets for practicing SQL .

Our dataset consists of two tables.

The table distribution_companies lists movie distribution companies with the following columns:

  • id – The ID of the distribution company. This is the primary key of the table.
  • company_name – The name of the distribution company.

The table is shown below.

The second table is movies . These are the columns:

  • id – The ID of the movie. This is the primary key of the table.
  • movie_title – The movie title.
  • imdb_rating – The movie rating on IMDb.
  • year_released – The year the movie was released.
  • budget – The budget for the movie in millions of dollars.
  • box_office – The earnings of the movie in millions of dollars.
  • distribution_company_id – The ID of the distribution company, referencing the table distribution_companies (foreign key).
  • language – The language(s) spoken in the movie.

Exercise: Select all data from the table distribution_companies .

Solution explanation: Select the data using the SELECT statement. To select all the columns, use an asterisk ( * ). The table from which the data is selected is specified in the FROM clause.

Solution output:

Exercise: For each movie, select the movie title, the IMDb rating, and the year the movie was released.

Solution explanation: List all the columns needed ( movie_title , imdb_rating , and year_released ) in the SELECT statement, separated by the comma. Reference the table movies in the FROM clause.

Exercise: Select the columns movie_title and box_office from the table movies . Show only movies with earnings above $300 million.

Solution explanation: List the columns in SELECT and reference the table in FROM . Use a WHERE clause to filter the data – write the column box_office and use the ‘greater than’ operator ( > ) to show only values above $300 million.

Exercise: Select the columns movie_title , imdb_rating , and year_released from the table movies . Show movies that have the word ‘Godfather’ in the title.

Solution explanation: List the columns in SELECT and reference the table in the FROM clause. Use a WHERE clause to filter the data. After writing the column name, use the LIKE logical operator to look for ‘Godfather’ in the movie title, written in single quotes. To find the word anywhere in the movie title, place the wildcard character ( % ) before and after the word.

Exercise: Select the columns movie_title , imdb_rating , and year_released from the table movies . Show movies that were released before 2001 and had a rating above 9.

Solution explanation: List the columns in SELECT and reference the table in FROM . Set the first condition that the year released is before 2001 using the ‘less than’ ( < ) operator. To add another condition, use the AND logical operator. Use the same logic as the first condition, this time using the ‘greater than’ operator with the column imdb_rating .

Exercise: Select the columns movie_title , imdb_rating , and year_released from the table movies . Show movies released after 1991. Sort the output by the year released in ascending order.

Solution explanation: List the columns in SELECT and reference the table in FROM . Filter the data with WHERE by applying the ‘greater than’ operator to the column year_released . To sort the data, use an ORDER BY clause and write the column name by which you wish to sort. The type of sorting is specified by writing ASC (ascending) or DESC (descending). If the type is omitted, the output is sorted in ascending order by default.

Exercise: Show the count of movies per each language category.

Solution explanation: Select the column language from the table movies . To count the number of movies, use the aggregate function COUNT() . Use the asterisk ( * ) to count the rows, which equals the count of movies. To give this column a name, use the AS keyword followed by the desired name. To show the count by language, you need to group the data by it, so write the column language in the GROUP BY clause.

Exercise: Show the count of movies by year released and language. Sort results by the release date in ascending order.

Solution explanation: List the columns year_released and language from the table movies in SELECT . Use COUNT(*) to count the number of movies and give this column a name using the AS keyword. Specify the columns by which you want to group in the GROUP BY clause. Separate each column name with a comma. Sort the output using ORDER BY with the column year_released and the ASC keyword.

Exercise: Show the languages spoken and the average movie budget by language category. Show only the languages with an average budget above $50 million.

Solution explanation: Select the column language from the table movies . To compute the average budget, use the aggregate function AVG() with the column budget in parentheses. Name the column in the output by using the AS keyword. Group the data by rating using GROUP BY . To filter the data after grouping, use a HAVING clause. In it, use the same AVG() construct as in SELECT and set the values to be above 50 using the ‘greater than’ operator.

Exercise: Show movie titles from the table movies , each with the name of its distribution company.

Solution explanation: List the columns movie_title and company_name in SELECT . In the FROM clause, reference the table distribution_companies . Give it an alias dc to shorten its name for use later. The AS keyword is omitted here; you may use it if you wish. To access the data from the other table, use JOIN (it may also be written as INNER JOIN ) and write the table name after it. Give this table an alias also. The join used here is an inner type of join; it returns only the rows that match the joining condition specified in the ON clause. The tables are joined where the column id from the table distribution_companies is equal to the column distribution_company_id from the table movies . To specify which column is from which table, use the corresponding alias of each table.

That Was Fun! Now, Time to Do SQL Practice on Your Own!

These ten SQL practice exercises give you a taste of what practicing SQL looks like. Whether you are at the beginner, intermediate, or advanced level, it’s the same. What changes is the complexity of the problems you solve and of the code you write.

Look for more challenges in the SQL Basics course and the Monthly SQL Practice track. Both are excellent for your SQL practice online. This is true, especially if you do not have an opportunity to use SQL on a daily basis in your job.

So, don’t try to test how long it takes to forget what you once knew in SQL! Use every opportunity to solve as many SQL practice problems as possible.

You may also like

sql key assignment

How Do You Write a SELECT Statement in SQL?

sql key assignment

What Is a Foreign Key in SQL?

sql key assignment

Enumerate and Explain All the Basic Elements of an SQL Query

SSRS 2022 Install, Setup and Configuration

By: Joe Gavin   |   Updated: 2024-02-21   |   Comments   |   Related: > Reporting Services Installation

SQL Server Reporting Services (SSRS) is still one of the major components of the Microsoft Business Intelligence (MSBI) stack used to generate interactive and automated reports. It's part of Microsoft SQL Server services, along with SQL Server Analysis Services (SSAS) and SQL Server Integration Services (SSIS).

SSRS 2022 includes enhanced Windows Narrator support, security enhancements, browser performance improvements, and accessibility bug fixes.

The SSRS installation process has changed slightly since it was first released in 2004 as an add-on to SQL Server 2000. We saw in a previous tip: SSRS Install, Setup and Configuration , where the SSRS installation used to be part of the SQL Server installer up through SQL Server 2016. As of SQL Server 2017, the installation now has its own downloadable installer. There are only some minor differences between installing SSRS 2019 and SSRS 2022, but knowing where to get the installer and what to expect before you go through the installation process is helpful. This tip will walk through the various screens and options for the installation process.

We'll look at the minimum hardware and software requirements, see where to get the installer, and walk through each step of installing and configuring a new installation of SSRS 2022. The installation documented here was done on a SQL Server 2022 running on Windows Server 2022.

SSRS 2022 Requirements

You'll likely have more resources than this, but the following are the minimum requirements for installing SSRS 2022:

  • 6 GB of available hard drive space
  • 512 MB (1 GB is recommended)
  • 1 GB (4 GB recommended)
  • X64: AMD Opteron, AMD Athlon 64, Intel Xeon with Intel EM64T support, Intel Pentium IV with EM64T support
  • 1.4 GHz (2 GHz recommended)
  • Windows Server 2016/2019/2022, Windows 10 / 11
  • SQL Server Database Engine 2014 SP3 or later
  • .NET Framework 4.8 or later

SSRS 2022 Installation

Download the installer.

Go to Microsoft SQL Server 2022 Reporting Services Installer Download

  • Click Download .

Download SSRS 2022

Retrieve and Run Installer

  • Run SQLServerReportingServices.exe from your Download folder.

Download folder

Welcome Screen

The installer opens, and you're presented with one option on the Welcome screen.

  • Install Reporting Services.

Welcome Screen

Choose Edition and Install

There are four edition options for SSRS. The first three do not require a Product Key:

  • Evaluation Edition – Full edition that is good for 180 days.
  • Developer Edition – Full edition that can be used for non-production, e.g., test, development, training, or demonstration purposes.
  • Express Edition – Limited edition with fewer features than the full edition.
  • Paid Edition - The fourth option requires a product key. Click Learn more , which will take you to Find the product key for SQL Server Reporting Services .

Install Paid Edition

  • Click the Enter the product key radio button and enter the key in the box.
  • Click Next .

Choose Edition

Review Licensing Terms

  • Read and accept license terms by checking the 'I accept the license terms' checkbox.

License Terms

Install Database Engine

Install SSRS

Specify Install Location

  • Choose the drive and directory to install SSRS.
  • Click Install .

Installation Location

The SSRS application has been installed, and now it's time to configure it.

Configure SSRS

  • Click Configure report server , which runs RSConfigTool.exe to open the Report Server Configuration Manager.

Configure SSRS

Connect to the SSRS Server

  • Click Connect .

Connect to SSRS

Configure Service Account

If the SSRS server requires access to remote servers, a Windows domain account can be specified to run the SSRS service.

  • Click on Service Account in the Report Server Configuration Manager.
  • Select the 'Use another account' radio button. Enter the domain account information and password.
  • Click Apply .

Configure Service Account

Configure SSRS Databases

Here, we configure the SSRS databases.

  • Click Database in the Report Server Configuration Manager.
  • Click Change Database .

Databases

Create New Database

The new SSRS installation can be pointed to an existing set of SSRS databases. However, since this is a new installation, the default 'Create a new report server database' is left selected, and the installer will create the new databases.

Create Databases

Test the Connection

We must test the connection to the SQL Server to ensure connectivity before attempting to create the SSRS databases.

  • Click Test Connection .
  • If the connection is successful, click OK .

If the connection is unsuccessful, verify that the SQL Server service is running and accepting connections, and then run the test again.

Test Connection

Name the Database

Unless a name other than the default database names must be used, leave the defaults as ReportServer and ReportServerTemp.

Database Names

Specify Credentials

Credentials

Review Summary

Review the install configuration on the Summary screen.

  • Click Next if all information is correct.

Summary

Progress and Finish

Verify all tasks completed successfully.

  • If all tasks are successful, click Finish .

Progress and Finish

At this point, we have the SSRS service installed and databases created.

It's time to create the Web Service and the Web Portal.

Create the SSRS Web Service

  • Click on Web Service URL .

Web Service URL

Validate Web Service

Open the Report Server Web Service URL to validate. There are no folders shown because no reports have been deployed yet.

Web Service

Create Web Portal

  • Click Web Portal URL .

Web Portal URL

Validate Web Portal

To validate, open the Report Server Web Portal URL. Again, the root folder is empty because no reports have been deployed yet.

Web Portal

We now have a fully functioning SSRS server that's ready for reports to be deployed to it.

Email Settings

If Subscriptions to email reports will be created, we'll need to configure the email settings.

  • Click E-mail Settings .
  • Enter a Sender Address in the form of [email protected] .
  • Enter the SMTP Server name.
  • Select the Authentication dropdown menu to enter the account and password information if your SMTP server requires authentication.

E-mail Settings

Email has been configured.

  • Click Exit .

Exit

You may run into failures, sometimes sporadically, related to TLS 1.2, where subscriptions are not sending emails and generating errors like this:

ERROR: Error sending email. Exception: System.Net.Mail.SmtpException: Failure sending mail. ---> System.IO.IOException: Authentication failed because the remote party has closed the transport stream.

Adding the following registry keys will force TLS 1.2:

Registry Keys to Force TLS 1.2

The following links have more information on SQL Server Reporting Services:

  • SSRS Install, Setup and Configuration
  • SQL Server Reporting Services 2017 Installation and Configuration
  • Install SSRS ReportServer Databases on Azure SQL Managed Instance
  • Installing SQL Server Reporting Services 2017
  • SQL Server Reporting Services Standalone Installation
  • How to Add SSRS to Existing SQL Server Clustered Instance
  • PowerShell Commands for SQL Server Reporting Services
  • Visual Studio 2019 Install and Configure for the SQL Server DBA
  • How to Install and Configure SSRS with Amazon RDS SQL Server
  • Side by Side SSRS Install and Upgrade to Minimize Downtime

sql server categories

About the author

MSSQLTips author Joe Gavin

Comments For This Article

Related articles.

Securing Databases Week 7 Assignment

  • Information Systems

SQL Tutorial

Sql database, sql references, sql examples, sql operators, sql arithmetic operators, sql bitwise operators, sql comparison operators.

Advertisement

SQL Compound Operators

Sql logical operators.

Get Certified

COLOR PICKER

colorpicker

Report Error

If you want to report an error, or if you want to make a suggestion, do not hesitate to send us an e-mail:

[email protected]

Top Tutorials

Top references, top examples, get certified.

IMAGES

  1. How to Create a Table With Multiple Foreign Keys in SQL?

    sql key assignment

  2. SQL Keys

    sql key assignment

  3. SQL Assignment 1 With Solution

    sql key assignment

  4. Primary key on two columns sql server

    sql key assignment

  5. SQL-Assignment 3

    sql key assignment

  6. Primary Key Constraint In SQL Server (With Examples)

    sql key assignment

VIDEO

  1. What Should we need to know before starting to learn SQL

  2. SQL Basics

  3. 3. SQL Database Training

  4. SQL Interview Questions and Answers Part -3

  5. SQL Tutorial-1 (Basic)

  6. SQL (Structured Query Language) Class13

COMMENTS

  1. SQL PRIMARY KEY Constraint

    To create a PRIMARY KEY constraint on the "ID" column when the table is already created, use the following SQL: MySQL / SQL Server / Oracle / MS Access: ALTER TABLE Persons ADD PRIMARY KEY (ID);

  2. SQL Primary Key

    CREATE TABLE project_assignments ( project_id INT , employee_id INT , join_date DATE NOT NULL , CONSTRAINT pk_assgn PRIMARY KEY (project_id , employee_id) ); Code language: SQL (Structured Query Language) (sql) Because the primary key consists of two columns: project_id and employee_id, you must use the PRIMARY KEY as the table constraint.

  3. Create Primary Keys in SQL Server

    Next steps Applies to: SQL Server 2016 (13.x) and later Azure SQL Database Azure SQL Managed Instance You can define a primary key in SQL Server by using SQL Server Management Studio or Transact-SQL. Creating a primary key automatically creates a corresponding unique clustered index, or a nonclustered index if specified as such. Before You Begin

  4. How to Create a Primary Key in SQL

    Solution 1: Creating new table with a single-column primary key CREATE TABLE product ( id INT PRIMARY KEY, name VARCHAR(100) NOT NULL, producer VARCHAR(100) NOT NULL, price DECIMAL(7,2) ); Discussion: To create a new table with a column defined as the primary key, you can use the keyword PRIMARY KEY at the end of the definition of that column.

  5. The Essential Guide To SQL Foreign Key Constraint

    Removing foreign key constraints. To remove a foreign key constraint, you also use the ALTER TABLE statement as follows: ALTER TABLE table_name. DROP CONSTRAINT fk_name; Code language: SQL (Structured Query Language) (sql) If you are using MySQL, you can use a cleaner syntax as follows: ALTER TABLE table_name.

  6. Primary and Foreign Key Constraints

    This column, or columns, is called the primary key (PK) of the table and enforces the entity integrity of the table. Because primary key constraints guarantee unique data, they're frequently defined on an identity column. When you specify a primary key constraint for a table, the Database Engine enforces data uniqueness by automatically ...

  7. Learn SQL: Primary Key

    Learn SQL: Primary Key December 20, 2019 by Emil Drkusic If you've already worked with databases, then you could hardly miss the term - Primary Key (PK). And if you're reading this series and use it to learn about databases, well, this article should give you a good overview of what the PK really is.

  8. SQL Server Primary Key: How it works and 6 rules you need to know

    1. What is a primary key? A primary key is a constraint put on a column or columns in a table to guarantee each row in a table is unique. One aspect that makes a table relational is that it has unique rows. If there are two rows in a table with exactly the same data, that table is not considered relational.

  9. How To Use Primary Keys in SQL

    If the primary key consists of more than one column, the combination of values in these columns must be unique across the whole table. Since the key is meant to identify every row uniquely, it can't appear more than once. A primary key must not contain NULL values. Each database table can use only one primary key.

  10. sql

    The developer must apply a few rules when choosing a primary key for each table: The primary key must uniquely identify each record. A record's primary-key value can't be null. The primary key-value must exist when the record is created. The primary key must remain stable—you can't change the primary-key field (s).

  11. SQL PRIMARY KEY Constraint (With Examples)

    In SQL, the PRIMARY KEY constraint is used to uniquely identify rows. It is a combination of NOT NULL and UNIQUE constraints i.e. it cannot contain duplicate or NULL values. Example

  12. Learn SQL: Practice SQL Queries

    The goal of this SQL practice is to analyze some typical assignments you could run into at the interview. Other places where this might help you are college assignments or completing tasks related to online courses. The focus shall be on understanding what is required and what is the learning goal behind such a question.

  13. How to Create a Table with a Foreign Key in SQL

    SQL MySQL PostgreSQL Oracle MS SQL Server Operators: CREATE TABLE FOREIGN KEY ALTER TABLE ADD CONSTRAINT ADD FOREIGN KEY Problem: You want to create a foreign key for a table in a database. Example: We would like to create a table named student that contains a foreign key that refers to the id column in the table city.

  14. SQL FOREIGN KEY Constraint

    To allow naming of a FOREIGN KEY constraint, and for defining a FOREIGN KEY constraint on multiple columns, use the following SQL syntax: MySQL / SQL Server / Oracle / MS Access: CREATE TABLE Orders ( OrderID int NOT NULL, OrderNumber int NOT NULL, PersonID int, PRIMARY KEY (OrderID), CONSTRAINT FK_PersonOrder FOREIGN KEY (PersonID)

  15. How to Use Oracle PRIMARY KEY Constraint to Manage Primary Keys

    To disable a PRIMARY KEY constraint of a table, you use the ALTER TABLE statement: ALTER TABLE table_name DISABLE CONSTRAINT primary_key_constraint_name; Code language: SQL (Structured Query Language) (sql) For example, to disable the primary key constraint of the purchase_orders table, you use the following statement:

  16. 10 Beginner SQL Practice Exercises With Solutions

    online practice sql practice Solve these ten SQL practice problems and test where you stand with your SQL knowledge! This article is all about SQL practice. It's the best way to learn SQL. We show you ten SQL practice exercises where you need to apply essential SQL concepts.

  17. SQL Exercises, Practice, Solution

    Happy Coding! You may read our SQL tutorial before solving the following exercises. List of SQL Exercises SQL Retrieve data from tables [33 Exercises] SQL Boolean and Relational operators [12 Exercises] SQL Wildcard and Special operators [22 Exercises] SQL Aggregate Functions [25 Exercises] SQL Formatting query output [10 Exercises]

  18. Troubleshoot SQL Server Transactional Replication Step By Step

    For this tip, we will review the possibilities for one of the errors and simulate the scenarios using a sample SQL Server database. Simulation Set Up For our simulation, download the WideWorldImporters sample database from Github , restore the database using the available backup, and configure transactional replication between two SQL instances.

  19. SSRS 2022 Install, Setup and Configuration

    Click Learn more, which will take you to Find the product key for SQL Server Reporting Services. Install Paid Edition. Click the Enter the product key radio button and enter the key in the box. Click Next. Review Licensing Terms. Read and accept license terms by checking the 'I accept the license terms' checkbox.

  20. Securing Databases Week 7 Assignment (docx)

    SQL INJECTIONS 2 Two key requirements for writing SQL server audits There are many requirements for writing SQL server audits. First the foremost, the audit object access must be configured to access and capture the events (Microsoft 2023). The audit policy tool exposes various sub policies settings within the audit object access category. To ensure that the SQL server events are being ...

  21. UK PRO WRITERS on Instagram: "Online Assignment Writing help has

    1 likes, 0 comments - ukprowriters1 on December 15, 2023: "Online Assignment Writing help has emerged as a valuable resource for students seeking to manage ..." UK PRO WRITERS 🇬🇧 on Instagram: "Online Assignment Writing help has emerged as a valuable resource for students seeking to manage their academic workload effectively.

  22. SQL FOREIGN KEY

    PersonID int, PRIMARY KEY (OrderID), FOREIGN KEY (PersonID) REFERENCES Persons (PersonID) ); SQL Server / Oracle / MS Access: CREATE TABLE Orders ( OrderID int NOT NULL PRIMARY KEY, OrderNumber int NOT NULL, PersonID int FOREIGN KEY REFERENCES Persons (PersonID) );

  23. SQL Tutorial

    SQL is a standard language for storing, manipulating and retrieving data in databases. Our SQL tutorial will teach you how to use SQL in: MySQL, SQL Server, MS Access, Oracle, Sybase, Informix, Postgres, and other database systems. Start learning SQL now ».

  24. SQL Keywords Reference

    SQL Keywords. Returns true if all of the subquery values meet the condition. Adds, deletes, or modifies columns in a table, or changes the data type of a column in a table. Returns true if any of the subquery values meet the condition. A constraint that limits the value that can be placed in a column. Changes the data type of a column or ...

  25. SQL Operators

    W3Schools offers free online tutorials, references and exercises in all the major languages of the web. Covering popular subjects like HTML, CSS, JavaScript, Python, SQL, Java, and many, many more.