SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Wizard jonese's Avatar
    Join Date
    Jul 1999
    Location
    Powder Springs GA (Atlanta)
    Posts
    1,238
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MS SQL Error when mapping out foreign key relationships

    Hey all i'm in Enterprise manager and i'm using the diagram feature to map out foreign key relaionships. and when i try to map a relationship between two tables i get the follow error...

    anyone got any ideas on how to get around this?

    'group_lookup' table
    - Unable to create relationship 'FK_group_lookup_users'.
    ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER TABLE statement conflicted with COLUMN FOREIGN KEY constraint FK_group_lookup_users'. The conflict occurred in database 'FDIC_dev', table 'users', column 'id'.

  2. #2
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ALTER TABLE statement conflicted with COLUMN FOREIGN KEY constraint FK_group_lookup_users'

    So show us the DDL for the 'users' table!

  3. #3
    SitePoint Wizard jonese's Avatar
    Join Date
    Jul 1999
    Location
    Powder Springs GA (Atlanta)
    Posts
    1,238
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    DDL?? not following you there?

  4. #4
    SitePoint Wizard jonese's Avatar
    Join Date
    Jul 1999
    Location
    Powder Springs GA (Atlanta)
    Posts
    1,238
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    All,
    Ok i have four tables. (actually i have more than that but for this we only have four).

    * users table
    key = id
    * group table
    key = id
    * section table
    key = id
    * permission table
    key = user_id
    key = group_id
    key = section_id

    i am using MS SQL Enterprise Manager and chosing the Diagram option for my DB to map out relationships between tables. Now when i try to map a relationship from the user.id to the permission.user_id field and save it i get the following error:

    'group_lookup' table
    - Unable to create relationship 'FK_group_lookup_users'.
    ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER TABLE statement conflicted with COLUMN FOREIGN KEY constraint FK_group_lookup_users'. The conflict occurred in database 'FDIC_dev', table 'users', column 'id'.

    I'm not sure what could be wrong anyone have any ideas? Sorry i wasn't clear on my first post.

  5. #5
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    DDL == Data Definiton Language. Your CREATE TABLE statements, ALL OF IT! Do not edit, paste, including key and other scripts.

  6. #6
    SitePoint Wizard jonese's Avatar
    Join Date
    Jul 1999
    Location
    Powder Springs GA (Atlanta)
    Posts
    1,238
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    group lookup table
    Code:
    /****** Object:  Table [dbo].[group_lookup]    Script Date: 9/30/2002 2:17:07 PM ******/
    CREATE TABLE [dbo].[group_lookup] (
    	[user_id] [bigint] NOT NULL ,
    	[group_id] [bigint] NOT NULL ,
    	[section_id] [bigint] NOT NULL ,
    	[accepted_stamp] [datetime] NULL ,
    	[requested_stamp] [datetime] NULL 
    ) ON [PRIMARY]
    GO
    users table
    Code:
    /****** Object:  Table [dbo].[ako_account_types]    Script Date: 9/30/2002 2:16:12 PM ******/
    CREATE TABLE [dbo].[ako_account_types] (
    	[id] [bigint] IDENTITY (1, 1) NOT FOR REPLICATION  NOT NULL ,
    	[account_short] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    	[account_long] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 
    ) ON [PRIMARY]
    GO
    
    /****** Object:  Table [dbo].[users]    Script Date: 9/30/2002 2:16:12 PM ******/
    CREATE TABLE [dbo].[users] (
    	[id] [bigint] IDENTITY (1, 1) NOT FOR REPLICATION  NOT NULL ,
    	[fname] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[lname] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[nickname] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[login] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[password] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[ako_type_id] [bigint] NOT NULL ,
    	[service] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[rank] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[mos] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[unit] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[location] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[phone] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[email] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[stamp] [datetime] NULL 
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    GO

  7. #7
    SitePoint Wizard jonese's Avatar
    Join Date
    Jul 1999
    Location
    Powder Springs GA (Atlanta)
    Posts
    1,238
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    GOT IT! Thanks to a message off this BBS I went though and checked the data in the tables. Thanks to poor maintenance on the last server there were some referential integrity issues. Once I clean those up it worked... Thanks for the messages and thoughts...


Bookmarks

Posting Permissions

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