Re: A trigger that prevents a duplicate value based on a foreign k
From: Tom Moreau (tom_at_dont.spam.me.cips.ca)
Date: 10/06/04
- Next message: Aaron [SQL Server MVP]: "Re: least function"
- Previous message: Hugo Kornelis: "Re: percentages of top 10 tallies"
- In reply to: Toby Herring: "Re: A trigger that prevents a duplicate value based on a foreign k"
- Next in thread: Hugo Kornelis: "Re: A trigger that prevents a duplicate value based on a foreign k"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 6 Oct 2004 14:48:33 -0400
If there are dupes, it won't work WITH NOCHECK either, since it creates a
unique index to support the constraint.
-- Tom --------------------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA SQL Server MVP Columnist, SQL Server Professional Toronto, ON Canada www.pinnaclepublishing.com "Toby Herring" <therring*@*teletrack.com> wrote in message news:OrspVR9qEHA.2904@TK2MSFTNGP15.phx.gbl... See if it works WITH NOCHECK instead of WITH CHECK. I would almost bet you already have duplicates in the table, which would prevent you from creating the constraint WITH CHECK. -- Toby Herring MCDBA, MCSD, MCP+SB Need a Second Life? http://secondlife.com/ss/?u=03e0e5b303c234bf08e80ee40119a65e "beacon-dartmouth" <beacondartmouth@discussions.microsoft.com> wrote in message news:6671C471-3CCF-4DD0-946A-1E70DD39BC48@microsoft.com... > Thank you for the suggestion, Toby. > > I tried it as: > > ALTER TABLE A_T_FIELD WITH CHECK ADD CONSTRAINT CK_A_T_FIELD UNIQUE > (Logical_Name, Table_ID) > > but the server returns "Error validating constraint 'CK_A_T_FIELD'" > without > any more clues. Clicking Help online doesn't offer any other possible > reasons > for the error.. > > I'm the dbo for the database so I should have permissions to commit > changes. > I don't think column modifications would cause data loss, or result in an > index greater than 900 bytes . I'm clue less. > > > "Toby Herring" wrote: > >> A unique constraint/index can span multiple columns: >> >> ALTER TABLE Field >> WITH CHECK ADD CONSTRAINT UN_TableField UNIQUE (Table_ID, Logical_Name) >> >> -- >> Toby Herring >> MCDBA, MCSD, MCP+SB >> Need a Second Life? >> http://secondlife.com/ss/?u=03e0e5b303c234bf08e80ee40119a65e >> >> >> "beacon-dartmouth" <beacon-dartmouth@discussions.microsoft.com> wrote in >> message news:A4BFF8BB-5795-414D-9F5B-4D33E82D9F39@microsoft.com... >> >I need to come up with a trigger that would prevent an insert if the >> >value >> >of >> > the column "Logical_Name" is a duplicate of another with the same value >> > in >> > the "Table_ID" column. >> > >> > In other words, I have two tables (Table and Field) where there may be >> > one >> > or more instances of a field for one table. I need to prevent two >> > fields >> > with >> > the same name in the same table. >> > >> > I tried using constraints but the "uniqueness" would to all the values >> > and >> > not just to those with a foreign key ID. >> > >> > Any examples??? >> >> >>
- Next message: Aaron [SQL Server MVP]: "Re: least function"
- Previous message: Hugo Kornelis: "Re: percentages of top 10 tallies"
- In reply to: Toby Herring: "Re: A trigger that prevents a duplicate value based on a foreign k"
- Next in thread: Hugo Kornelis: "Re: A trigger that prevents a duplicate value based on a foreign k"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
