Home » SQL & PL/SQL » SQL & PL/SQL » Constraint Error
icon8.gif  Constraint Error [message #233391] Thu, 26 April 2007 00:51 Go to next message
Power Penguin
Messages: 33
Registered: April 2007
Location: Australia
Member
Hi Guys

I have an assignment for uni and I keep getting a constraint error im pretty sure its with the foreign key when I try and implement this table:

CREATE TABLE Transactions (TransNo number (12), LoanDate varchar (10), StaffName char (20), ReturnDate varchar (10),
CONSTRAINT PK_TRANSNO PRIMARY KEY (TransNo));
CONSTRAINT FK_STAFF_NAME FOREIGN KEY (StaffName) REFERENCES (Staff) Name;




This is the table the foreign key relates to:

CREATE TABLE Staff (Name char (20), PayRate decimal (7,2), WorkDates varchar (Cool, ShiftStart varchar (Cool, ShiftEnd varchar (Cool,
CONSTRAINT PK_NAME PRIMARY KEY (Name));





Any help would be massivly appreciated.


Thanks.
Re: Constraint Error [message #233394 is a reply to message #233391] Thu, 26 April 2007 01:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ Read How to format your posts and edit your post
2/ Of course Oracle version is irrelevant
3/ Of course error number/message is irrelevant
4/ Of course posting what you done when you got the error will not help

Regards
Michel
Re: Constraint Error [message #233395 is a reply to message #233391] Thu, 26 April 2007 01:05 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Show a copy-paste from sqlplus to show us what error you get

[Update: hm, double with Michel]

[Updated on: Thu, 26 April 2007 01:06]

Report message to a moderator

Re: Constraint Error [message #233401 is a reply to message #233391] Thu, 26 April 2007 01:09 Go to previous messageGo to next message
Power Penguin
Messages: 33
Registered: April 2007
Location: Australia
Member
Hi Sorry.

SqlPlus on an Oracle machine - we access it using ssh, from what I understand its oracle 10. Sorry I dont have any further info on it.


ERROR:

SQL> CREATE TABLE Transactions (TransNo number (12), LoanDate varchar (10), StaffName char (20), ReturnDate varchar (10),
CONSTRAINT PK_TRANSNO PRIMARY KEY (TransNo));
CONSTRAINT FK_STAFF_NAME FOREIGN KEY (StaffName) REFERENCES (Staff) Name;
  2  
Table created.

SQL> SP2-0734: unknown command beginning "CONSTRAINT..." - rest of line ignored.





Tables:

CREATE TABLE Transactions (TransNo number (12), LoanDate varchar (10), StaffName char (20), ReturnDate varchar (10),
CONSTRAINT PK_TRANSNO PRIMARY KEY (TransNo));
CONSTRAINT FK_STAFF_NAME FOREIGN KEY (StaffName) REFERENCES (Staff) Name;



CREATE TABLE Staff (Name char (20), PayRate decimal (7,2), WorkDates varchar (8), ShiftStart varchar (8), ShiftEnd varchar (8),
CONSTRAINT PK_NAME PRIMARY KEY (Name));


Re: Constraint Error [message #233406 is a reply to message #233401] Thu, 26 April 2007 01:17 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Quote:
CONSTRAINT PK_TRANSNO PRIMARY KEY (TransNo));
CONSTRAINT FK_STAFF_NAME FOREIGN KEY (StaffName) REFERENCES (Staff) Name;

You end your create-table statement with a semicolon (first quoted line), then you start a new statement "constraint ...."
This should be
alter table transactions add constraint....
Re: Constraint Error [message #233410 is a reply to message #233391] Thu, 26 April 2007 01:26 Go to previous message
Power Penguin
Messages: 33
Registered: April 2007
Location: Australia
Member
Thanks heaps Frank!!
Previous Topic: Max Query Help!!!
Next Topic: how to select rows using datatypes
Goto Forum:
  


Current Time: Fri Dec 09 23:38:43 CST 2016

Total time taken to generate the page: 0.11581 seconds