SQL Server 27 – How to Create FOREIGN KEY Constraints

Công Nghệ



In the previous video we set up an entire table. The problem with this table is that the species column is just plain text. The problem with this is that there is a higher probability of incorrect data and if we have tons of animals in here there will be a lot of redundant information. The solution to this is to change this species to a foreign key to another table.

Remember that when you create a foreign key it is a child to a parent. The thing you need to know is that the parent has to exist before the child so the child has something to reference.

So let’s create the parent table.

CREATE TABLE Species(
ID INT PRIMARY KEY IDENTITY,
Species VARCHAR(50) NOT NULL UNIQUE,
FriendlyName VARCHAR(50) NOT NULL //NOT UNIQUE because multiple rows could be same category (Ex: Bunny)
);

Now, the friendly name will likely have redundant data, so maybe later we can add a table for animal categories or something, but let’s not overwhelm ourselves quite yet. Maybe in a few videos.

Now that we have created that table, we can recreate the table that references it. The first thing to know when creating a foreign key is that the data type must match. Because the ID column in the species table is of type INT, we should make our Species column in the Animals table also of type INT. Secondly, to make this a foreign key we add REFERENCES Species(ID) to the Species column.

DROP TABLE IF EXISTS Animals;

CREATE TABLE Animals(
ID INT PRIMARY KEY IDENTITY,
Name VARCHAR(50) NOT NULL,
Species INT NOT NULL REFERENCES Species(ID),
ContactEmail VARCHAR(50) NOT NULL UNIQUE
);

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Support me!

Subscribe to my newsletter:

Donate!:

~~~~~~~~~~~~~~~Additional Links~~~~~~~~~~~~~~~

More content:
Facebook:
Google+:
Twitter:

Amazing Web Hosting – (The best web hosting for a cheap price!)

Nguồn: https://bus-rush.info/

Xem thêm bài viết khác: https://bus-rush.info/cong-nghe/

10 thoughts on “SQL Server 27 – How to Create FOREIGN KEY Constraints

  1. You're probably a really nice guy that's why you laugh so much, but it's hard to take people seriously when they do that, I hope you learn from it as it's for your own benefit

  2. How do you create foreign key constraints by it self not next to the specific column name ? Not like the way you did it now

Leave a Reply

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