6 Replies - 2387 Views - Last Post: 05 December 2012 - 06:27 PM

#1 tieugiang94  Icon User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 160
  • Joined: 07-December 11

What is the aim of Constraint

Posted 15 November 2012 - 07:15 AM

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 ?
Is This A Good Question/Topic? 1
  • +

Replies To: What is the aim of Constraint

#2 modi123_1  Icon User is offline

  • Suitor #2
  • member icon



Reputation: 8939
  • View blog
  • Posts: 33,521
  • Joined: 12-June 08

Re: What is the aim of Constraint

Posted 15 November 2012 - 08:10 AM

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.
Was This Post Helpful? 1
  • +
  • -

#3 BobRodes  Icon User is offline

  • Your Friendly Local Curmudgeon
  • member icon

Reputation: 573
  • View blog
  • Posts: 2,989
  • Joined: 19-May 09

Re: What is the aim of Constraint

Posted 04 December 2012 - 10:38 PM

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.
Was This Post Helpful? 1
  • +
  • -

#4 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5780
  • View blog
  • Posts: 12,594
  • Joined: 16-October 07

Re: What is the aim of Constraint

Posted 05 December 2012 - 06:58 AM

View Posttieugiang94, 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.
Was This Post Helpful? 1
  • +
  • -

#5 e_i_pi  Icon User is offline

  • = -1
  • member icon

Reputation: 789
  • View blog
  • Posts: 1,676
  • Joined: 30-January 09

Re: What is the aim of Constraint

Posted 05 December 2012 - 03:53 PM

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

Was This Post Helpful? 0
  • +
  • -

#6 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5780
  • View blog
  • Posts: 12,594
  • Joined: 16-October 07

Re: What is the aim of Constraint

Posted 05 December 2012 - 05:47 PM

View Poste_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.
Was This Post Helpful? 1
  • +
  • -

#7 e_i_pi  Icon User is offline

  • = -1
  • member icon

Reputation: 789
  • View blog
  • Posts: 1,676
  • Joined: 30-January 09

Re: What is the aim of Constraint

Posted 05 December 2012 - 06:27 PM

Sorry, I should have qualified that by saying that constraints don't necessarily imply an index.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1