Welcome to the Treehouse Community

The Treehouse Community is a meeting place for developers, designers, and programmers of all backgrounds and skill levels to get support. Collaborate here on code errors or bugs that you need feedback on, or asking for an extra set of eyes on your latest project. Join thousands of Treehouse students and alumni in the community today. (Note: Only Treehouse students can comment or ask questions, but non-students are welcome to browse our conversations.)

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and a supportive community. Start your free trial today.

Development Tools Database Foundations Joining Relational Data Between Tables in SQL Keys and Auto-Incrementing Values

Miguel Nunez
Miguel Nunez
3,266 Points

How is it that you can combine Primary keys n unique keys together I thought they r different keys dat r used on 1 table

I thought primary keys where structured some thing like this CREATE TABLE one_a (id INTEGER PRIMARY KEY, name VARCHAR(30)); but then I saw a guy did this

CREATE TABLE genres (id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR (30) NOT NULL UNIQUE); so u can mix it with a unique a key or a foreign key in the same code sentence ?

2 Answers

Corey Johnson
PLUS
Corey Johnson
Courses Plus Student 10,192 Points

This can be a little a little confusing. But at a basic level, a PRIMARY key is a UNIQUE key, but has some special attributes. A table can only have one PRIMARY key, and a PRIMARY key can not be NULL. Both a PRIMARY key and a UNIQUE key can be used to ensure the uniqueness of a row though. There are some other differences, depending on the SQL server software you are using as well.

A UNIQUE key can be null, and it actually does not always have to be unique... but it is considered to be a CANDIDATE key. And a table can have more than one UNIQUE key.

The SQL server will create and enforce a constraint on PRIMARY, UNIQUE and FOREIGN keys.

The fact that a UNIQUE key can have a null value is a bit weird i know, but keep in mind, this just means that one of the columns used to form the UNIQUE key can have a null value. ie - if you had a UNIQUE key made up of 2 columns named "FIRST_NAME" and "LAST_NAME", and one of the rows represented a person with just a FIRST_NAME.. lets say like "Sting" from the Police music group.. his LAST_NAME would be "null." But this still means you could not add a 2nd record with a FIRST_NAME of "String" and the LAST_NAME of null.. the null in that row is part of the key. But you COULD add a 2nd record with a FIRST_NAME of "String" and a LAST_NAME of "Johnson" since that does not violate the uniqueness of the 1st record containing the null for LAST_NAME.

I hope this helps a little.

Miguel Nunez
Miguel Nunez
3,266 Points

So does that mean I can have a Primary KEY and a FOREIGN KEY in the same MYSQL code sentence as well? Just like how that guy mixed a UNIQUE KEY within a PRIMARY KEY sentence?

Corey Johnson
PLUS
Corey Johnson
Courses Plus Student 10,192 Points

Yes that is correct. You can definitely define a primary key and foreign key within the same create statement. The foreign key constraint can perform cascaded deletes automatically as well to avoid orphaned records. very cool stuff!

Miguel Nunez
Miguel Nunez
3,266 Points

I thought that you could only use one key type at a time on one table at a time. I’m talking about foreign keys,unique keys and primary keys. I’m still puzzled because of this CREATE TABLE genres (id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR (30) NOT NULL UNIQUE); Its mixed. So you can mix them together in one mysql sentence wow. So if I understand this correctly keys in general define a tables row by an ID by referencing to a tables column. http://www.programmerinterview.com/index.php/database-sql/sql-key-definition/

Miguel Nunez
Miguel Nunez
3,266 Points

So in other words you could have different key types with each other in the same table ? Like for example the first row is a primary key the second row is a unique key based and the third row is a foreign key based? Wrong ? Right ? Let me know please :-) ?