SQL Server 27 – How to Create FOREIGN KEY Constraints

0
14



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!)

sql server, tutorial 27, how, to, how to, create, foreign, key, constraints, table, column, data, database, child, parent, int, drop table, drop, caleb, curry, calebthevideomaker2

Xem Thêm Bài Viết Về Thủ Thuật Hay Khác: https://sealsystem.org/thu-thuat

Nguồn: https://sealsystem.org/

LEAVE A REPLY

Please enter your comment!
Please enter your name here