January 18, 2007 at 2:15 pm
Need to write expression to create constraint for multiple columns "email" "password". The rule is combination of email and password must be unique.
There is another column "UID" that is a Primary Key.
January 18, 2007 at 2:51 pm
You could use a trigger...
CREATE TRIGGER t_my_table ON my_table
FOR INSERT, UPDATE
AS
BEGIN
IF EXISTS (
SELECT 1
FROM my_table f
JOIN INSERTED i
ON f.email = i.email
AND f.password = i.password
AND f.UID i.UID
)
BEGIN
ROLLBACK TRAN;
Print ('No.. bad!')
-- Raiserror or do something else....
END
END
January 19, 2007 at 12:43 am
Create a 2 column unique index. While there can only be one pimary key on a table, there can be multiple indexes.
CREATE UNIQUE INDEX idx_MyTable_EmailPassword
ON MyTable (email, Password)
Trigger will work, but is not necessary. Triggers can be slow. bonuswith an index is you get fast searches as well as enforcement of the uniqueness.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 19, 2007 at 7:09 am
I agree with Ms. Shaw. Who wouldn't. You may also want to consider using a unique constraint:
ALTER TABLE [x]
ADD CONSTRAINT [y]
UNIQUE NONCLUSTERED ([emailaddress],[password]) ON [PRIMARY]
Then you get the index suggested above and you get the ability to reference this with foreign key's, if you want to. You may also realize a performance benefit on large scale inserts since constraints are checked before indexes (key word: may).
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply