Posted on



The previous video had these tables:

CREATE TABLE Species(
ID INT PRIMARY KEY IDENTITY,
Species VARCHAR(50) NOT NULL UNIQUE,
FriendlyName VARCHAR(50) NOT NULL
);

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

These column attributes are all examples of constraints. To be more specific, they are known as column-level constraints. Sometimes you need to create what is known as a table-level constraint. To illustrate this, we are going to create a new table that shows this in action.

Remember that we are trying to create an animal dating site. A specific animal can create an account on our website and say what kind of species he or she is interested in. One specific animal can be interested in multiple species and an individual species can be liked by many animals. Therefore, it is a many to many relationship. We already have a Species table and an Animals table, so we need an intermediary table to associate animals with species:

CREATE TABLE Interests(
AnimalID INT NOT NULL REFERENCES Animals(ID),
SpeciesID INT NOT NULL REFERENCES Species(ID)
);

But now, we want to create a composite primary key, the combination of both columns. A column-level attribute is not capable of doing this because it is associated with just one column. Let’s create a table-level constraint. To do this we add the constraint as if it’s another column:

CREATE TABLE Interests(
AnimalID INT NOT NULL REFERENCES Animals(ID),
SpeciesID INT NOT NULL REFERENCES Species(ID),
PRIMARY KEY (AnimalID, SpeciesID)
);

The downside here is that we cannot create a foreign key to reference this composite key. If for some reason we have to be able to reference an individual interest in another table, we would create an ID column and put a UNIQUE constraint on the combination of AnimalID and SpeciesID. Then we could reference an individual ID.

Now, in SSMS, inside of our tables key folder you can find the composite key.

Unfortunately, SSMS defaults to a pretty ugly name for our primary key constraint. Fortunately, we can actually give names to our constraints. That’s what we will be doing in the next video.

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

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://taiduc.org

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

10 Replies to “SQL Server 28 – Composite Primary Key”

  1. i have one confusion and not finding the answer , what is the main difference b/w composite primary key and composite unique key except null ??
    like i read a lot that primary key can only apply to one columns and unique in multiple but there is also composite primary key option as well as unique

  2. I don't yet see how INTERESTS table will work.
    I see where PK SpeciesID is made and mentioned in Animals().
    But shouldn't AnimalID have an "s" on the end because the table is "Animals"? And
    PK creation, shouldn't AnimalID INT have REFERENCES Animals(ID)?, similar to how SpeciesID was created in Animals? (maybe this'll all be cleared up in the next one …)

  3. So easy to understand, and it's straight to the point, and to mention not boring. Don't know how it's even possible but you did it.

  4. Would it be possible to make more examples or maybe like a PT.2 with this video with more examples? You did great btw

Leave a Reply

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