March 4, 2010 at 4:58 am
go
create table Band(Band_ID varchar(6),Band_Name varchar(25),Bands_OtherNames varchar(100),Found datetime,Genre varchar(30));
insert into Band values('ar1','Linkin Park','Hybrid Theory(2000)/Fuse(1996)','2000-01-01','Alternative Rock/Rap-Rock/Rock')
select*from Band
go
the check constraint is not working
alter table Band add constraint ch_2 check (Found<'2000-01-01')
the error message=Msg 547, Level 16, State 0, Line 1
The ALTER TABLE statement conflicted with the CHECK constraint "ch_2". The conflict occurred in database "music", table "dbo.Band", column 'Found'.
whats wrong with it?
March 4, 2010 at 5:13 am
If you had defined the check constraint before executing the insert statement then it would have worked fine.
The problem is in check constraint you are specifying a condition that the date should be less than '2000-01-01' , whereas there is already a record in table where date='2000-01-01'.
Delete the record from the table and then define the check constraint.
March 4, 2010 at 5:30 am
thx dude
What a stupid question that was
March 4, 2010 at 5:42 am
thx before dude now i hava another problem
The Foreign key constraint cannot be created
go
create table Artist(Artist_ID int identity,Artist_FName varchar(30),Artist_Surname varchar(30),Artist_OtherNames varchar(40),Artist_DOB datetime);
go
create table Band(Band_ID varchar(6),Band_Name varchar(25),Bands_OtherNames varchar(100),Found datetime,Genre varchar(30));
go
go
alter table Band alter column Band_ID varchar(6) not null
alter table Band add constraint pk_band Primary Key(Band_ID)
go
go
alter table Artist add constraint pk_Artist Primary key(Artist_ID) --problem is here
go
error message
Msg 1769, Level 16, State 1, Line 1
Foreign key 'fk_Band' references invalid column 'Artist_ID' in referencing table 'Band'.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
March 4, 2010 at 5:50 am
I could not see any script which creates the foreign key.
Cheers,
Satnam
March 4, 2010 at 6:04 am
sorry pal i just misplaced it
alter table Band add Constraint fk_Band Foreign key(Artist_ID) references Artist(Artist_ID)
March 4, 2010 at 6:12 am
Hello,
Where is the column named Artist_ID in the table Band?
Your script is:
alter table Band add Constraint fk_Band Foreign key(Artist_ID) references Artist(Artist_ID)
The column name specified as foreign key should be present in the table Band.
Cheers,
Satnam
March 4, 2010 at 6:14 am
Also the data type of the primary key and the foreign key column
should be the same.
Cheers,
Satnam
March 4, 2010 at 6:18 am
oh i understood
Artist_ID would'n implicitly get into the Table Band
thank u very much
March 4, 2010 at 6:20 am
Welcome........
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply