Incorrect syntax near 'ModifiedDate'

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Incorrect syntax near 'ModifiedDate'

Author

Topic

SalasJ
Starting Member

15 Posts

Posted - 2011-05-13 : 15:43:32

I am trying to create a table in SQL 2008 but is giving me an error stating: 'Incorrect syntax near 'ModifiedDate' I want the modified date to get current date from sql server automatically whenever I perform an insert command. Below is my Create command. Please advice. Thanks.

CREATE TABLE LOC_Apps_Test
(
App_ID int NOT NULL PRIMARY KEY,
AppName varchar(50) NOT NULL,
AppType_ID int NOT NULL FOREIGN KEY
ModifiedDate datetime NOT NULL DEFAULT GETDATE()
)

tmaiden
Yak Posting Veteran

86 Posts

Posted - 2011-05-13 : 15:45:01

missing a comma after AppType_ID int NOT NULL FOREIGN KEY

SalasJ
Starting Member

15 Posts

Posted - 2011-05-13 : 15:59:19

Thanks tbmaiden but now is giving me 'Incorrect syntax near ','

X002548
Not Just a Number

15586 Posts

Posted - 2011-05-13 : 16:01:30

there's a couple of things


CREATE TABLE myTable99(AppType_ID int PRIMARY KEY)
GO

CREATE TABLE LOC_Apps_Test
(
  App_ID int NOT NULL PRIMARY KEY
, AppName varchar(50) NOT NULL
, AppType_ID int NOT NULL		
, ModifiedDate datetime NOT NULL DEFAULT (GETDATE())
)
GO

ALTER TABLE [dbo].[LOC_Apps_Test]  WITH CHECK ADD FOREIGN KEY([AppType_ID])
REFERENCES [dbo].[myTable99] ([AppType_ID])
GO

DROP TABLE LOC_Apps_Test, myTable99
GO

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

SalasJ
Starting Member

15 Posts

Posted - 2011-05-13 : 16:10:54

Thanks Brett! but question before I execute the drop command, don't I only want to drop the 'mytable99' only?

DROP TABLE LOC_Apps_Test, myTable99
GO

tmaiden
Yak Posting Veteran

86 Posts

Posted - 2011-05-13 : 16:15:26

I name my default keys since I don't like the names that SQL makes.


CREATE TABLE LOC_Apps_Test (
    App_ID INT NOT NULL,
    AppName VARCHAR(50) NOT NULL,
    AppType_ID INT NOT NULL,		
    ModifiedDate DATETIME NOT NULL
    CONSTRAINT DF_ModifiedDate DEFAULT (GETDATE()),
    CONSTRAINT PK_App_ID PRIMARY KEY CLUSTERED ( App_ID ASC ),
    CONSTRAINT FK_LOC_Apps_Test_AppType_ID FOREIGN KEY ( AppType_ID ) REFERENCES dbo.AppType_ID_TABLE(AppType_ID),
)

X002548
Not Just a Number

15586 Posts

Posted - 2011-05-13 : 16:26:10

it's just a test since your table doesn't exist.

Question, is what is the foreign key relationship would be (instead of myTable99?)???

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

SalasJ
Starting Member

15 Posts

Posted - 2011-05-13 : 16:52:15

The relationship would be that it needs to appear in another table...since there would two application types (mainframe and distributed applications)

SalasJ
Starting Member

15 Posts

Posted - 2011-05-18 : 13:59:38

Does my response above makes sense at all? I am kinda new at this....Please advise

X002548
Not Just a Number

15586 Posts

Posted - 2011-05-18 : 14:53:43

You need to tell us what the other table that is being referenced is. We don't know

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-05-18 : 15:27:49

While defining foreign key relationship you must have to specify the table and corresponding column which needs to be referenced. Brett demonstrated the way you can get this done, by introducing the dummy table. You'll need to replace the "myTable99" with the original table name having "AppType_ID" as primary Key.

Cheers
MIK

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources