Re: Primary keys in a single column table and text vs varchar
От | Lew |
---|---|
Тема | Re: Primary keys in a single column table and text vs varchar |
Дата | |
Msg-id | ii9l5r$9ke$1@news.albasani.net обсуждение исходный текст |
Ответ на | Re: Primary keys in a single column table and text vs varchar (Michael Swierczek <mike.swierczek@gmail.com>) |
Список | pgsql-novice |
matty jones wrote: >> I am designing a table that will hold a list of unique names and be >> referenced by several other tables. There will only be one column in the >> table (name), should I still create a separate primary key for that column >> or can I use the name column as my key. I have also been reading a lot >> about the differences between varchar and text and was wondering if there is >> a downside to using text. The column I want to use text on (notes) will >> hold a string that could be any length, I can use varchar(255) but I am >> worried about the text being truncated or of the queries giving errors when >> I try to output the text. I understand there is a mathematical limit to how >> much can be stored in a single entry but I wasn't sure of any downsides to >> just defaulting to text when possible. Michael Swierczek wrote: > It may help you to read pages on natural key versus surrogate key > discussions, like this page: > http://www.agiledata.org/essays/keys.html > To summarize briefly, using the name column as the primary key makes > your database easier to understand, but if you need to change the > table in the future and add or remove additional unique constraints, > changing your database is a lot of work. Using a separate column for > the primary key gives you the converse situation, so your database > layout is less intuitive, but changing the tables in the future > becomes much easier. PostgreSQL will handle either type of primary > key just fine. In my limited experience a separate key usually is a > better choice, but I've only been wrestling with databases for a few > years. > > In terms of text versus character varying, I don't think the > differences are significant. I would use character varying(500) or > (1000), so you have plenty of room for big names but you can't > accidentally have a 10MB entry in the table. For simple one-column lookup tables such as the OP describes, the surrogate key adds little benefit but relatively larger cost. The lookup value is supposed to be constrained by the lookup table, and using a surrogate key to protect against changes in that constraint defeats the purpose. If you do change it, it means the value acquired for all earlier transactions was wrong, or at least that you lose the history of what value was used at that time. You risk semantic changes, e.g., if you change 1 FOO 2 BAR 3 BAZ 4 QUX to 1 BAR 2 URP 3 BAX 4 QUX . You might think you'll protect against that, but the next database copy or move to a new host via dump/restore could really hose things. The referencing table also becomes harder to use - EVERY query will need a join to get at the real value. This will slow down query performance, and a lookup tables' use is likely to be mostly for queries. Using the natural key directly means that the FK reference will guarantee a correct value, but you won't need a join to query that value. The complexity of managing a second column for a one-column table is likely to be too high for the limited benefit conferred on a lookup table. Just store the one natural-key column for a lookup table. -- Lew Ceci n'est pas une fenêtre. .___________. |###] | [###| |##/ | *\##| |#/ * | \#| |#----|----#| || | * || |o * | o| |_____|_____| |===========|
В списке pgsql-novice по дате отправления: