CODEWITHSUNDEEP
mysqlsql
user3043278
user3043278

Reputation: 184

Primary keys in sql

I have here some problems undersanding how this works. So I have 2 tables Students and Enrolled like this:

CREATE TABLE Students 
 (sid CHAR(20), 
 name CHAR(50), 
 email CHAR(30), 
 age INTEGER, 
 gr INTEGER) 

CREATE TABLE Enrolled 
 (sid CHAR(20), 
 cid CHAR(20), 
 grade CHAR(2), 
PRIMARY KEY (sid,cid))

So I don't understand this particulary row PRIMARY KEY (sid,cid) Can someone explain to me how it works? I have to specify that I have another table Courses from where the cid.

Is is equivalent saying like this:

CREATE TABLE Enrolled
(sid CHAR(20) foreign key references Students(sid),
cid CHAR(20) foreign key references Courses(cid)
)

Upvotes: 0

Views: 756

Answers (3)

carexcer
carexcer

Reputation: 1427

A PRIMARY KEY is used to identify a table. A field or column that is defined as PRIMARY KEY will contains different values on each row in that table, and is mandatory to have a value (so, PRIMARY KEY is equivalent to UNIQUE and NOT NULL).

PRIMARY KEY can be a single field, or multiple fields, but it always satisfy that "each row will have a different PRIMARY KEY".

If you declare as PRIMARY KEY a combination of 2 columns, you will be able to have this for example:

CREATE TABLE Enrolled 
 (sid CHAR(20), 
 cid CHAR(20), 
 grade CHAR(2), 
PRIMARY KEY (sid,cid)) --PRIMARY KEY with combination of 2 columns

sid   |   cid   |   grade
1          1         XX
1          2         XX
2          1         XX
2          2         XX
2          3         XX

In this example, you can see that the column sid or the column cid has repeated values individually, but there isn't a combination of (sid, cid) that was repeated.

Like a PRIMARY KEY is used to identify a row in a table, when we want to relate two tables we can define a FOREIGN KEY in one table to link this one with the other table.

Your case is that the ENROLLED table is identified by a composite PRIMARY KEY to represent a many-to-many relationship. That is the way to say that:

  • A student can be enrolled in many courses.
  • A course can have enrolled many students.

enter image description here

Note*: Is a best practice to define the PRIMARY KEYS as numeric values, such integer, bigint, etc. because it is better to improve the indexes performance (all PRIMARY KEYS have defined inherently an INDEX, and they are faster working with "numeric" values than working with "string" values).

Upvotes: 1

Klas Lindbäck
Klas Lindbäck

Reputation: 33273

PRIMARY KEY means both UNIQUE and NOT NULL.

If you want sid and cid to also be FOREIGN KEY you have to specify that separately.

Having two fields for a primary key is often used for tables that are the physical representation of a many-to-many relation. In your database design diagram you would have STUDENT and COURSE as entities and ENROLLMENT as a many-to-many relationship between them.

In the physical database diagram many-to-many relationships are modelled as tables, often with a composite PRIMARY KEY and with FOREIGN KEY constraints to the entity tables.

Upvotes: 1

sumit
sumit

Reputation: 15464

PRIMARY KEY (sid,cid) means composite primary key.. the combination of these field should be unique.

Upvotes: 1

Related Questions