• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 420
  • Last Modified:

table constraints

I have Table A like this

Id, Name, ClassName
1, Neil, English
1, Neil, French
2, Rob, Spanish
2,Rob, English

I want to ensure that Id is unque to the name meaning only Neil can have id 1  no one else. Any idea how to achieve that ?
2 Solutions
Use a trigger...
create trigger tablea_trg_iu
on tablea
as begin
if exists (select * from tablea t, inserted i where t.Id = i.Id and t.Name != i.Name) rollback trigger with raiserror 20000 "id/name must be unique"

Open in new window

Jan FranekCommented:
Well, it's badly designed table. You should have three tables - one for students, second for classes and third for binding students to classes.

To expand on what Jan said, you could easily use two tables (in this case).

Using three tables:

Students: Id, Name (better named StudentId and StudentName)
Classes: ClassId, ClassName
Xref: Id, ClassId (StudentId, ClassId)

Using two tables:

Students: Id, Name (better named StudentId and StudentName
Classes: Id, ClassName (StudentId and ClassName)

The three-table version will use less database space and make querying based on a class easier.

There are a lot of good points from wilcoxon and jan

If this is an academic excerise rather than a real world exercise.  You may simply look to do:

create unique index idx1 on tableA (Id, Name, ClassName)

Note:  You would still need to control the Id somehow (programmatically, identity, etc)

Also, I could interpret your question as follows.  I want to have a unique id (not necessarily sequential) for each student.  As a result, I could take jan and wilcoxon's idea of a "Students" table (containing StudentId and StudentName) and have the StudentId field be an "identity" column(or integer field that is controlled).

Bottom line:  What was the real purpose of your question?  Was it academic and the instructor simply wanted you to have a "unique" index or was it real world and you should incorporate jan and wicoxon thoughts (normalization) and some indexes, triggers, etc.

Either way, let us know.

For the 3 table solution, a unique index on Name would have to be added to prevent 1,Neil and 3,Neil from simultaneously existing.

In the real world, it's just the other way around, you would have assume the occasional duplicate John Smith type name and instead create a unique index on the Students.Id field

You could/should also then add referential integrity constraints to make sure students and classes are defined before they're used in the Xref table.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now