Professional Web Applications Themes

Transact-SQL syntax question - Microsoft SQL / MS SQL Server

Nico, Please Refer [url]http://www.sqlmag.com/Articles/Index.cfm?ArticleID=8687&pg=1[/url] Hope this helps. Thanks Rajan Murthy "Nico den Boer" <nicodenboer> wrote in message news:uHA#vI#PDHA.1216TK2MSFTNGP11.phx.gbl... > I have 2 tables in a existing database with only some indexes on them; > > CREATE TABLE [dbo].[deelname] ( > [primarykey] [int] IDENTITY (1, 1) NOT NULL , > [lesgroep] [int] NULL > ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] > GO > CREATE UNIQUE INDEX [primarykey] ON [dbo].[deelname]([primarykey]) ON > [PRIMARY] > GO > > CREATE TABLE [dbo].[lesgroep] ( > [primarykey] [int] IDENTITY (1, 1) NOT NULL , > [definitief] [tinyint] NULL > ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] > GO > ...

  1. #1

    Default Re: Transact-SQL syntax question

    Nico,

    Please Refer [url]http://www.sqlmag.com/Articles/Index.cfm?ArticleID=8687&pg=1[/url]

    Hope this helps.

    Thanks
    Rajan Murthy


    "Nico den Boer" <nicodenboer> wrote in message
    news:uHA#vI#PDHA.1216TK2MSFTNGP11.phx.gbl...
    > I have 2 tables in a existing database with only some indexes on them;
    >
    > CREATE TABLE [dbo].[deelname] (
    > [primarykey] [int] IDENTITY (1, 1) NOT NULL ,
    > [lesgroep] [int] NULL
    > ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    > GO
    > CREATE UNIQUE INDEX [primarykey] ON [dbo].[deelname]([primarykey]) ON
    > [PRIMARY]
    > GO
    >
    > CREATE TABLE [dbo].[lesgroep] (
    > [primarykey] [int] IDENTITY (1, 1) NOT NULL ,
    > [definitief] [tinyint] NULL
    > ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    > GO
    > CREATE UNIQUE INDEX [primarykey] ON [dbo].[lesgroep]([primarykey]) ON
    > [PRIMARY]
    > GO
    >
    > (of course there are more fields and indexes in the real table)
    >
    > I would like to ensure ref. integrity between the parent (lesgroep) and
    > child (deelname) table. More specific, a record added in the child table
    > should only be inserted when a parent record exists.
    >
    > Never done this in MS-SQL server before.
    > Tried to do it like this (which doesn't work):
    >
    > ALTER TABLE deelname
    > ALTER COLUMN lesgroep
    > FOREIGN KEY lesgroep REFERENCES lesgroep(primarykey) ON DELETE NO ACTION
    >
    > The docs don't give a clear example or explanation about how to do this.
    > How can I ?
    >
    > Any help would be greatly appreciated...
    >
    > Nico
    >
    >
    >

    Rajan Murthy Guest

  2. #2

    Default Re: Transact-SQL syntax question

    You best create a primary key on the TABLE lesgroep as well:

    ALTER TABLE lesgroep ADD CONSTRAINT PK_lesgroep PRIMARY KEY (primarykey)
    GO
    --Unique index is redundant now there is a primary key:
    DROP INDEX lesgroep.[primarykey]
    GO
    -- Create the Foreign Key
    ALTER TABLE deelname ADD CONSTRAINT FK_deelname_lesgroep FOREIGN KEY
    (lesgroep) REFERENCES lesgroep (primarykey)
    GO
    -- And create a primary key on deelname as well:
    ALTER TABLE deelname ADD CONSTRAINT PK_deelname PRIMARY KEY (primarykey)
    GO
    --Unique index is redundant now there is a primary key:
    DROP INDEX deelname.[primarykey]
    GO



    --
    Jacco Schalkwijk MCDBA, MCSD, MCSE
    Database Administrator
    Eurostop Ltd.


    "Nico den Boer" <nicodenboer> wrote in message
    news:uHA#vI#PDHA.1216TK2MSFTNGP11.phx.gbl...
    > I have 2 tables in a existing database with only some indexes on them;
    >
    > CREATE TABLE [dbo].[deelname] (
    > [primarykey] [int] IDENTITY (1, 1) NOT NULL ,
    > [lesgroep] [int] NULL
    > ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    > GO
    > CREATE UNIQUE INDEX [primarykey] ON [dbo].[deelname]([primarykey]) ON
    > [PRIMARY]
    > GO
    >
    > CREATE TABLE [dbo].[lesgroep] (
    > [primarykey] [int] IDENTITY (1, 1) NOT NULL ,
    > [definitief] [tinyint] NULL
    > ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    > GO
    > CREATE UNIQUE INDEX [primarykey] ON [dbo].[lesgroep]([primarykey]) ON
    > [PRIMARY]
    > GO
    >
    > (of course there are more fields and indexes in the real table)
    >
    > I would like to ensure ref. integrity between the parent (lesgroep) and
    > child (deelname) table. More specific, a record added in the child table
    > should only be inserted when a parent record exists.
    >
    > Never done this in MS-SQL server before.
    > Tried to do it like this (which doesn't work):
    >
    > ALTER TABLE deelname
    > ALTER COLUMN lesgroep
    > FOREIGN KEY lesgroep REFERENCES lesgroep(primarykey) ON DELETE NO ACTION
    >
    > The docs don't give a clear example or explanation about how to do this.
    > How can I ?
    >
    > Any help would be greatly appreciated...
    >
    > Nico
    >
    >
    >

    Jacco Schalkwijk Guest

  3. #3

    Default Re: Transact-SQL syntax question

    Rajan:
    Thanks for the link. It's a very good article.

    Vishal:
    In the table are character and text fields too.
    Thanks for the hint !

    Jacco:
    Thanks for this detailed answer !

    Kind regards,
    Nico den Boer


    Nico den Boer Guest

Similar Threads

  1. Question about E4X syntax.
    By rpierich in forum Macromedia Flex General Discussion
    Replies: 7
    Last Post: May 27th, 06:12 AM
  2. Question on syntax...
    By Jonathan Villa in forum PHP Development
    Replies: 1
    Last Post: August 20th, 03:15 PM
  3. Transact-SQL Debugger
    By Neeraj in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 14th, 01:14 AM
  4. SQL Syntax Question???
    By Kory in forum Macromedia Dreamweaver
    Replies: 2
    Last Post: July 10th, 10:57 PM
  5. resource low - Transact-SQL
    By Otto Naesset in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 10th, 01:31 PM

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
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139