Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
fmardani
Constraint Violating Yak Guru
433 Posts |
 Posted - 2006-05-31 : 09:07:41
|
| I have created a table as follows:CREATE TABLE [dbo].[tbl_PatientManagement] ( [ID] [int] IDENTITY (1, 1) NOT NULL , [Year] [int] NOT NULL , [YearPeriod] [int] NOT NULL , [Code] [varchar] (3) ON [PRIMARY]GOID field is the primary key.How can I alter thge table so that the fields Year,YearPeriod, Code become a key so that no two records will have the same values for these three columns?i.e.1,2006,5,'code1'2,2006,5,'code2'3,2006,6,'code1'4,2006,5,'code3'The table should not allow something like:1,2006,5,'code1'2,2006,5,'code1'I think the best thing is to make these three fields as PK and take out the PK from the ID field but leave it as identity.Do you agree?Thanks |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-05-31 : 09:21:10
|
| Nope. You can create a unique index on these fields.You can change the PK if you wish but the other needs to have a unique index.These can't be a PK as they stand as Code allows nulls (probbaly) but can have a unique index (note that for indexes nulls are considered equal).create unique index ix on tbl_PatientManagement (Year,YearPeriod, Code)==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-05-31 : 09:43:08
|
| You could also make a unique contraint, if there is no need to make another index.ALTER TABLE [dbo].[tbl_PatientManagement]ADD CONSTRAINT [IX_NameOfUniqueConstraint] UNIQUE NONCLUSTERED ( [Year], [YearPeriod], [Code] ) ON [PRIMARY] |
|
|
|
fmardani
Constraint Violating Yak Guru
433 Posts |
Posted - 2006-05-31 : 10:29:31
|
| solved.thanks |
|
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2006-05-31 : 22:19:43
|
quote:
Originally posted by Peso
You could also make a unique contraint, if there is no need to make another index.
I agree you should make a constraint because that is what you have stated is the requirement. An index is an implementation detail and 'behind the scenes' stuff whereas a constraint is a business rule. Chances are SQL Server will create an index to enforce your constraint anyway. I suspect (but don't know) creating an index on a constrained column simply renames the interal index. |
|
|
|
|
|
|
|
|
PK