Constraint or Index?

Suppose I have a table called "Languages" with two fields. One field is an Identity field that acts as the Primary Key. The second field is "LanguageName" which would naturally be "English", "Spanish", etc. What I want to do is put a constraint on the LanguageName field so that someone cannot enter the same name twice.

Is it better to create an Index --> Create UNIQUE and use:
a) Constraint?
b) Index with Ignore duplicate key checked?

Is there any benefit of one over the other for my purpose? Thanks.The Database Engine automatically creates a UNIQUE index to enforce the uniqueness requirement of the UNIQUE constraint. The underlying changes are actually the same :)|||Ah-ha. Thanks!|||I'd use a constraint because it better reflects what your intentions for that field are. An index will do the job of course but and index is more of an implementation thing than a data/business rule thing.|||One field is an Identity field that acts as the Primary Key. The second field is "LanguageName" which would naturally be "English", "Spanish", etc.

Isn't language name a lovely natural key?

Constants in TSQL

Hello,
Is there such a thing as constants in TSQL (similar to constants in
other languages)? I've looked in books online and can't find any
references to them.
ThanksHi,
Create a Master table which stores all the Constants. Table name will be
some thing like SYSCONTROL
Create table SYSCONTROL(Controlname Varchar(100), ControlValue
numeric(20,3))
Eg:-
insert into SYSCONTROL Values('Launchdate','08/10/2005')
Thanks
Hari
SQL Server MVP
"Frank Rizzo" <nospam@.nospam.com> wrote in message
news:OSvKkjHoFHA.708@.TK2MSFTNGP09.phx.gbl...
> Hello,
> Is there such a thing as constants in TSQL (similar to constants in other
> languages)? I've looked in books online and can't find any references to
> them.
> Thanks|||You can use DECLARE and SET/SELECT combination to define and assign a
constant value to the "constant"
DECLARE @.COLOR varchar(30)
SET @.COLOR = 'red' or SELECT @.COLOR ='red'
"Frank Rizzo" wrote:

> Hello,
> Is there such a thing as constants in TSQL (similar to constants in
> other languages)? I've looked in books online and can't find any
> references to them.
> Thanks
>|||Coskun T. wrote:
> You can use DECLARE and SET/SELECT combination to define and assign a
> constant value to the "constant"
> DECLARE @.COLOR varchar(30)
> SET @.COLOR = 'red' or SELECT @.COLOR ='red'
That's what I do now. It just seems like a PITA.
I'd settle for at least
declare @.color varchar(30) = 'red'
>
> "Frank Rizzo" wrote:
>|||You can build a table of constants within a schema using Standard SQL
like this:
CREATE TABLE Constant
(lock CHAR(1) DEFAULT 'X' NOT NULL PRIMARY KEY
CHECK (lock = 'X'),
pi REAL DEFAULT 3.141592653 NOT NULL,
e REAL DEFAULT 2.718281828 NOT NULL,
phi REAL DEFAULT 1.618033988 NOT NULL,
.);
INSERT INTO Constants DEFAULT VALUES;
The insertion creates one row, so the table ought to have a singular
name. The "lock" column assures you that there is always only one row.
In full SQL-92, you can create such as table as a VIEW with a row
constructor:
CREATE VIEW Constant (pi, e, phi, ..)
AS VALUES (3.141592653, 2.718281828, 1.618033988, ..);|||> In full SQL-92, you can create such as table as a VIEW with a row
> constructor:
> CREATE VIEW Constant (pi, e, phi, ..)
> AS VALUES (3.141592653, 2.718281828, 1.618033988, ..);
You can accomplish this in SQL Server as well, using below technique:
CREATE VIEW Constant
AS
SELECT
CAST(3.141592653 AS float) AS pi
,CAST(2.718281828 AS float) AS e
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1124026681.166459.209960@.o13g2000cwo.googlegroups.com...
> You can build a table of constants within a schema using Standard SQL
> like this:
> CREATE TABLE Constant
> (lock CHAR(1) DEFAULT 'X' NOT NULL PRIMARY KEY
> CHECK (lock = 'X'),
> pi REAL DEFAULT 3.141592653 NOT NULL,
> e REAL DEFAULT 2.718281828 NOT NULL,
> phi REAL DEFAULT 1.618033988 NOT NULL,
> ..);
> INSERT INTO Constants DEFAULT VALUES;
> The insertion creates one row, so the table ought to have a singular
> name. The "lock" column assures you that there is always only one row.
> In full SQL-92, you can create such as table as a VIEW with a row
> constructor:
> CREATE VIEW Constant (pi, e, phi, ..)
> AS VALUES (3.141592653, 2.718281828, 1.618033988, ..);
>