Menu
How can i create a multi-column primary key within the CREATE TABLE statement using the h2 database? From my investigations, the code to do so in mySQL and Apache Derby databases is:
CREATE TABLE SAMP.SCHED(
CLASS_CODE CHAR(7) NOT NULL,
DAY SMALLINT NOT NULL,
STARTING TIME,
ENDING TIME,
PRIMARY KEY (CLASS_CODE, DAY));
But this doesn't work in h2, it results in a 'org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement'
Any help is much appreciated. Thanks
547
In Table Designer, click the row selector for the database column you want to define as the primary key. If you want to select multiple columns, hold down the CTRL key while you click the row selectors for the other columns. Right-click the row selector for the column and select Set Primary Key.
CREATE TABLE SH_LEAGUE_CONTACT_TEAM_ROLE(ROLE_NAME VARCHAR NOT NULL, TEAM_ID INT NOT NULL, CONTACT_ID INT NOT NULL, FOREIGN_KEY(TEAM_ID) REFERENCES SH_LEAGUE_TEAM(ID), FOREIGN_KEY(CONTACT_ID) REFERENCES SH_LEAGUE_CONTACT(ID), PRIMARY KEY(ROLE_NAME, TEAM_ID, CONTACT_ID)); h2.
For defining a PRIMARY KEY constraint on multiple columns, use the SQL syntax given below. CREATE TABLE CUSTOMERS( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25) , SALARY DECIMAL (18, 2), PRIMARY KEY (ID, NAME) );
For a constraint to be recognized as a primary key, it must contain unique values throughout the row and none of the values must be NULL. In a table, there can only be one primary key. A primary key can have one or as many columns as possible.
From here:
this should work:
ALTER TABLE SAMP.SCHED ADD PRIMARY KEY (CLASS_CODE, DAY)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
