VARCHAR as foreign key/primary key in database good or bad?

Is it better if I use ID nr:s instead of VARCHARS as foreign keys? And is it better to use ID nr:s isntead of VARCHARS as Primary Keys? By ID nr I mean INT!

This is what I have now:

category table: cat_id ( INT ) (PK) cat_name (VARCHAR) category options table: option_id ( INT ) (PK) car_id ( INT ) (FK) option_name ( VARCHAR )

I COULD HAVE THIS I THINK:

category table: cat_name (VARCHAR) (PK) category options table: cat_name ( VARCHAR ) (FK) option_name ( VARCHAR ) ( PK )

Or am I thinking completely wrong here?

--------------Solutions-------------

The problem with VARCHAR being used for any KEY is that they can hold WHITE SPACE. White space consists of ANY non-screen-readable character, like spaces tabs, carriage returns etc. Using a VARCHAR as a key can make your life difficult when you start to hunt down why tables aren't returning records with extra spaces at the end of their keys.

Sure, you CAN use VARCHAR, but you do have to be very careful with the input and output. They also take up more space and are likely slower when doing a Queries.

Integer types have a small list of 10 characters that are valid, 0,1,2,3,4,5,6,7,8,9. They are a much better solution to use as keys.

You could always use an integer-based key and use VARCHAR as a UNIQUE value if you wanted to have the advantages of faster lookups.

My 2 cents:

From a performance perspective, using CHAR or VARCHAR as primary key or index is a nightmare.

I've tested compound primary keys (INT + CHAR, INT + VARCHAR, INT + INT) and by far INT + INT was the best performance (loading a data warehouse). Lets say about twice more performance if you keep only numeric primary keys/indexes.

I would say it is fine to use VARCHAR as both PRIMARY and FOREIGN KEYS.

Only issue I could forsee is if you have a table, lets say Instruments (share instruments) and you create the PRIMARY/FOREIGN KEY as VARCHAR, and it happens that the CODE changes.

This does happen on Stock Exchanges, and would require you to rename all references to this CODE, where as a ID nr would not require this from you.

So to conclude, I would say this dependes on your intended use.

EDIT

When I say CODE, I mean the Ticker Code for lets say GOOG, or any other share. It is possible for these codes to change over time, lets say you look at Dirivative/Future instruments.

When I'm doing design work I ask myself: have I got anything in this data that I can guarantee is going to be non-NULL, unique, and unchanging? If so that's a candidate to be the primary key. If not, I know I have to generate a key value to use. Assuming, then, that my candidate key happens to be a VARCHAR I then look at the data. Is it reasonably short in length (meaning, say, 20 characters or less)? Or is the VARCHAR field rather long? If it's short it's usable as a key - if it's long, perhaps it's better to not use it as a key (although if it's in consideration for being the primary key I'm probably going to have to index it anyways). At least part of my concern is that the primary key is going to have to be indexed and will perhaps be used as a foreign key from some other table. Comparisons of VARCHAR fields tend to be slower than the comparison of numeric fields (particularly binary numeric fields such as integers) so using a long VARCHAR field as a key may result in slow performance. YMMV.

with an int you can store up to 2 billion in 4 bytes with varchars you cannot you need to have 10 bytes or so to store that, if you use varchars there is also a 2 byte overhead

so now you add up the 6 extra bytes in every PK and FK + the 2 byte varchar overhead

If you make the category name into the ID you will have a problem if you ever decide to rename a category.

There's nothing wrong with either approach, although this question might start the usual argument of which is better: natural or surrogate keys.

If you use CHAR or VARCHAR as a primary key you'll end up using it as a forign key at some point. When it comes down to it, as @astander says, it depends on your data and how you are going to use it.

Category:sql Time:2010-01-20 Views:2

Related post

  • MySQL - how to use VARCHAR as AUTO INCREMENT Primary Key 2010-08-11

    I am using a VARCHAR as my primary key. I want to auto increment it (base 62, lower/upper case, numbers), However, the below code fails (for obvious reasons): CREATE TABLE IF NOT EXISTS `campaign` ( `account_id` BIGINT(20) NOT NULL, `type` SMALLINT(5

  • Difference between Key, Primary Key, Unique Key and Index in MySQL 2010-10-02

    When should I use KEY, PRIMARY KEY, UNIQUE KEY and INDEX? --------------Solutions------------- KEY and INDEX are synonyms in MySQL. They mean the same thing. In databases you would use indexes to improve the speed of data retrieval. An index is typic

  • Entity Framework 4 Returning KEY/Primary Key 2011-03-28

    Entity Framework 4 Returning KEY/Primary Key I’m Trying to find a way to Return a Key/Primary Key without having to create a stored procedure to do it for me using: CREATE PROCEDURE [dbo].[UserRecords] ( @Name varchar(10) ) AS -- INSERT the new recor

  • Key, Superkey, Minimal Superkey, Candidate Key, Primary Key DIFFERENCES 2011-08-05

    I'm new to MySQL and I'm really confused about the different terms that I've encountered. I tried googling the answer but the results are really confusing and when I try and understand it just seems like they are the same thing. What exactly the diff

  • MySQL keys: PRIMARY KEY, FOREIGN KEY, KEY - what is "KEY"? 2010-11-16

    Looking at a "mysqldump -d" and see a key that is KEY, not "PRIMARY KEY" or "FOREIGN KEY" What is KEY? Example: CREATE TABLE IF NOT EXISTS `TABLE_001` ( `COL_001` int(256) NOT NULL, `COL_002` int(256) NOT NULL, `COL_003` int(256) NOT NULL, `COL_004`

  • SQL Delete Update Foreign Key Primary Key Constaint 2011-09-20

    Let's say I have a table which already include 'IsDelete Char(1)' Column. Whenever I make delete process for this table, i don't make actual Delete command. eg. DELETE FROM TableName But I make Update command. eg. UPDATE TableName SET IsDelete = '1'

  • Hibernate: Can a foreign-cum-primary key be defined as a property rather than a field of a model? 2012-03-29

    Is it absolutely mandatory for the primary key of a Hibernate model (entity) to be a field rather than a property? I have a table, say 'Purchase', whose primary key, say, cust_id, also acts as a foreign key to a 'Cust' table. Can I define the primary

  • Superkey, candidate key & primary key 2011-12-01

    can any kind soul clarify my doubts with a sample example below for the superkey, candidate key and primary key. I know there are alot of posts and websites out there explaining what's the differences between them. But it looks like all are generic d

  • How do I use elements of a dataframe like hash keys / dictionary keys / primary keys? 2011-07-25

    I have a dataframe in which I want to use certain values as hash keys / dictionary keys (or whatever you call it in your language of choice) for other values in that dataframe. Say I have a dataframe like this which I've read in from a large csv file

  • Designing Unique Keys(Primary Keys) for a heavily denormalized NoSQL database 2011-02-02

    I am working on a web application related to Discussion forums using Java and Cassandra database. I need to construct 'keys' for the rows storing the user's details and & another set of rows storing the content posted by the user. One option is t

  • Ruby on Rails ActiveRecord: eager loading issue with foreign and primary key 2010-04-29

    The eager loading on Ruby on Rails is not working properly for the following scenario. First we had a model called marks which has the following fields id, student, subject, mark the student is a string column which has the active directory login val

  • Are there any good reasons to have a database table without an integer primary key? 2009-03-22

    Although I'm guilty of this crime, it seems to me there can't be any good reason for a table to not have an identity field primary key. Pros: - whether you want to or not, you can now uniquely identify every row in your table which previously you cou

  • Database Design and the use of non-numeric Primary Keys 2009-05-29

    I'm currently in the process of designing the database tables for a customer & website management application. My question is in regards to the use of primary keys as functional parts of a table (and not assigning "ID" numbers to every table just

  • Relational Database Design - double primary key in one table? 2010-09-20

    I have a table that keeps the user ratings for items. I want to allow each user to rate an item only once, is there any way to force the database not to allow duplicate for itemId and userId? I don't want each individual field to be a primary key. I

  • Mysql primary key for table with int and varchar field? 2011-04-21

    I have a movie table and want to store alternative titles. I'll be storing the alternative titles/aliases in another table. I'm not sure what is the best primary key to use though. I will have a movie_id INT field, and an alias varchar(255) field. Sh

  • Foreign key with composite primary key not working in MySql 2011-06-24

    I have the following SQL script. After it runs the foreign key relationship is never enforced. CREATE TABLE Country ( name varchar(40) NOT NULL, abbreviation varchar(4) NOT NULL, PRIMARY KEY (name) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; CREATE TABLE S

  • Symfony: Loading fixtures of objects with multiple column primary key which are also foreign keys 2011-09-03

    im writing fixtures for my project in symfony, and i have preblems when trying to add fixtures to objects with a multiple column primary key, which is also foreign key to other table. Here's the schema in MySql Workbench: I generated the sql code wit

  • composite primary key and unique key as a foreign key 2011-12-26

    Ok Here is the situation. A university has more than one faculty. Each faculty has more than one department. I do not want any duplicated faculty or department. So I defined the three tables below. CREATE TABLE university ( id INT PRIMARY KEY AUTO_IN

  • MySQL Primary Keys and VARCHAR(255) 2012-01-11

    I have a database using VARCHAR(255) as its primary key on tables and they look like GUIDs. Wouldnt an Int be better for performance? --------------Solutions------------- It depends on your storage engine, but generally speaking an int/bigint would b

Copyright (C) pcaskme.com, All Rights Reserved.

processed in 0.669 (s). 13 q(s)