Welcome to the Treehouse Community
Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.
Looking to learn something new?
Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.
Start your free trial
Miguel Nunez
3,266 PointsHow 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
Courses Plus Student 10,192 PointsThis 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.
Corey Johnson
Courses Plus Student 10,192 PointsYes 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
3,266 PointsI 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
3,266 PointsSo 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 :-) ?
Miguel Nunez
3,266 PointsMiguel Nunez
3,266 PointsSo 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?