how to set up unique combinational constraint

Hi I have a table and I do not want to allow entries if two column value
match, for example
table 1
id type name value
int varchar20 varchar20 int
1 test paul 2
if name is paul and value is 3 allow insert
if name is paul and value is 2 do not allow insert.
I am trying to do this with under the index/keys menu, not quite sure how to
do it and if this is correct.
Thanks.
Paul G
Software engineer.
If you just want to prevent the combination from being inserted into the
table, you can use an INSERT trigger. If you want the condition to always
hold for all the rows in the table, you can create a CHECK constraint on the
table.
Linchi
"Paul" wrote:

> Hi I have a table and I do not want to allow entries if two column value
> match, for example
> table 1
> id type name value
> int varchar20 varchar20 int
> 1 test paul 2
> if name is paul and value is 3 allow insert
> if name is paul and value is 2 do not allow insert.
> I am trying to do this with under the index/keys menu, not quite sure how to
> do it and if this is correct.
> Thanks.
> --
> Paul G
> Software engineer.
|||Consider putting a UNIQUE constraint on (name, value)
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:23467935-67F3-418E-A24E-200CE74DD637@.microsoft.com...
If you just want to prevent the combination from being inserted into the
table, you can use an INSERT trigger. If you want the condition to always
hold for all the rows in the table, you can create a CHECK constraint on the
table.
Linchi
"Paul" wrote:

> Hi I have a table and I do not want to allow entries if two column value
> match, for example
> table 1
> id type name value
> int varchar20 varchar20 int
> 1 test paul 2
> if name is paul and value is 3 allow insert
> if name is paul and value is 2 do not allow insert.
> I am trying to do this with under the index/keys menu, not quite sure how
to
> do it and if this is correct.
> Thanks.
> --
> Paul G
> Software engineer.
|||I think I need the check constraint, I do not want any duplicates in the
table using both columns.
for example
id type name value
int varchar20 varchar20 int
1 test paul 2
2 test1 paul 3 ok
3 test2 tom 2 ok
4 test3 tom 3 ok
5 test4 tom 2 **do not allow this
entry**
Paul G
Software engineer.
"Linchi Shea" wrote:
[vbcol=seagreen]
> If you just want to prevent the combination from being inserted into the
> table, you can use an INSERT trigger. If you want the condition to always
> hold for all the rows in the table, you can create a CHECK constraint on the
> table.
> Linchi
> "Paul" wrote:
|||Actually, you want a UNIQUE constraint on (name, value):
alter table MyTable
add
constraint UK_MyTable UNIQUE (name, value)
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:65E03382-8002-4FCE-8531-6EA764838D75@.microsoft.com...
I think I need the check constraint, I do not want any duplicates in the
table using both columns.
for example
id type name value
int varchar20 varchar20 int
1 test paul 2
2 test1 paul 3 ok
3 test2 tom 2 ok
4 test3 tom 3 ok
5 test4 tom 2 **do not allow this
entry**
Paul G
Software engineer.
"Linchi Shea" wrote:

> If you just want to prevent the combination from being inserted into the
> table, you can use an INSERT trigger. If you want the condition to always
> hold for all the rows in the table, you can create a CHECK constraint on
the[vbcol=seagreen]
> table.
> Linchi
> "Paul" wrote:
how to[vbcol=seagreen]
|||ok thanks. Since I do not care if they are unique in a single column but
need to look at both columns together would I need to change the statement or
would the statement below still work
thanks?
alter table MyTable
add
constraint UK_MyTable UNIQUE (name, value)
column1 column2
paul 1 ok
paul 2 ok
paul 1 do not allow this entry match(value
in column1 and column2 matches value in column1 and column2 in previous
entry).
Paul G
Software engineer.
"Tom Moreau" wrote:

> Actually, you want a UNIQUE constraint on (name, value):
> alter table MyTable
> add
> constraint UK_MyTable UNIQUE (name, value)
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:65E03382-8002-4FCE-8531-6EA764838D75@.microsoft.com...
> I think I need the check constraint, I do not want any duplicates in the
> table using both columns.
> for example
> id type name value
> int varchar20 varchar20 int
> 1 test paul 2
> 2 test1 paul 3 ok
> 3 test2 tom 2 ok
> 4 test3 tom 3 ok
> 5 test4 tom 2 **do not allow this
> entry**
>
> --
> Paul G
> Software engineer.
>
> "Linchi Shea" wrote:
> the
> how to
>
>
|||The code I gave you is what you need, since it specifies that the uniqueness
is to be applied to the combination of both columns.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:D044B7E2-3B9A-4B19-84A9-AADDA4AEBE2A@.microsoft.com...
ok thanks. Since I do not care if they are unique in a single column but
need to look at both columns together would I need to change the statement
or
would the statement below still work
thanks?
alter table MyTable
add
constraint UK_MyTable UNIQUE (name, value)
column1 column2
paul 1 ok
paul 2 ok
paul 1 do not allow this entry match(value
in column1 and column2 matches value in column1 and column2 in previous
entry).
Paul G
Software engineer.
"Tom Moreau" wrote:

> Actually, you want a UNIQUE constraint on (name, value):
> alter table MyTable
> add
> constraint UK_MyTable UNIQUE (name, value)
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:65E03382-8002-4FCE-8531-6EA764838D75@.microsoft.com...
> I think I need the check constraint, I do not want any duplicates in the
> table using both columns.
> for example
> id type name value
> int varchar20 varchar20 int
> 1 test paul 2
> 2 test1 paul 3 ok
> 3 test2 tom 2 ok
> 4 test3 tom 3 ok
> 5 test4 tom 2 **do not allow
this[vbcol=seagreen]
> entry**
>
> --
> Paul G
> Software engineer.
>
> "Linchi Shea" wrote:
always[vbcol=seagreen]
> the
value
> how to
>
>
sql

how to set up unique combinational constraint

Hi I have a table and I do not want to allow entries if two column value
match, for example
table 1
id type name value
int varchar20 varchar20 int
1 test paul 2
if name is paul and value is 3 allow insert
if name is paul and value is 2 do not allow insert.
I am trying to do this with under the index/keys menu, not quite sure how to
do it and if this is correct.
Thanks.
--
Paul G
Software engineer.If you just want to prevent the combination from being inserted into the
table, you can use an INSERT trigger. If you want the condition to always
hold for all the rows in the table, you can create a CHECK constraint on the
table.
Linchi
"Paul" wrote:
> Hi I have a table and I do not want to allow entries if two column value
> match, for example
> table 1
> id type name value
> int varchar20 varchar20 int
> 1 test paul 2
> if name is paul and value is 3 allow insert
> if name is paul and value is 2 do not allow insert.
> I am trying to do this with under the index/keys menu, not quite sure how to
> do it and if this is correct.
> Thanks.
> --
> Paul G
> Software engineer.|||Consider putting a UNIQUE constraint on (name, value)
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:23467935-67F3-418E-A24E-200CE74DD637@.microsoft.com...
If you just want to prevent the combination from being inserted into the
table, you can use an INSERT trigger. If you want the condition to always
hold for all the rows in the table, you can create a CHECK constraint on the
table.
Linchi
"Paul" wrote:
> Hi I have a table and I do not want to allow entries if two column value
> match, for example
> table 1
> id type name value
> int varchar20 varchar20 int
> 1 test paul 2
> if name is paul and value is 3 allow insert
> if name is paul and value is 2 do not allow insert.
> I am trying to do this with under the index/keys menu, not quite sure how
to
> do it and if this is correct.
> Thanks.
> --
> Paul G
> Software engineer.|||I think I need the check constraint, I do not want any duplicates in the
table using both columns.
for example
id type name value
int varchar20 varchar20 int
1 test paul 2
2 test1 paul 3 ok
3 test2 tom 2 ok
4 test3 tom 3 ok
5 test4 tom 2 **do not allow this
entry**
Paul G
Software engineer.
"Linchi Shea" wrote:
> If you just want to prevent the combination from being inserted into the
> table, you can use an INSERT trigger. If you want the condition to always
> hold for all the rows in the table, you can create a CHECK constraint on the
> table.
> Linchi
> "Paul" wrote:
> > Hi I have a table and I do not want to allow entries if two column value
> > match, for example
> > table 1
> >
> > id type name value
> > int varchar20 varchar20 int
> > 1 test paul 2
> >
> > if name is paul and value is 3 allow insert
> > if name is paul and value is 2 do not allow insert.
> >
> > I am trying to do this with under the index/keys menu, not quite sure how to
> > do it and if this is correct.
> > Thanks.
> > --
> > Paul G
> > Software engineer.|||Actually, you want a UNIQUE constraint on (name, value):
alter table MyTable
add
constraint UK_MyTable UNIQUE (name, value)
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:65E03382-8002-4FCE-8531-6EA764838D75@.microsoft.com...
I think I need the check constraint, I do not want any duplicates in the
table using both columns.
for example
id type name value
int varchar20 varchar20 int
1 test paul 2
2 test1 paul 3 ok
3 test2 tom 2 ok
4 test3 tom 3 ok
5 test4 tom 2 **do not allow this
entry**
Paul G
Software engineer.
"Linchi Shea" wrote:
> If you just want to prevent the combination from being inserted into the
> table, you can use an INSERT trigger. If you want the condition to always
> hold for all the rows in the table, you can create a CHECK constraint on
the
> table.
> Linchi
> "Paul" wrote:
> > Hi I have a table and I do not want to allow entries if two column value
> > match, for example
> > table 1
> >
> > id type name value
> > int varchar20 varchar20 int
> > 1 test paul 2
> >
> > if name is paul and value is 3 allow insert
> > if name is paul and value is 2 do not allow insert.
> >
> > I am trying to do this with under the index/keys menu, not quite sure
how to
> > do it and if this is correct.
> > Thanks.
> > --
> > Paul G
> > Software engineer.|||ok thanks. Since I do not care if they are unique in a single column but
need to look at both columns together would I need to change the statement or
would the statement below still work
thanks?
alter table MyTable
add
constraint UK_MyTable UNIQUE (name, value)
column1 column2
paul 1 ok
paul 2 ok
paul 1 do not allow this entry match(value
in column1 and column2 matches value in column1 and column2 in previous
entry).
--
Paul G
Software engineer.
"Tom Moreau" wrote:
> Actually, you want a UNIQUE constraint on (name, value):
> alter table MyTable
> add
> constraint UK_MyTable UNIQUE (name, value)
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:65E03382-8002-4FCE-8531-6EA764838D75@.microsoft.com...
> I think I need the check constraint, I do not want any duplicates in the
> table using both columns.
> for example
> id type name value
> int varchar20 varchar20 int
> 1 test paul 2
> 2 test1 paul 3 ok
> 3 test2 tom 2 ok
> 4 test3 tom 3 ok
> 5 test4 tom 2 **do not allow this
> entry**
>
> --
> Paul G
> Software engineer.
>
> "Linchi Shea" wrote:
> > If you just want to prevent the combination from being inserted into the
> > table, you can use an INSERT trigger. If you want the condition to always
> > hold for all the rows in the table, you can create a CHECK constraint on
> the
> > table.
> >
> > Linchi
> >
> > "Paul" wrote:
> >
> > > Hi I have a table and I do not want to allow entries if two column value
> > > match, for example
> > > table 1
> > >
> > > id type name value
> > > int varchar20 varchar20 int
> > > 1 test paul 2
> > >
> > > if name is paul and value is 3 allow insert
> > > if name is paul and value is 2 do not allow insert.
> > >
> > > I am trying to do this with under the index/keys menu, not quite sure
> how to
> > > do it and if this is correct.
> > > Thanks.
> > > --
> > > Paul G
> > > Software engineer.
>
>|||The code I gave you is what you need, since it specifies that the uniqueness
is to be applied to the combination of both columns.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:D044B7E2-3B9A-4B19-84A9-AADDA4AEBE2A@.microsoft.com...
ok thanks. Since I do not care if they are unique in a single column but
need to look at both columns together would I need to change the statement
or
would the statement below still work
thanks?
alter table MyTable
add
constraint UK_MyTable UNIQUE (name, value)
column1 column2
paul 1 ok
paul 2 ok
paul 1 do not allow this entry match(value
in column1 and column2 matches value in column1 and column2 in previous
entry).
--
Paul G
Software engineer.
"Tom Moreau" wrote:
> Actually, you want a UNIQUE constraint on (name, value):
> alter table MyTable
> add
> constraint UK_MyTable UNIQUE (name, value)
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:65E03382-8002-4FCE-8531-6EA764838D75@.microsoft.com...
> I think I need the check constraint, I do not want any duplicates in the
> table using both columns.
> for example
> id type name value
> int varchar20 varchar20 int
> 1 test paul 2
> 2 test1 paul 3 ok
> 3 test2 tom 2 ok
> 4 test3 tom 3 ok
> 5 test4 tom 2 **do not allow
this
> entry**
>
> --
> Paul G
> Software engineer.
>
> "Linchi Shea" wrote:
> > If you just want to prevent the combination from being inserted into the
> > table, you can use an INSERT trigger. If you want the condition to
always
> > hold for all the rows in the table, you can create a CHECK constraint on
> the
> > table.
> >
> > Linchi
> >
> > "Paul" wrote:
> >
> > > Hi I have a table and I do not want to allow entries if two column
value
> > > match, for example
> > > table 1
> > >
> > > id type name value
> > > int varchar20 varchar20 int
> > > 1 test paul 2
> > >
> > > if name is paul and value is 3 allow insert
> > > if name is paul and value is 2 do not allow insert.
> > >
> > > I am trying to do this with under the index/keys menu, not quite sure
> how to
> > > do it and if this is correct.
> > > Thanks.
> > > --
> > > Paul G
> > > Software engineer.
>
>

How to set a primary key constraint in a View table using SQL Server 2005

Hi All,

I have created a table using VIEWS in SQL server 2005, now i want to be ablle to edit it in a datagrid but i cannot do so as i there is no primary key!

now does anybody know how to set a primary key constraint so i can set one of the fields as a primary key to identify the row?

many thanks

You can't apply PRIMARY KEY constriant on views.|||

Hi,

Is there any way in which i can state one of the columns to be a unique identifier? as i want to be able to edit my datagrid which is populating a VIEW table from SQL '05 but i cannot do so as i do not have a unique number to identify the row, even though one of the columns in the table is a PK in its original table.

any ideas?

|||

If you just want a unique column to identify a row,newid() function is good for you, which can be used in your CREATE VIEW statements:

use northwind
go
create view v_test as
select o.OrderID, C.ContactName,newid() as ColID
from Orders o join Customers c
on o.CustomerID=C.CustomerID