[tieugiang94]

Today I leanred to add a constraint for a table.
But, Why we name for contraint instead of default name ? What's benefit when we built a Database or Program ?

[modi123_1]

Generally it is to keep order among the chaos which means data integrity. It's the last line of trust (or lack there of) from any app or user interacting with out data. Without constraints you can have insane things like row ids not being unique and customers being billed for things they didn't order.

So we have the 'primary key' constraint that keeps the row indexing unique. Then the 'foreign key' constraint to keep up our ephemeral relationships between tables. Orders have details and details have items.. etc. Finally there is the data or domain constraint... does a column have a default value? Can it be null? The date entered column can't be set in the future.

[BobRodes]

Simply put, constraints keep a user from entering the wrong kind of data. For example, if we want a field to contain a number between 1 and 10, we can add a constraint to require that.

[baavgai]

tieugiang94, on 15 November 2012 - 09:15 AM, said:

Why we name for contraint instead of default name ? What's benefit when we built a Database or Program ?

Because computers suck at choosing names. If you have a table named "employee" and just let the computer pick a name, it might choose something like "I0235A4E4_foo". If you choose the name, you can choose something like "pk_employee" or "fk_employee_dept". Such names make it clear what the constraint is for. If you don't care, don't name it.

[e_i_pi]

PostgreSQL may be an exception, in that it determines a default name for constraints (amongst other things, like indexes) based on the table, column, what you're constraining to, etc.

An important note to make about constraints, in particular FK constraints, is that those constraints don't imply an index (at least not in MSSQL, Access, MySQL and PostgreSQL, the DBs I'm familiar with). The constraint simply restricts the type of data that can go in that field. If you want to FK constrain and have an index on that FK constraint as well, you'll need to generate the index separately.

This post has been edited by e_i_pi: 05 December 2012 - 03:54 PM

[baavgai]

e_i_pi, on 05 December 2012 - 05:53 PM, said:

An important note to make about constraints, in particular FK constraints, is that those constraints don't imply an index

I'm not sure that's entirely true. Constraints such as PRIMARY KEY and UNIQUE generate indexes, pretty much in every database I know.

Quote

A FOREIGN KEY constraint does not have to be linked only to a PRIMARY KEY constraint in another table; it can also be defined to reference the columns of a UNIQUE constraint in another table.
-- http://msdn.microsof...(v=sql.80).aspx

So, in MS SQL, at least, a FK relationship must have a least one index. Since it's the index where the lookup is happening, it doesn't really seem to be an issue if the constrained field doesn't have an index.

[e_i_pi]

Sorry, I should have qualified that by saying that constraints don't necessarily imply an index.