Primary key and Foreign Key relation..URGENT!!!

[chaitra]

Hi,

I have two tables.
CREATE TABLE Service(
SERVId varchar (10) NOT NULL,
SERVName varchar (30) NOT NULL,
SERVDesc TEXT NULL,
PRIMARY KEY(SERVId)
)



CREATE TABLE New(
SERVId varchar (10) NOT NULL,
NDId varchar (10) NOT NULL,
NDName varchar (30) NOT NULL,
NDDesc text NULL,
PRIMARY KEY(SERVId,NDId)
FOREIGN KEY(SERVId)
REFERENCES Service(SERVId)
)


Msg 547, Level 16, State 2
DELETE statement conflicted with COLUMN REFERENCE constraint 'FK__Newdev__SERVId__4939E6D2'. The conflict occurred in database 'test', table 'New', column 'SERVId'
Command has been aborted.


The same problem occurs even if the second table i.e. new is declared as follows, i.e created with only one primary key:
CREATE TABLE New(
SERVId varchar (10) NOT NULL,
NDId varchar (10) NOT NULL,
NDName varchar (30) NOT NULL,
NDDesc text NULL,
PRIMARY KEY(NDId)
FOREIGN KEY(SERVId)
REFERENCES Service(SERVId)
)


what might be the problem? But the same relation works on MS Access.
Is there anyother way of declaring this kind of relationship i.e. primary and foreign key?Can anyone pl. help me in solving this problem of foreign key relation.

thanks in advance
-chaitra

------------
chaitra at 3/11/99 7:47:50 AM

Hi,

I have two tables.
CREATE TABLE Service(
SERVId varchar (10) NOT NULL,
SERVName varchar (30) NOT NULL,
SERVDesc TEXT NULL,
PRIMARY KEY(SERVId)
)



CREATE TABLE New(
SERVId varchar (10) NOT NULL,
NDId varchar (10) NOT NULL,
NDName varchar (30) NOT NULL,
NDDesc text NULL,
PRIMARY KEY(SERVId,NDId)
FOREIGN KEY(SERVId)
REFERENCES Service(SERVId)
)


Msg 547, Level 16, State 2
DELETE statement conflicted with COLUMN REFERENCE constraint 'FK__Newdev__SERVId__4939E6D2'. The conflict occurred in database 'test', table 'New', column 'SERVId'
Command has been aborted.


The same problem occurs even if the second table i.e. new is declared as follows, i.e created with only one primary key:
CREATE TABLE Service(
SERVId varchar (10) NOT NULL,
SERVName varchar (30) NOT NULL,
SERVDesc TEXT NULL,
PRIMARY KEY(SERVId)
)

CREATE TABLE New(
SERVId varchar (10) NOT NULL,
NDId varchar (10) NOT NULL,
NDName varchar (30) NOT NULL,
NDDesc text NULL,
PRIMARY KEY(NDId)
FOREIGN KEY(SERVId)
REFERENCES Service(SERVId)
)


what might be the problem? But the same relation works on MS Access.
Is there anyother way of declaring this kind of relationship i.e. primary and foreign key?Can anyone pl. help me in solving this problem of foreign key relation.


Solution:
--------------
Try running the commands below:

CREATE TABLE Service(
SERVId varchar (10) NOT NULL,
SERVName varchar (30) NOT NULL,
SERVDesc TEXT NULL,
PRIMARY KEY(SERVId)
)



CREATE TABLE New(
SERVId varchar (10) NOT NULL,
NDId varchar (10) NOT NULL,
NDName varchar (30) NOT NULL,
NDDesc text NULL,
PRIMARY KEY(SERVId,NDId)
constraint service_fk_constraint
REFERENCES Service(SERVId)
)


Adois

Himaushu





thanks in advance
-chaitra

[Gregory]

ehhh....people....people...

and what exactly are you trying to do?
looks like you're trying to delete record from Parent table (Service table), and you have child records attached to it (in New table).
you need to delete child records first!

reason it works in Access, simply because, when you set a relationship in Access you have an option for a relashionship - 'Cascade Delete Related Records' - which simply means, when you're deleting record(s) from
Parent table, Access will delete attached records from Child table.

I hope that will help.

------------
chaitra at 3/11/99 7:47:50 AM

Hi,

I have two tables.
CREATE TABLE Service(
SERVId varchar (10) NOT NULL,
SERVName varchar (30) NOT NULL,
SERVDesc TEXT NULL,
PRIMARY KEY(SERVId)
)



CREATE TABLE New(
SERVId varchar (10) NOT NULL,
NDId varchar (10) NOT NULL,
NDName varchar (30) NOT NULL,
NDDesc text NULL,
PRIMARY KEY(SERVId,NDId)
FOREIGN KEY(SERVId)
REFERENCES Service(SERVId)
)


Msg 547, Level 16, State 2
DELETE statement conflicted with COLUMN REFERENCE constraint 'FK__Newdev__SERVId__4939E6D2'. The conflict occurred in database 'test', table 'New', column 'SERVId'
Command has been aborted.


The same problem occurs even if the second table i.e. new is declared as follows, i.e created with only one primary key:
CREATE TABLE New(
SERVId varchar (10) NOT NULL,
NDId varchar (10) NOT NULL,
NDName varchar (30) NOT NULL,
NDDesc text NULL,
PRIMARY KEY(NDId)
FOREIGN KEY(SERVId)
REFERENCES Service(SERVId)
)


what might be the problem? But the same relation works on MS Access.
Is there anyother way of declaring this kind of relationship i.e. primary and foreign key?Can anyone pl. help me in solving this problem of foreign key relation.

thanks in advance
-chaitra