Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2005
    Posts
    7

    Question Unanswered: Foreign Key is an uniqueidentifier

    Hello everyone.
    I have 2 tables in MSSQL Server 2000, Products(idProduct, idProductType, name, quatity) and ProductsType(idProductType, name).

    idXXXX are the primery keys and the data type is uniqueidentifier.

    In table Products, the collum idProductType is a foreign key refering table ProductsType.

    I can insert as many product types as i want without any problems.
    Insert INTO ProductsType(name) VALUES('milk')

    When inserting products i use the idProductType that was automatacly generated in last insert.
    INSERT INTO Products (uuidProductType,name,quantity,)
    VALUES ('AD9388A3-CA86-482D-B57F-6FA068E7D405','President Milk 50cl',500)

    but i got the following error in SQL Query Analyzer:
    Server: Msg 8169, Level 16, State 2, Line 1
    Syntax error converting from a character string to uniqueidentifier.

    I have searched the forum and i cant find any solution. i need to use uniqueidentifer data types because replication with PDA devices.

    Any tips?

    Thank you in advance.
    Helder

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    This works fine for me:
    Code:
    declare	@MyGuid uniqueidentifier
    set	@MyGuid = 'AD9388A3-CA86-482D-B57F-6FA068E7D405'
    ...so the problem is in your code or your table definition, not with your GUID.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Nov 2005
    Posts
    7
    Well i tried what you say in this way:

    declare @MyGuid uniqueidentifier
    set @MyGuid = 'F03EF117-F003-4965-B1D7-76E9E5A786C0'

    INSERT INTO Products(idProductType,name,quantaty) VALUES (@MyGUID,'MILK 50cl',500)

    and i get this:
    Server: Msg 8169, Level 16, State 2, Line 4
    Syntax error converting from a character string to uniqueidentifier.


    Im constructing the tables like this:
    create table "dbo"."Products" (
    "idProduct" uniqueidentifier ROWGUIDCOL default 'newid()' not null,
    "idProductType" uniqueidentifier not null,
    "name" char(20) not null,
    "quantity" int not null) ON 'PRIMARY'
    go
    alter table "dbo"."Products"
    add constraint "Products_PK" primary key clustered ("idProduct")

    create table "dbo"."ProductsType" (
    "idProductType" uniqueidentifier ROWGUIDCOL default newid() not null,
    "name" char(20) not null) ON 'PRIMARY'
    go
    alter table "dbo"."ProductsType"
    add constraint "ProductsType_PK" primary key clustered ("idProductType")

    alter table "dbo"."Products"
    add constraint "ProductsType_Products_FK1" foreign key (
    "idProductType")
    references "dbo"."ProductsType" (
    "idProductType") on update no action on delete no action
    go

    Thank you!

  4. #4
    Join Date
    Jun 2003
    Posts
    269

    Thumbs up Re

    change ur insert query,

    declare @MyGuid uniqueidentifier
    set @MyGuid = 'F03EF117-F003-4965-B1D7-76E9E5A786C0'

    INSERT INTO Products(idProduct,idProductType,name,quantity) VALUES (newid(),@MyGuid,'MILK 50cl',500)
    Last edited by mallier; 11-11-05 at 11:51.
    I love cursor,though I never use it.Cos' I dont want to misuse it.
    ----------------------------------------------

    ----cheers
    ----http://mallier.blogspot.com

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    The error is occuring because you are declaring your default to be 'newid()' as a STRING. You should just declare it as newid(), without the quotes. Apparently, even though you are specifying a value for the input GUID, it chokes on trying to create a default guid value of 'newid()'.

    This, for instance, works fine:
    Code:
    create table dbo.Products
    	(idProduct uniqueidentifier ROWGUIDCOL default newid() not null,
    	idProductType uniqueidentifier not null,
    	name char(20) not null,
    	quantity int not null) ON 'PRIMARY' 
    
    INSERT INTO Products (idProductType,name,quantity)
    VALUES ('AD9388A3-CA86-482D-B57F-6FA068E7D405','President Milk 50cl',500)
    D'oh! That was a good one. Took me a while to find it.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Nov 2005
    Posts
    7
    Ohh god..what a fully mistake.

    thank you a lot for ypur help

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •